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

[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