3

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.

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.

Thank you!

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();
                        });
                    });
                });
            });
        });
    });

}
CSeelig
  • 31
  • 1
  • 5
  • Do `ps.prepare()` and `ps.execute()` return a promise? FYI, you're doing `await ps.unprepare` which looks like it should probably be `ps.unprepare()`. `await` only waits for something if it's awaiting a promise. – jfriend00 Feb 14 '18 at 01:02
  • If I do not await ps.prepare(), then: Error: PreparedStatementError: Statement is not prepared. Call prepare() first. As for unprepare(), agree! I've amended and tested; continues to fail as original (request in progress). – CSeelig Feb 14 '18 at 12:38
  • Also - my expectation in translating callbacks to awaits is this: if the method uses callbacks in the original, then it must return a promise in the revision. Is that sound? – CSeelig Feb 14 '18 at 12:47
  • I don't know what "return a promise in the revision" means. You can't ever assume you know what something returns. You either look in the doc or in the code to see what a function returns. What specific version of node-msql are you running? And, have you studied the doc or code for the various functions you're using to see if they return promises? – jfriend00 Feb 14 '18 at 16:11
  • Mea culpa - admitting to laziness in my inferences regarding how it ought to work. Versions for pertinents are: mssql: 4.1.0; tedious: 2.2.4; node: v8.4.0 – CSeelig Feb 14 '18 at 18:13

2 Answers2

6

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

Hope this help.

cscan
  • 364
  • 2
  • 9
  • This should be the accepted answer - works beautifully @cscan – ttemple Oct 04 '19 at 21:41
  • At time of writing `transaction.begin` does return a promise provided that a callback function is not provided, e.g. in this case `begin()` returns a promise - `const t = await transaction.begin(ISOLATION_LEVEL.READ_COMMITTED)` – firxworx Jan 14 '21 at 21:18
2

Before you can commit or rollback a transaction, all statements have to be unprepared.

You have to await the unprepare statement too, otherwise the request is still in progress and the execute promise hasn't resolved yet.

Use a a little wrapper to make things easy:

import * as dotenv from 'dotenv'
import mssql from 'mssql'

dotenv.config()

const sqlServerConfig = {
  server: process.env.SQL_SERVER,
  user: process.env.QS_USER,
  password: process.env.QS_PASS,
  options: { enableArithAbort: false },
}

let pool: mssql.ConnectionPool
let transaction: mssql.Transaction
const statements: mssql.PreparedStatement[] = []

export const connect = async (): Promise<void> => {
  pool = new mssql.ConnectionPool({ ...sqlServerConfig, database: process.env.DATABASE })
  await pool.connect()
}

export const disconnect = async (): Promise<void> => {
  if (typeof pool == 'undefined') return
  if (pool.connected) await pool.close()
}

export const begin = async (): Promise<void> => {
  transaction = new mssql.Transaction(pool)
  await transaction.begin()
}

export const unprepare = async (statement: mssql.PreparedStatement): Promise<void> => {
  if (typeof statement == 'undefined') return
  if (statement.prepared) await statement.unprepare()
}

export const commit = async (): Promise<void> => {
  await transaction.commit()
}

export const rollback = async (): Promise<void> => {
  for (const statement of statements) {
    await unprepare(statement)
  }
  if (typeof transaction == 'undefined') return
  await transaction.rollback()
}

export const createStatement = (): mssql.PreparedStatement => {
  const statement = new mssql.PreparedStatement(transaction)
  statements.push(statement)
  return statement
}

Usage:

try {
  await connect()
  await begin()

  const myStatement = createStatement()

  ..... bind parameters
  ..... prepare statement

  for ( ..... ) {
    await myStatement.execute( ..... )
  }

  await unprepare(myStatement)

  await commit()
  await disconnect()
  exit(0)
}
catch(e) {
  log.error(e)
  await rollback()
  await disconnect()
  exit(1)
}

You create a prepared statement with createStatement(). createStatement keeps track of the statements so in case you rollback they will be unprepared for you when you call rollback.

Phierru
  • 219
  • 2
  • 5