Node-mssql: Mssql cause node.js instance exit after a few queries

[Zam4]

Hi,

First, thank you for the job you have done with this it looks great !

But I am having a little issue querying database a few times in a row :

Here is the code

try {

var timeOutCallback = function() {
    var config = {
        "user": "msq2RTFlow",
        "password": "msqService4RTFlow",
        "server": "localhost",
        "database": "MSQ"
    };
    var connection = new mssql.Connection(config);
    connection.connect().then(function() {
        var request = new mssql.Request(connection);
        request.query('SELECT * FROM Patient;').then(function(recordset) {
            console.log(recordset.length);
            connection.close();
            timeoutId = setTimeout(timeOutCallback, 1000);
        }).catch(function(err) {
            console.log(err);
            connection.close();
            timeoutId = setTimeout(timeOutCallback, 1000);
        });
    }).catch(function(err) {
        console.log(err);
        connection.close();
        timeoutId = setTimeout(timeOutCallback, 1000);
    });
};
var timeoutId = setTimeout(timeOutCallback, 1000);

} catch (p_error) {
    console.log(p_error);
}

I took the code to query databasase directly from exemples using promises.
Even if I'm not using promises, I am experiencing the same issue.
I Am not having neither of my error logs in console, I'm having 7 results from query, ten times, then it stops with no message or error at all.

[JasonWeise]

I am experiencing exactly the same behaviour. When multiple consecutive calls to the SQL Server are processed, it just stops responding, no errors, not catching any exceptions... just stops.

Is there any idea why this is happening.

[gronnbeck]

Is it possible that your mssql closes your connection? The standard connection timeout is 15 seconds [1].

[1] https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout(v=vs.110).aspx

[Zam4]

I don't think so because as you can see opening a new connection for each callback.
So each callback should open a connection for way less than 15 seconds.

[gronnbeck]

@Zam4 you got a point.

Btw I experienced a resembling problem when my application had connections. So I am, as we speak, rewriting our application to pool n-number of connections instead. I will keep you posted if that works or not.

[neuralsyn]

I am experiencing the same thing. Has anyone figured out a way around this?

[tracker1]

Is there a reason you aren't holding onto the connection (the connection is actually a pool), and just reusing it without explicitely closing?

[tracker1]

The following pattern will give you an existing connection/pool when available, as well as support closing, which will kill the handle to the pool, and connection.

IMHO, this should really be the default behavior... why the connection callback doesn't return the connection is beyond me... also, that there isn't an internal pool-list is a bit odd too, imho. I'm doing something similar for mssql-ng, which wraps this library with a template processor for parameterized queries.

//getConnection Module
var pool = null;
module.exports = function getConnection() {
  var config = {
    user: "msq2RTFlow",
    password: "msqService4RTFlow",
    server: "localhost",
    database: "MSQ"
  };

  if (pool) return pool;
  var conn = new mssql.Connection(config);

  //override close behavior to eliminate the pool
  var close_conn = conn.close;
  conn.close = function(){
    pool = null;
    close_conn.apply(conn, arguments);
  }

  return pool = conn.connect()
      .then(function(){ return conn; })
      .catch(function(err){
        pool = null;
        return Promise.reject(err);
      });
}

[dstroot]

@tracker1 - I am trying to use this in Express Router routes. Are they any examples of how to use the connection module pattern above?

[infoTrainingym]

Similiar situation here. After some time, mssql doesnt responde. No error, no timeout. If i restart, nodejs server, it works again.

Any idea? i Use 3.0 version.

[tracker1]

@dstroot sorry, didn't see your reply until now... put the above in it's own module... in your code, you would use...

getConnection().then(
    function(conn){
      //use conn, don't close unless the process is closing.
    },
    function(err){
      //error creating connection
    }
);`

[vespina]

Is this still an issue in current version of this module? I am evaluating to use node.js as backend for a SQL Server based Web App, and I am kind of depending of this module.

[willmorgan]

Pooling and individual connection lifecycle management has been improved in recent releases so I'm closing this in the hope that upgrading to the latest version resolves the matter.