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

ikhvjs · Answer 1 · 2022-08-02T15:44:09.840

3

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

    let transaction;
    try {
        transaction = new sql.Transaction(dbConn);

        await transaction.begin();

        const request = new sql.Request(transaction);

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

        await transaction.commit();

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

edited Aug 02 '22 at 15:44

answered Aug 18 '21 at 12:04

ikhvjs

5,316
2
13
36

thank you for your reply @ikhvjs , i tried the above code and still am getting the same error : "Can't rollback transaction. There is a request in progress." – Kmss Aug 19 '21 at 07:11
@Kmss, I updated my answer to try using the same request for the opearation instead. – ikhvjs Aug 19 '21 at 07:40
1

i thank you so much for your answer .and its works fine for the procedures having the different parameter. But I have one more use case that I have to execute two different procedures having the same parameter Name and different values – Kmss Aug 22 '21 at 06:10
kindly please check the below my answer as well – Kmss Aug 22 '21 at 06:14

score 0 · Answer 2 · answered Aug 22 '21 at 06:13

@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;
  }
};```

Node.js MS SQL transaction

2 Answers2