node.js mssql javascript method second time call to fetch data from database prints undefined

Question

I have issues with the following code using the mssql module of nodejs:

A) Function getAllRecords_B prints "undefined" on second call to it. (As can be seen in the code, what I am trying to do is : I want to pass the connection object to it so that it can pick one connection from the pool and use it instead of creating connections again and again.)

B) THe second time function call of getAllRecords_C says the connection has been closed already. How to overcome this problem?

    var fs = require("fs");
    var sql = require("mssql");
    var config = {  
        "server": "localhost",
        "user": "xxx",
        "password": "xxx",
        "database": "TestAutomation",
         "port": 1433 
    };

    getAllRecords_B = function (conn, quer, callback) {
        conn.connect(function (err){
            var request = new sql.Request(conn); // or: var request = connection1.request(); 
            request.query(quer, function(err, recordset) {
                //console.dir(recordset);
                callback(recordset);
            });
        });
}

function getAllRecords_C(configuration) {
  return sql.connect(configuration).then(function() {
      var retVal = new sql.Request().query('select * from USERSTATUS');
    }).catch(function(err) {
        console.log("Error1", err);
    }).catch(function(err) {
    console.log("Error2", err);
  });
}

console.log("---------------------------------");
var connection = new sql.Connection(config);

var query1 = "select * from USERSTATUS";
getAllRecords_B(connection, query1, function(recs) {
    console.log(recs);
});

getAllRecords_B(connection, query1, function(recs) {
    console.log(recs);
});

forrestmid · Accepted Answer · 2017-03-18 19:18:12Z

1

Seems like you should be creating a connection pool if you want to pull from a connection pool. Something like the following:

var fs = require("fs");
var sql = require("mssql");
var config = {  
    "server": "localhost",
    "user": "xxx",
    "password": "xxx",
    "database": "TestAutomation",
     "port": 1433 
};

//Define records GET function.
var getAllRecords_B = (pool, quer, callback) => {
  pool.request().query(quer, (err, recordset) => {
    callback(recordset);
  });
}

//Start connection.
const pool = new sql.Connection(config, err => {
  var query = "select * from USERSTATUS";

  //Call function twice.
  getAllRecords_B(pool, query, recs => {
    console.log("query1",recs);
  });

  getAllRecords_B(pool, query, recs => {
    console.log("query2",recs);
  });
});

pool.on('error', err => {
    // ... error handler
});

Added some ES6 awesomesauceness in there for your enjoyment.

edited Mar 18, 2017 at 19:18

answered Mar 18, 2017 at 16:58

forrestmid

1,50418 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

win Over a year ago

Thanks @forrest, but I got this error. You see in "mssql" module (npmjs.com/package/mssql) the Connection is the ConnectionPool. var pool = new sql.ConnectionPool(config).then(function(pool){ ^ TypeError: sql.ConnectionPool is not a constructor

forrestmid Over a year ago

@win Happy to hear that. You can accept the answer with the little check under the vote counter. :)

Jonathan · Accepted Answer · 2017-08-01 01:52:27Z

0

forrestmid's answer was correct when he commented.

Just for someone who use this code from now on. Beware there was a rename change since the new release V4.0.0 on 1 Apr 17.

https://github.com/patriksimek/node-mssql/releases/tag/v4.0.0

[change] Connection renamed to ConnectionPool

new sql.Connection (v3.3) => new sql.ConnectionPool (v4.0+)

So you have to use new sql.ConnectionPool if you install this module (by default) after April 17.

answered Aug 1, 2017 at 1:52

Jonathan

514 bronze badges

Collectives™ on Stack Overflow

node.js mssql javascript method second time call to fetch data from database prints undefined

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related