Going through hell using Promises

[Karun Reddy]

In my application, I am querying multiple SQL Servers to return some data. In my main function, I am iterating through the SQL Servers and on each of them, I am iterating through the queries that are supposed to run to fetch the data. When querying the SQL Server for data, I am using a connection promise returned by the following function. 

function poolIt (sqlQuery,sqlServer,databaseName) {

if(!connection)

{

//sqlServer_temp = sqlServer

//instead of checking if connection is defined

//we have to check if connection is open

var dbConfiguration = {

driver: 'msnodesqlv8',

server: sqlServer,

database: databaseName,

options: {

trustedConnection: true

}

}

connection = sql.connect(dbConfiguration);

}

}

Now when the loop is done for one SQL Server, it should connect to another SQL Server. But when the debug mode is on, I see that all the queries are running against only one server. I understand that this is because of the above function as it is checking if a connection object is available or not before creating a new connection. So, my question is on how to kill the connection object?  connection.close() is not working and nor does sql.close() 

Any help, please ?

[Joshua Holbrook]

This is actually pretty easy--if you don't need connection in the outer scope (ie you don't ever plan on reusing it), you can define it locally:

function poolIt(q, url, dbName) {
  const conn = sql.connect(mssqlConfig(url, dbName));

Or, if you need to reuse it, you can store it on an object, maybe something like:

const conn = {};

function poolIt(q, url, dbName) {
  const k = hashSomehow(url, dbName);

  if (!conns[k]) {
    conns[k] = sql.connect(mssqlConfig(url, dbName));
  }

Keep in mind you'll need some kind of cleanup for the conn:

conns[k].close(); // This will close the connection
conns[k] = null; // This will enable GC

I'd consider changing the above to just use a connection name parameter rather than generating a hash, though.

--Josh

--
Job board: http://jobs.nodejs.org/
New group rules: https://gist.github.com/othiym23/9886289#file-moderation-policy-md
Old group rules: https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines
---
You received this message because you are subscribed to the Google Groups "nodejs" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nodejs+unsubscribe@googlegroups.com.
To post to this group, send email to nod...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nodejs/44521f9c-993a-4e4f-a533-efaba1d7054c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jaret Flores]

If you need asynchronous tasks to be synchronized, you should proceed with the next "stage" in the previous stage's `then` block:

function getConnection (server, databaseName) {
    // ...
}


function executeServerQueries (connection, queries) {
    var queryPromises = queries.map(function (query) {
        return connection.execute(query);
    });


    // the 'catch' below insures this function always returns a resolved promise
    return Promise.all(queryPromises)
        .catch(function (err) {
            console.log(err);
            return 'No results!';
        });
}


function executeQueries (configArr) {
    if (!configArr.length) return Promise.resolve();


    var config = configArr.unshift();


    return getConnection(config.sqlServer, config.databaseName)
        .then(function (connection) {
            return Promise.all([
                executeServerQueries(connection, config.queries),
                connection
            ]);
        })
        .then(function (outcome) {
            var connection = outcome.pop(),
                results = outcome.pop();


            connection.close();
            // do something with results?


            return executeQueries(configArr);
        });
}

Now, `executeQueries` just takes an array of configurations and returns a `Promise` after all have completed. I forgot if Node.js has native `Promise.all` support, but you can use something like bluebird if not.  It can also be cleaned up a bit by using some other bluebird methods and es6 syntax (but I forgo'd these since I didn't see you use them).