Node.js - Returning data from multiple MSSQL queries

Question

I'm new to learning Node.js, so I'm still getting used to asynchronous programming, callbacks and promises. I'm trying to return data from multiple MSSQL queries in one recordset, but most help articles I find are about MySQL.

I tried to follow the steps shown in the accepted answer here: Synchronous database queries with Node.js

In my SQL function, when I print to console, it's showing the object properly. When I return it to my express router and try to print the value it's saying it's undefined.

Here's my MSSQL function:

var config = require('../../db/config');

async function getJobData(jobID) {

   const sql = require('mssql');
   let sqlResult = {};

   var lock = 2;

   var finishRequest = function() {
       // This prints the values properly
       console.log(sqlResult['jobData']['recordset']);
       console.log(sqlResult['jobText']['recordset']);

       return sqlResult;
   }

   // first query
   try {
        await sql.connect(config)
        let result = await sql.query(`SELECT * FROM Jobs WHERE JobID = ${jobID}`);

        lock -= 1;
        sqlResult['jobData'] = result;
        sql.close();
        if (lock === 0) {
            finishRequest();
        }
    } catch (err) {
        // ... error checks
        console.log(err);
    }

   // second query
   try {
        await sql.connect(config)
        let result = await sql.query(`SELECT * FROM JDSectionTxt WHERE JobID = ${jobID} ORDER BY TypeID, OrderID`);

        lock -= 1;
        sqlResult['jobText'] = result;
        sql.close();
        if (lock === 0) {
            finishRequest();
        }
    } catch (err) {
        // ... error checks
        console.log(err);
    }
}

module.exports = getJobData;

Here is my express router:

const express = require('express');
//....
const app = express();
//....

// Job Descriptions - Edit
app.get('/jds/edit', (req, res) => {
    const getJobData = require("../models/jds/getJobData");

    let jobID = 0;

    if(req.query.jobID){
        jobID = parseInt(req.query.jobID);
    }

    let jobData = getJobData(jobID);

    jobData.then(result => {
        //This just prints 'undefined'
        console.log(result);

        res.render('jds/edit', {

            data: result
        });
    }).catch(err => {
        console.log(err);
    });

})

What do I need to change here to properly pass the object from my SQL queries so result is not undefined?

score 1 · Accepted Answer · answered Jun 10 '19 at 18:43

I think you're complicating it. Here's code that should do what you need:

var config = require('../../db/config');
const sql = require('mssql');

async function getJobData(jobID) {
    let sqlResult = {};

    await sql.connect(config)

    let firstAwait = getFirstQuery(jobID);
    let secondAwait = getSecondQuery(jobID);

    sqlResult['jobData'] = await firstAwait;
    sqlResult['jobText'] = await secondAwait;

    return sqlResult;
}

async function getFirstQuery(jobID) {
    try {

        return await sql.query(`SELECT * FROM Jobs WHERE JobID = ${jobID}`);
    } catch (err) {
        // ... error checks
        console.log(err);
    }
}

async function getSecondQuery(jobID) {
    try {
        return await sql.query(`SELECT * FROM JDSectionTxt WHERE JobID = ${jobID} ORDER BY TypeID, OrderID`);
    } catch (err) {
        // ... error checks
        console.log(err);
    }
}

module.exports = getJobData;

I definitely was complicating it. This is just what I needed, it's working now. I'll read up on awaits, thanks! — Cineno28, Jun 10 '19 at 23:27

score 0 · Answer 2 · answered Jun 10 '19 at 18:42

0

I think you missed return statement. In both first and second query try/catch block, you should give as return finishRequest().

answered Jun 10 '19 at 18:42

Prince Devadoss

416
3
6

Vikas Keskar · Answer 3 · 2019-06-10T18:45:13.017

You haven't returned result to function, so it sends it as undefined, You can update statement like, to see what's updated in code,please find comment // this is updated statement

var config = require('../../db/config');
function getJobData(jobID) {
sql = require('mssql');
sqlResult = {};
lock = 2;
var finishRequest = function() {
   // This prints the values properly
   console.log(sqlResult['jobData']['recordset']);
   console.log(sqlResult['jobText']['recordset']);

   return sqlResult;
}
try {
await sql.connect(config)
let result = await sql.query(`SELECT * FROM Jobs WHERE JobID = ${jobID}`);

    lock -= 1;
    sqlResult['jobData'] = result;
    sql.close();
    if (lock === 0) {
        finishRequest();
    }
} catch (err) {
    // ... error checks
    console.log(err);
}
// second query
try {
    await sql.connect(config)
    let result = await sql.query(`SELECT * FROM JDSectionTxt WHERE JobID = ${jobID} ORDER BY TypeID, OrderID`);

    lock -= 1;
    sqlResult['jobText'] = result;
    sql.close();
    if (lock === 0) {
        finishRequest();
    }
} catch (err) {
    // ... error checks
    console.log(err);
}
// this is updated statement
return sqlResult;
}
module.exports = getJobData;

When he calls finishRequest in the try catch block, finishRequest then returns the object with his results... Am I right? — silencedogood, Jun 10 '19 at 18:45
finishRequest returns result where it is called, not to main calling function. — Vikas Keskar, Jun 10 '19 at 18:46

Molda · Answer 4 · 2019-06-10T18:52:52.410

Although i'm not very familiar with async/await i believe the bellow code should work.

var config = require('../../db/config');

async function getJobData(jobID) {

    const sql = require('mssql');
    let sqlResult = {};

    try {

        await sql.connect(config)
        sqlResult['jobData'] = await sql.query(`SELECT * FROM Jobs WHERE JobID = ${jobID}`);

    } catch (err) {}

    try {

        sqlResult['jobText'] = await sql.query(`SELECT * FROM JDSectionTxt WHERE JobID = ${jobID} ORDER BY TypeID, OrderID`);
        sql.close();

    } catch (err) {}

    return sqlResult;
}

module.exports = getJobData;

You don't need the lock since when you use await the code is actualy synchronous You would need the lock if you used callbacks.

The only 'issue' with this approach is they queries are not run in parallel. The code will 'wait' at each `await` to continue to the next. — MattB, Jun 10 '19 at 18:47

Node.js - Returning data from multiple MSSQL queries

4 Answers4