如何使用Node.js和Tedious从单个连接/事务中进行多个数据库调用

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

我试图使用NodeJS与Tedious(http://pekim.github.io/tedious/)sql server插件进行多个数据库调用。我的意图是:1。打开连接2.启动事务3.进行多个数据库(存储过程)调用,这些调用不会返回任何数据。 4.提交事务(或在错误时回滚)。 5.关闭连接

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.

这是一个示例.js文件,(不使用事务)用于NodeJS,我试图进行多个数据库调用,并且失败并显示错误“请求只能在LoggedIn状态,而不是SentClientRequest状态。”我没有尝试解决这个问题。

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!');

3 个解决方案

#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.

您正尝试在未建立的连接上执行语句。在调用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.");
        });
    });
});

#2

you can use Tedious Connection pools https://github.com/pekim/tedious-connection-pool

您可以使用Tedious Connection pool https://github.com/pekim/tedious-connection-pool

#3

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

您应该使用繁琐的连接池来创建多个连接池。对于节点js,可以在以下网址获得npm模块: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

对于for循环中的每个新值,您可以获取新连接并在doneInProc事件上使用connection.reset。您一直在进行的情况是正确执行for循环的第一次迭代(LoggedIn State),并且在没有关闭或释放连接的情况下继续使用相同的连接对象(SentClientRequest状态)。因此,当代码到达for循环的第二次迭代时,相同的对象处于最终状态。希望它能解决你的问题

#1