CODEWITHSUNDEEP
sqlnode.js
Djaenike
Djaenike

Reputation: 1865

Node js make request to SQL Server, return result

Working with Node.js and am having some trouble understanding how to return the result after making a request to SQL Server. When running independently and writing to the console, I can get the result just fine, however using it as a function and having it return the result is where I am running into problems.

I'm pretty sure I have to use a callback/promise, but don't really understand how either of those are set up. Hoping someone on here can help me out!

Here is my code:

var sql = require("mssql");

var config = {
    user: 'username',
    password: 'password',
    server: 'localhost', 
    database: 'Master' 
};

function updateTable() {
    var connection = new sql.ConnectionPool(config, function(err) {
        var request = new sql.Request(connection);
        request.query('select LastName from Persons', function(err, result) {
           return result.recordset;
        });
    });
};

console.log(updateTable());

Basically trying to print the result to the console by calling the function through console.log. Right now it's printing 'undefined', but I assume putting in a callback would do the trick. Again, just need some help understanding how it works and getting it set up. Thanks!

Upvotes: 1

Views: 1696

Answers (1)

Kunal Mukherjee
Kunal Mukherjee

Reputation: 5853

If you just want to print it to the console then you can simply tweak your code as follows:

var sql = require("mssql");

var config = {
    user: 'username',
    password: 'password',
    server: 'localhost', 
    database: 'Master' 
};

function updateTable(callback) {
    var connection = new sql.ConnectionPool(config, function(err) {
        var request = new sql.Request(connection);
        request.query('select LastName from Persons', function(err, result) {
           callback(result.recordset);
        });
    });
};

updateTable(console.log);

To send the result from an express handler, assuming >= NodeJS v8:

Wrap the actual database interfacing logic inside an async function which will not block the main thread and export it from your module.

sqlConnector.js

const sql = require('mssql');

const config = {
    user: 'username',
    password: 'password',
    server: 'localhost', 
    database: 'Master' 
};

const updateTable = async () => {
    try {
        const pool = await sql.connect(config);
        const sqlQuery = 'SELECT LastName FROM Persons';
        const result = await pool.request().query(sqlQuery);
        return result;
    } catch (err) {
        throw err;
    }
};

export.updateTable = updateTable;

In express handler

Import your module which talks with MS-SQL (assumed sqlConnector.js) and mark your handler with the async keyword and return it from res.json at the end.

const sqlConnector = require('sqlConnector');

app.get('/someroute', async (req, res, next) => {
  try {
    const result = await sqlConnector.updateTable();
    return res.status(200).json(result);
  } catch (error) {
    next(error);
  }
});

Upvotes: 3

Related Questions