为什么我的事务mssql应用程序的异步/等待版本失败，但是回调版本有效? [英] Why does async/await version of my transactional mssql app fail but callbacks version works?

问题描述

为了阐明我正在使nodejs/mssql应用程序正常运行的问题，我尝试编写包装在事务中的简单(准备好的)INSERT语句的两个功能等效的版本.

To illuminate the problem I'm having getting a nodejs/mssql application working, I've attempted to code two functionally equivalent versions of a simple (prepared) INSERT statement wrapped in a transaction.

回调版本有效-在我的Sql Server数据库中插入一行.

The callbacks version works - inserts a row into my Sql Server db.

异步/等待版本引发错误-

The async / await version throws an error -

TransactionError: Can't commit transaction. There is a request in progress.

我已经尝试了失败版本的许多变体(在可能的情况下重新声明语句)，但是下面包含的版本是最能模仿工作中的回调版本逻辑的版本.

I have tried many variations of the failing version (statement reordering where plausible), but the version included below is the version that most closely mimics the logic of the working, callbacks version.

谢谢！

var sql = require('mssql');  // mssql: 4.1.0; tedious: 2.2.4; node: v8.4.0

var cfg = {
    "db": "sqlserver",
    "domain": "XXXXXX",
    "user": "cseelig",
    "password": "xxxxxx",
    "server": "xxxxxx.xxxxxx.xxxxxx.xxxxxx",
    "port": 1433,
    "stream": false,
    "options": { 
        "trustedConnection": true
    },
    "requestTimeout": 900000,
    "connectionTimeout": 30000,
    "pool": {
        "max": 3,
        "min": 0,
        "idleTimeoutMillis": 30000
    }
};

var statement = "insert into wng_dw.dbo.D_LIB_Google_Search_Query (query, LastUpdateDate) values (@query, GetDate())";

// I only run one or the other -

main1("12347");   // fails
main2("98765:);   // works

async function main1(val) {

    try {
        const conn = await new sql.connect(cfg);
        const transaction = new sql.Transaction();
        await transaction.begin();
        const ps = new sql.PreparedStatement(transaction);
        ps.input('query', sql.VarChar(200));
        await ps.prepare(statement);
        await ps.execute( {"query": val} );
        await ps.unprepare();
        await transaction.commit();
        sql.close;
    } catch(err){
        console.log("Error: " + err);
    };

    process.exit(0);

}


async function main2(val) {

    sql.connect(cfg, err => {
        const transaction = new sql.Transaction();
        transaction.begin(err => {
            const ps = new sql.PreparedStatement(transaction);
            ps.input('query', sql.VarChar(200));
            ps.prepare(statement, err => {
                ps.execute( {"query": val}, (err, result) => {
                    ps.unprepare(err => { 
                        transaction.commit(err => {
                            sql.close();
                        });
                    });
                });
            });
        });
    });

}

推荐答案

transaction.begin不返回Promise.您可以简单地散布它.类似于以下内容:

The transaction.begin does not return a Promise. You could simply promisfy it. Something like the following:

await new Promise(resolve => transaction.begin(resolve));
const request = new sql.Request(transaction);
//...
await transaction.commit();

在提交和回滚之后，无法再使用请求"对象.否则，它将显示有关交易未开始的错误....

After the commit and rollback, the "request" object could not be used anymore. Otherwise it will show the error regarding the transaction didn't begin ....

希望获得帮助.

这篇关于为什么我的事务mssql应用程序的异步/等待版本失败，但是回调版本有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！