How to connect On-Premise MySQL database using SAP CAPM NodeJS app?

[ramana_saps4hana43]

Hi Everyone,

we have a on-premise My SQL database where the data exists and we need to  fetch data from MySQL DB and store into HANA Cloud DB using CAPM NodeJS application on interval basis liek once in a day.

I'm able to connect to On-Premise MYSQL DB in Locally installed VS Code using NodeJS but when I'm trying the same in SAP BAS then getting error like "Error connecting to MySQL: Error: connect ECONNREFUSED 127.0.0.1:3306". I have update my etc file locally but still error persists.

Also I maintained Cloud Connector configuration as below

also created a destination on top of it in BTP cockpit.

I created a function in service.js file under srv folder

const { cds } = require('@sap/cds');

const { getDestination } = require('@sap-cloud-sdk/connectivity');

const mysql = require('mysql2/promise');

module.exports = async (srv) => {

    srv.on('loadReadingsData', async (req) => {

        try {

            // Get the destination from BTP Cockpit

            const destination = await getDestination({ destinationName: 'MySQLDB' });

            const { URL, username, password, database } = destination.originalProperties;

            console.log("connecting to EnerChart DB start");

            // // Connect to the MySQL database using the details from the destination

            const connection = await mysql.createConnection({

                host: '127.0.0.1',

                port: 3306,

                // host: URL,

                user: username,

                password: password,

                database: database,

                // ssl: {

                    // ca: fs.readFileSync('/path/to/ca-cert.pem'),

                    // cert: fs.readFileSync('/path/to/client-cert.pem'),

                    // key: fs.readFileSync('/path/to/client-key.pem'),

                //     ciphers: 'TLS_AES_128_GCM_SHA256',

                //     rejectUnauthorized: true

                // }

            });

            // console.log("Connected to MySQL!", connection.threadId);

            // Query MySQL (example)

            // const [rows, fields] = await connection.execute('SELECT * FROM MeterReadings');

            // console.log(rows.length);

            // Close the connection

            // await connection.end();

        } catch (error) {

            console.error('Error connecting to MySQL:', error);

        }

    })

}

Not able to connect with URL : http:mysql:3306 so i tried with direct IP address above.

Still not able to connect to On-premise MySQL DB. Also Can anyone suggest any approach for better security like Certificates or principle propagation etc.. in both BTP & MySQL DB to implement.

Can anyone please help/guide me to achieve the above scenario with best practices.

Thank you in advance.

Regards,

Ramana.

[Yogananda]

@ramana_saps4hana43 

MySQL is not supported and will not be made available for support for CAP based applications with Nodejs or Java based..

Refer List of supported and Built in Database support for CAP

https://cap.cloud.sap/docs/guides/databases

[ramana_saps4hana43]

Hi yogananda,

[ramana_saps4hana43]

Hi yogananda, Thanks for your kind response. I'm not using MySQL DB as backend DB for CAP service rather I'm fetching data from MySQL DB and storing into HANA Cloud DB. As NodeJS can connect to any DB to read and same I'm trying. I'll insert the data finally into HANA Cloud DB and UI on top of it for all CRUD and Analytics.

[Yogananda]

thanks for altering the questions posted with provided more revised content... As I see your Destination is still not be able reachable to the cloud to get the data from your local db