How do you make multiple database calls from a single connection/transaction with Node.js and Tedious

Question

I am attempting to use NodeJS with the Tedious (http://pekim.github.io/tedious/) sql server plugin to make multiple database calls. My intent is to: 1. Open a connection 2. Start a transaction 3. Make multiple database (stored procedure) calls, which will not return any data. 4. Commit transaction (or roll back on error). 5. Close connection

Here is an example .js file, (without using a transaction) for NodeJS where I am attempting to make multiple database calls and it is failing with the error "Requests can only be made in the LoggedIn state, not the SentClientRequest state." Nothing I try resolves this issue.

Does anyone know how to resolve this?

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

var config = {
    userName: 'login',
    password: 'password',
    server: '127.0.0.1',
    options: { rowCollectionOnDone: true }
};

var max = 1;
for (var i = 0; i < max; i++) {
    var connection = new Connection(config);

    function executeStatement() {
        request = new Request("select 42, 'hello world'", function (err, rowCount) {
            if (err) {
                console.log(err);
            } else {
                console.log(rowCount + ' rows');
            }
        });

        request.on('row', function (columns) {
            columns.forEach(function (column) {
                console.log(column.value);
            });
        });

        request.on('doneInProc', function (rowCount, more, rows) {
        });

        request.on('doneProc', function (rowCount, more, rows) {
            console.log('statement completed!')
            connection.execSql(request);
        });

        request.on('returnStatus', function (status) {
            console.log('statement completed!')
        });

        connection.execSql(request);
    }

    connection.on('connect', function (err) {
        // If no error, then good to go...
        executeStatement();
    });
}
console.log('Done!');

Solution 1

You're trying to execute a statement on a connection that is not established. You're missing an error handler before you call executeStatement.

connection.on('connect', function (err) {
    if (err) {
        console.log(err); // replace with your code
        return;
    };

    // If no error, then good to go...
    executeStatement();
});

Edit:

How to execute multiple statements in a transaction in serial:

var statements = ["select 1", "select 2", "select 3"];

var transaction = new sql.Transaction(connection);
transaction.begin(function(err) {
    // ... error checks

    async.mapSeries(statements, function(statement, next) {
        var request = new sql.Request(transaction);
        request.query(statement, next);
    }, function(err, results) {
        // ... error checks

        transaction.commit(function(err, recordset) {
            // ... error checks

            console.log("Transaction commited.");
        });
    });
});

Solution 2

You should use tedious connection pools to create a pool of multiple connections. For node js, a npm module is available at : https://www.npmjs.com/package/tedious-connection-pool

For every new value inside for loop you can acquire a new connection and use connection.reset on doneInProc event. The case which you have been doing is performing 1st iteration of for loop correctly(LoggedIn State) and as you have proceeded without closing or releasing the connection you are using same connection object (SentClientRequest state). Hence the same object is at final state when the code reaches second iteration of for loop. Hope it resolves your issue

Comments

[Ken K]

You are correct that there should be error checking on the connect, but this is not the issue. I can execute only one sql statement on the connection. The question is how do I execute multiple statements on a connection, in serial. This is so I can batch multiple commands in a transaction. I will likely have too many statements to chain together in a single call to execute sql.

[Patrik Šimek]

You must not execute a statement before the previous one is complete. To ensure that you should use (or write your own) asynchronous library like async.

[Patrik Šimek]

I have updated my answer with example how to execute multiple statements in a transaction using async.

[Ryan]

When executing this example. I am getting multiple logs as "Transaction committed." when I thought the idea here is to run multiple queries within one transaction, in which case it should only be committing one transaction with a result of one log of "Transaction committed." and not as many transaction commits as there are queries. Am I thinking correctly on this?