Closing the connection after procedure is complete

[theturrible]

I cant figure out how to do that.... I just need to close the connection to the database after query is complete.

[Mike Pilsbury]

You can call the Connection's close function in the Request's completion callback.

The documentation for the close function was missing, and I've just added it. Sorry for that.

[gman]

Mike, 

What exactly does the connection.close method do? Does it render the connection useless after that and hence I need to use new to make another connection? Or does it simply close all result sets and object associated with the previously run query, and hence is reusable?

The reason I am asking is because we have spent almost a whole day trying o figure out why we keep getting the error:

Invalid state; requests can only be made in the LoggedIn state, not the SentClientRequest state

It seems to be telling us that the case is that we are trying to query using a connection that has a pending/in process query. However, our completion callback is being called, the row callback is being called - but we aren't getting the done event, and everything in our code is identical to your simple example sans the the call to connection.close int he completion routine. Should we be calling close in our callback routine and if we do will we have to call new connection every time after that? 

Thanks,

Gregory

[Mike Pilsbury]

What exactly does the connection.close method do? Does it render the connection useless after that and hence I need to use new to make another connection?

Yes. As there is a bit of overhead in creating a new connection and authenticating, it's usually better to reuse the connection if you know that you have more requests to make. An alternative is to pool connections (github.com/pekim/tedious-connection-pool). 

 

Or does it simply close all result sets and object associated with the previously run query, and hence is reusable?

No. Result set data will be available for garbage collection once it's no longer referenced.

The reason I am asking is because we have spent almost a whole day trying o figure out why we keep getting the error:

Invalid state; requests can only be made in the LoggedIn state, not the SentClientRequest state

It seems to be telling us that the case is that we are trying to query using a connection that has a pending/in process query.

That is correct.

 

However, our completion callback is being called, the row callback is being called - but we aren't getting the done event,

That is strange. Do my comments in https://github.com/pekim/tedious/issues/61 help at all?

You could try enabling some debug logging (see https://github.com/pekim/tedious/issues/58#issuecomment-10691777), and see if that sheds any light on what's happening. 

[Andrew Marienberg]

I'm new to node and the async paradigm, so maybe I'm thinking about this wrong.

Anyway, what I'm trying to do is create a generic module for connecting using tedious-connection-pool. Here is the code for the sqlConnection.js module:

var ConnectionPool = require("tedious-connection-pool");

var baseConfig = {

userName: 'username',

password: 'password',

server: 'server'

};

var poolConfig = {

max: 10,

min: 0,

idleTimeoutMillis: 30000

};

 

function createConfig(config, options) {

config.options = options;

return config;

}

 

function executeStatement(connection, request) {

connection.execSql(request);

}

function connectAndExecute(configOptions, request) {

var pool = new ConnectionPool(poolConfig, createConfig(baseConfig, configOptions));

pool.requestConnection(function (err, connection) {

if(!err) {

console.log("Executing: " + request);

} else {

console.log("Could not create pool connection");

}

connection.on('connect', function(err) {

if(err) {

console.log('Connection Error: ' + err);

return;

} else {

executeStatement(connection, request);  

}

});

});

 }

exports.connectAndExecute = connectAndExecute;

//end of module

If I can do something like this, then I can just define what database to connect to passing in the database specific options and I can define Requests in their individual files and then pass them to the generic connection module to execute. The problem that I have is that the callback for a Request is the only place where I can be sure that the query has been run and close the connection. Like this:

var Request = require("tedious").Request;

var sqlConnection = require('./sqlConnection');

var options = {

database: 'database',

rowCollectionOnRequestCompletion: true

 };

var request = new Request("delete from dbo.Table", function(err, rowCount, rows) {

if (err) {

console.log(err);

} else {

console.log("Table cleared");

}

        connection.close();

});

sqlConnection.connectAndExecute(options, request); 

//end of file

The problem is that connection is not defined in this scope - so I can't do this.

But I shouldn't need to copy and paste the connection code every time I want to run a query (Don't Repeat Yourself). 

Is this a problem in the async paradigm, the way the library is set up or am I just going about it wrong?

[Andrew Marienberg]

Nevermind, I can probably just put the Request in connectAndExecute in the sqlConnection.js module and then just pass the query string.

A good reminder to myself not to post when frustrated. Sorry about that.

[Andrew Marienberg]

Ok, I'm still going through the pains of learning callbacks and asynchronous design, so I apologize if this is an ignorant question.

Basically, I know that my previous solution is not a good one. In my mind, there should be a way to decouple Request from Connection. But since the Request callback is typically the place to call connection.close(), is there any reason why connection isn't available as a parameter in the Request callback function rather than requiring the Request to be defined inside the Connection callback? Or is there a more elegant solution/some best practice that I'm missing?

Also, is it possible for user of the tedious library (i.e. me) to create a modified Request that makes more parameters available to the callback or can that only be done on your end? Something like this:

function requestWithConnection(requestString, connection, callback)

{

return req = new Request(requestString, function (err, rowCount, rows, connection) { 

callback(err, rowCount, rows, connection);

});

}