Connect to MSSQL Database from SAP BTP CAPM application

[kowsalyaa]

Hi All,

 

1. We have a capm application, in which we are trying to connect to to Microsoft SQL Server using mssql npm package
2. While doing so, we are encountering an issue while connection to database.
3. I’m adding the code snippet and error screenshot that we are encountering while reaching the database. Please let us know how can we connect to MSSQL database from CAPM application. I have also used sequelize to connect to the database, but still couldn't able to connect.

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

const sql = require('mssql');

module.exports = cds.service.impl(async function () {

  this.on('READ', 'YourEntity', async (req) => {

    try {

      // SQL Server Connection Config

      const config = {

        user: process.env.SQL_USER,

        password: process.env.SQL_PASSWORD,

        server: process.env.SQL_SERVER,

        database: process.env.SQL_DATABASE,

        options: {

          encrypt: true, // Use encryption if necessary

          trustServerCertificate: true // Change to false in production

        }

      };

      // Connect to SQL Server

      const pool = await sql.connect(config);

      const result = await pool.request().query('SELECT * FROM YourTable');

      return result.recordset;

    } catch (err) {

      console.error(err);

      req.reject(500, 'Error connecting to SQL Server');

    }

  });

});

Regards,

Kowsalyaa Kumar

[Pmayank]

Hi @kowsalyaa ,

My requirement was same to connect with SQL Database and i'm able to connect to SQL DB as well.

Above code looks fine, just make sure libraries are updated in package.json.

Sharing mine.

"dependencies": {

"@sap/cds": "^7",

"@sap/cds-hana": "^2",

"@sap/cds-sql": "^1.24.1",

"@sap/xssec": "^3",

"express": "^4",

"mssql": "^11.0.1",

"mysql2": "^3.12.0",

"pg": "^8.13.3",

"pg-hstore": "^2.3.4",

"sequelize": "^6.37.5",

"tedious": "^18.6.1"

},

Regards,

Preeti

[carlbf]

Hi @Pmayank ,

I have the same issue trying to reach the server of the SQL Server configured in the cloud conector using the mssql library for nodejs.

Did you configure anything else apart of binding the connectivity servíce to the cap project?

Seems that the project is not able to resolve the virtual host and virtual port configured in cloud conector.

Thanks in advance!

[martinstenzig]

A few things: 

1. There should not be a problem connecting to MS SQL server if you have the right node package. 

2. Your problem seems to be a problem that it cannot find or resolve the host name of your server. 

3. There is at present no "standard SAP SQL Server adapter". For node, the only ones that are available (for Node) are HANA, Postgres and sqlite. 

4. If you wanted to build it correctly, you would package your SQL adapter in a CAP plugin "shell" similar to the adapters you find in https://github.com/cap-js/cds-dbs