Connection MSSQL with node_modules

Hello!!!
I made a connection to MSSQL by copying the module to the node_modules folder.

I tried to connect in two ways, but neither of them works. There is no connection.

Both versions of the code work locally.

I connected according to the method

Example code 1 variant:

const sql = require(‘mssql’);

const config = {
user: ‘test’,
password: ‘test’,
server: ‘test’,
database: ‘test’,
authentication: {
type: ‘default’
},
options: {
encrypt: true
}
}

console.log(“Starting…”);
connectAndQuery();

async function connectAndQuery() {
const searchEmail = “test@test.com
try {
var poolConnection = await sql.connect(config);

    console.log("Reading rows from the Table...");

    var resultSet = await poolConnection.request().query(`SELECT Email, Id, Salt FROM Users WHERE Email LIKE '%${searchEmail}%'`);
    const result = resultSet.recordset
    console.log(result)
    console.log(result.length)
    
    poolConnection.close();
} catch (err) {
    console.error(err.message);
}

}

Example code variant 2:
var Connection = require(‘tedious’).Connection;
var config = {
server: ‘test’,
authentication: {
type: ‘default’,
options: {
userName: ‘test’,
password: ‘test’
}
},
options: {
encrypt: true,
database: ‘test’
}
};
var connection = new Connection(config);
connection.on(‘connect’, function(err) {
console.log(“Connected”);
executeStatement();
});

connection.connect();

const searchEmail = "test@test.com"


var Request = require('tedious').Request;   

function executeStatement() {  
    request = new Request(`SELECT Email, Id, Salt FROM Users WHERE Email LIKE '%${searchEmail}%'`, function(err) {  
    if (err) {  
        console.log(err);}  
    });  
    var result = "";  
    request.on('row', function(columns) {  
        columns.forEach(function(column) {  
          if (column.value === null) {  
            console.log('NULL');  
          } else {  
            result+= column.value + " ";  
          }  
        });  
        console.log(result);  
        result ="";  
    });  

    request.on('done', function(rowCount, more) {  
    console.log(rowCount + ' rows returned');  
    });  
    
    request.on("requestCompleted", function (rowCount, more) {
        connection.close();
    });
    connection.execSql(request);  
}

Thank you in advance.

hi @ROOT_INHALE

do you see any errors?

@vladimir-upirov
No, i don`t see errors.


Hello @vladimir-upirov !
Do you have any suggestions on how to solve this?

yes, seems like you need to keep the async sequence

see the following post

https://backendless.com/docs/bl-js/bl_troubleshooting.html#servercode-works-well-in-the-debug-mode-but-does-not-when-deployed-to-the-cloud

Hello @vladimir-upirov !

I kept the asynchronous sequence. The examples have files with different connections that work correctly locally and none of them work in Backendless.
Please help with the solution of the issue.
Code variants that I use:

Regards

you need to do something like that in your CustomCode:

const result = await new Promise((resolve, reject)=>
sql.connect(config).then(() => {
    return sql.query`select * from Users where id = ${id}`
}).then(result => {
    console.dir(result)
    
    resolve(result) // THIS IS IMPORTANT
}).catch(err => {
    console.log(err);
   
   reject(err) // THIS IS IMPORTANT
})
})

once you resolve/reject the promise the execution goes on.

if you just add the following code

sql.connect(config).then(() => {
    return sql.query`select * from Users where id = ${id}`
}).then(result => {
    console.dir(result)    
}).catch(...)

the execution won’t wait a result

@vladimir-upirov
I tried the option to connect via Promise, but the value is not displayed in the console. I do not understand why Promise does not work

Regard

could you please share the entire code for the API Method?

@vladimir-upirov

Thanks!

this is not an API Service Method, I would like to see how you use promises in your CloudCode Api Service

@vladimir-upirov

If I understood you correctly, I don’t use it yet because I can’t get an answer from the method
image

try to add an await in front of connectAndQuery();

@vladimir-upirov


no console.log(result)

and add await in front of promise.then

@vladimir-upirov
The result has not changed

the value.request()... promise is still out of the main promise sequence