How to fill the pool with different connections?

[boredSinan]

Hi.

I am calling the following function 5 times for different select-statements and write the results in an array as follows:

var select_result = [];
execute_SELECT_Statement('SELECT column1 FROM table');
execute_SELECT_Statement('SELECT column2 FROM table');
execute_SELECT_Statement('SELECT column3 FROM table');
execute_SELECT_Statement('SELECT column4 FROM table');
execute_SELECT_Statement('SELECT column5 FROM table');

function execute_SELECT_Statement(sql_statement) {
    var i = 0;
    pool.acquire(function(err, connection) {
        if (err)
            console.error(err);

        //use the connection as normal
        var request = new Request(sql_statement, function(err, rowCount) {
            if (err)
                console.error(err);

            //release the connection back to the pool when finished
            connection.release();
        });
        connection.execSql(request);
        request.on('row', function(columns) {
            select_result[i] = columns[0].value;
            i++;
        });
    });
}

The display output of the array elements looks like:

This is my problem:

There is only 1 connection which fills the pool, therefore there is always the same result!
So how can I fill the pool with all 5 connections?
I need all different results of all the select statements!

Maybe there is an error in:
https://github.com/pekim/tedious-connection-pool/blob/master/lib/connection-pool.js#L144
and the following LOC?

[ben-page]

Hello, will you please add a complete example? Your example code doesn't do what your output shows. Also, please be sure to include your pool config.

[ben-page]

Just FYI, the connection-pool will not give you the same connection again until you release it. I'm not sure why you think that it's the same connection, but that's not how the connection pool works.

One thing that I noticed when trying to run your example, is that you are setting i to 0 on every call to execute_SELECT_Statement. So i is always 0 when accessed.

[boredSinan]

Thanks a lot ben-page,
now my code works fine with your tip (i is always set to 0) as follows:

Changes:

" i " is called now " loop_count " and it is set to 0 at the end of any execute_SELECT_Statement()-call.

//Import tedious-connection-pool project.
var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;


/*
 *  Config varibale which includes important information about the Microsoft SQL database.
 *  Without port number specification, there wont be a connection!
 *
 */
var connectionConfig = {
    server: 'MSserver',
    userName: 'user',
    password: 'password',
    options: {
        database: 'databaseName',
        port: 55555
    }
};

var poolConfig = {
    min: 1,
    max: 10,
    log: true
};

//Connection objects of the Microsoft SQL database.
//You need several connections, because 1 single connection is not able to work concurrent!
//Define a pool:
var pool = new ConnectionPool(poolConfig, connectionConfig);

/*
 *  db_uid_start: fixed first bytes for the MS-SQL datatype 'UniqueIdentifier'
 *  db_uid_end:   last bytes of the UID
 *
 */
var db_uid_start = '00000000-0000-0000-0000-';
var db_uid_end = '';

var sql_select_result_original = '';
var sql_select_result_fake = '';

var select_result = [];
var loop_count = 0;


//Make a Ms-SQL uniqueidentifier string format
//e.g. 00000000-0000-0000-0000-207754F45231
db_uid_end = db_uid_start.concat(peripheral.uuid);

//Execute SQL SELECT command
execute_SELECT_Statement('SELECT UID FROM table');
execute_SELECT_Statement('SELECT Name FROM table');
execute_SELECT_Statement('SELECT Address FROM table');
execute_SELECT_Statement('SELECT Brand FROM table');
execute_SELECT_Statement('SELECT Location FROM table');

display_Select_Result(select_result);

/*
 * Function which selects from all database-tables
 * Do not execute: "SELECT *       FROM table"
 * Always execute: "SELECT column  FROM table"
 */
function execute_SELECT_Statement(sql_statement) {

    pool.acquire(function(err, connection) {
        if (err)
            console.error(err);

        //use the connection as normal
        var request = new Request(sql_statement, function(err, rowCount) {
            if (err)
                console.error(err);
            //console.log('rowCount: ' + rowCount);

            //release the connection back to the pool when finished
            connection.release();
        });

        connection.execSql(request);

        request.on('row', function(columns) {
            //console.log('value: ' + columns[0].value);
            console.log('loop_count: ' + loop_count);
            select_result[loop_count] = columns[0].value;
            loop_count++;
        });
        //pool.drain();
    });
   loop_count = 0;
}

/*
 * Function which displays content of any array
 */
function display_Select_Result(select_result) {
    for (var l = 0; l < select_result.length; l++) {
        console.log('select_result: ' + select_result[l] + '\n');
    }
    select_result = null;
    select_result = [];
}

[ben-page]

This code has a lot of problems.

• There is no guarantee the queries will return in the order you called them. They are just appended to the end of select_result, but that could be any order. For example, the query for Brand could easily return before the query for Address.
• If there are multiple rows in table this would become even more problematic. The values from the columns in all the rows would just stuffed in select_result in a entirely unpredictable order.
• display_Select_Result will never display anything. pool.acquire is asynchronous. It's callback will always be executed after the call to display_Select_Result is called in your code.
• You don't need to do multiple queries to the same table. You can select multiple columns in a single query.

SELECT UID, Name FROM table