CODEWITHSUNDEEP
javascriptnode.js
Cineno28
Cineno28

Reputation: 909

Node.js - Returning data from multiple MSSQL queries

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?

Upvotes: 0

Views: 1205

Answers (4)

Molda
Molda

Reputation: 5704

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.

Upvotes: 0

Vikas Keskar
Vikas Keskar

Reputation: 1248

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;

Upvotes: 0

MattB
MattB

Reputation: 1104

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;

Upvotes: 1

Prince Devadoss
Prince Devadoss

Reputation: 416

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

Upvotes: 0

Related Questions