Node-mssql: RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state

[baekft]

Expected behaviour:

transaction.begin & loop DML excute error
Can't reuse "request"?

error log :

C:\workspace\AX5UI>node test_transaction.js
{ RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state
    at Request.tds.Request.err [as userCallback] (C:\workspace\AX5UI\node_modules\mssql\lib\tedious.js:629:19)
    at Request.callback (C:\workspace\AX5UI\node_modules\tedious\lib\request.js:37:27)
    at Connection.makeRequest (C:\workspace\AX5UI\node_modules\tedious\lib\connection.js:1671:15)
    at Connection.execSql (C:\workspace\AX5UI\node_modules\tedious\lib\connection.js:1449:10)
    at Immediate.parent.acquire [as _onImmediate] (C:\workspace\AX5UI\node_modules\mssql\lib\tedious.js:856:65)
    at runCallback (timers.js:706:11)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
  code: 'EINVALIDSTATE',
  number: 'EINVALIDSTATE',
  state: undefined,
  originalError:
   { RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state
       at RequestError (C:\workspace\AX5UI\node_modules\tedious\lib\errors.js:32:12)
       at Connection.makeRequest (C:\workspace\AX5UI\node_modules\tedious\lib\connection.js:1671:24)
       at Connection.execSql (C:\workspace\AX5UI\node_modules\tedious\lib\connection.js:1449:10)
       at Immediate.parent.acquire [as _onImmediate] (C:\workspace\AX5UI\node_modules\mssql\lib\tedious.js:856:65)
       at runCallback (timers.js:706:11)
       at tryOnImmediate (timers.js:676:5)
       at processImmediate (timers.js:658:5)
     message:
      'Requests can only be made in the LoggedIn state, not the SentClientRequest state',
     code: 'EINVALIDSTATE' },
  name: 'RequestError',
  precedingErrors: [] }
{ TransactionError: Can't acquire connection for the request. There is another request in progress.
    at Transaction.acquire (C:\workspace\AX5UI\node_modules\mssql\lib\base.js:813:30)
    at Immediate._query.err (C:\workspace\AX5UI\node_modules\mssql\lib\tedious.js:601:19)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5) code: 'EREQINPROG', name: 'TransactionError' }
{ TransactionError: Can't acquire connection for the request. There is another request in progress.
    at Transaction.acquire (C:\workspace\AX5UI\node_modules\mssql\lib\base.js:813:30)
    at Immediate._query.err (C:\workspace\AX5UI\node_modules\mssql\lib\tedious.js:601:19)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5) code: 'EREQINPROG', name: 'TransactionError' }
C:\workspace\AX5UI\node_modules\mssql\lib\tedious.js:375
      this._acquiredConnection.rollbackTransaction(err => {
                               ^

TypeError: Cannot read property 'rollbackTransaction' of null
    at Immediate._rollback.err (C:\workspace\AX5UI\node_modules\mssql\lib\tedious.js:375:32)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)

C:\workspace\AX5UI>

Actual behaviour:

> test_db_insert()
> 
> function test_db_insert(){
>   const pool = new sql.ConnectionPool(config)
>   pool.connect(err => {
>       if(err) console.log(err)
>       const transaction = new sql.Transaction(pool);
>       const request = new sql.Request(transaction);
>       
>       transaction.begin(err => {
>           if(err) return console.log(err);
>           
>           const array = [1, 2, 3];
>           
>           for(let TEST_ID of array){
>               let db_insert = 'INSERT INTO T_TEST_TABLE VALUES(' + TEST_ID + ');'
>               request.query(db_insert, (err, result) => {
>                   if(err){
>                       console.log(err)
>                       transaction.rollback()
>                   }
>               })
>           }
>           transaction.commit()
>       })
>   })
> }

Configuration:

Windows 10 Pro X64

CREATE TABLE TEST_DB.dbo.T_TEST_TABLE (
    TEST_ID int NOT NULL,
    PRIMARY KEY (TEST_ID)
) GO

Software versions

• NodeJS : v10.16.0
• node-mssql : 5.1.0
• SQL Server: Microsoft SQL Server 2008 R2 (SP3)

[willmorgan]

I think you'll be needing a request per item in your array. You're running multiple queries on the same request before waiting for the previous one to finish:

            for(let TEST_ID of array){
                let db_insert = 'INSERT INTO T_TEST_TABLE VALUES(' + TEST_ID + ');'
                request.query(db_insert, (err, result) => {

Closing as not a defect with the library.

[baekft]

sorry.
I do not understand well.

How can I finish a "query"?

What I want is to execute multiple "queries" and "rollback" them when an "error" occurs.

I want to make it like "proceduer" in "mssql".

sorry.
I'm a Korean developer and I'm not good at English.

[willmorgan]

You need to create a new request per query in the loop. You can still pass in the transaction to the request.

Bear in mind query execution is asynchronous so you're committing the transaction before all queries have a chance to complete.

https://github.com/tediousjs/node-mssql#query-command-callback

[baekft]

Thank you so much!
I'll change "source" and write "COMMENT" again!

[baekft]

willmorgan!!
Thank you so much!

Using "async.eachSeries" instead of "for" statements makes it synchronous and works well.

You are my benefactor
best regards!!

Here is the code changed as follows.

test_db_insert()

function test_db_insert(){
    const pool = new sql.ConnectionPool(config)
    pool.connect(err => {
        if(err) console.log(err)
        const transaction = new sql.Transaction(pool)
        const request = new sql.Request(transaction)

        transaction.begin(err => {
            if(err) return console.log(err)

            const array = [1, 2, 3]

//          for(let TEST_ID of array){
            async.eachSeries(array, function(TEST_ID, callback){
                if(TEST_ID == 3) TEST_ID = 1 //Deliberately "error"

                console.log(TEST_ID)

                let script_inc_insert = 'INSERT INTO T_TEST_TABLE VALUES(' + TEST_ID + ');'
                request.query(script_inc_insert, (err, result) => {
                    if(err){
                        callback(err)
                    }else{
                        callback()
                    }
                })
            }, function(err){
                if(err){
                    transaction.rollback()
                    console.log(err)
                }else{
                    console.log('success!')
                    transaction.commit()
                }
            })
        })
    })
}