Node.js MS SQL transaction

Question

Can anyone help to implement MS SQL transactions in Node.js . I am try to execute multiple stored procedures inside a promise.

Method 1

const executeProcedure = async (data1, data2) => {
    try {
        // sql connection 
        let dbConn  = new sql.ConnectionPool(config));
        await dbConn.connect();
        let transaction = new sql.Transaction(dbConn);

        await transaction.begin().then(async()=> {
            // tranaciton create
            // begin tran
    
            let result  = await insertOperation(transaction, data1);
            let result2 = await updateOperation(transaction, data2);

            let result1 = await  Promise.all([result, result2]);
            await transaction.commit();
            dbConn.close();

        }).catch(async(err)=> {
            await transaction.rollback();
            dbConn.close();
            throw err;
        });

        return {};
    }
    catch (error) {
        throw(error);
    }
}

method 2

const insertOperation   = async (transaction,data1) => {
    return new Promise((resolve, reject) => {
        try {
            var request = new sql.Request(transaction);
            request.input('data1'      , sql.NVarChar(40)   , data1)
            .execute('dbo.insertOperation').then((recordSet) => {
                resolve(recordSet.recordsets);
            }).catch((err) => {
                reject(err);
            });
        }
        catch (error) {
            reject(error);
        }
    });
}

method 3

const updateOperation   = async (transaction,data2) => {
    return new Promise((resolve, reject) => {
        try {
            var request = new sql.Request(transaction);
            request.input('data2'      , sql.NVarChar(40)   , data2)
            .execute('dbo.updateOperation').then((recordSet) => {
                resolve(recordSet.recordsets);
            }).catch((err) => {
                reject(err);
            });
        }
        catch (error) {
            reject(error);
        }
    });
}

Now I get this error Can't rollback transaction. There is a request in progress.

anybody please help me to solve this problem

Answer 1

You make some unnecessary Promise wrapper.

Example below:

const insertOperation = async (request, data1) => {
    request.input("data1", sql.NVarChar(40), data1);

    const result = await request.execute("dbo.insertOperation");

    return result.recordsets;
};

const updateOperation = async (request, data2) => {
    request.input("data2", sql.NVarChar(40), data2);

    const result = await request.execute("dbo.updateOperation");

    return result.recordsets;
};

const executeProcedure = async (data1, data2) => {
    // sql connection
    const dbConn = new sql.ConnectionPool(config);
    await dbConn.connect();
    const transaction = new sql.Transaction(dbConn);
    try {
        await transaction.begin();

        const request = new sql.Request(transaction);

        const results = await Promise.all([
            insertOperation(request, data1),
            updateOperation(request, data2),
        ]);

        await transaction.commit();
    } catch (err) {
        await transaction.rollback();
        throw err;
    } finally {
        await dbConn.close();
    }
};

Answer 2

@ikhvjs please check the below use case as well

  try {
    request.input("data", sql.NVarChar(40), data1);

    const result = await request.execute("dbo.insertOperation");

    return result.recordsets;
  } catch (err) {
    throw err;
  }
};

const updateOperation = async (request, data2) => {
  try {
    request.input("data", sql.NVarChar(40), data2);

    const result = await request.execute("dbo.updateOperation");

    return result.recordsets;
  } catch (err) {
    throw err;
  }
};

const executeProcedure = async (data1, data2) => {
  try {
    // sql connection
    const dbConn = new sql.ConnectionPool(config);
    await dbConn.connect();
    const transaction = new sql.Transaction(dbConn);
    try {
      await transaction.begin();

      const request = new sql.Request(transaction);

      const results = await Promise.all([
        insertOperation(request, data1),
        updateOperation(request, data2),
      ]);

      await transaction.commit();
    } catch (err) {
      await transaction.rollback();
      throw err;
    } finally {
      await dbConn.close();
    }
  } catch (error) {
    throw error;
  }
};```