Node-mssql: Resolve array of queries in one Transaction

[estvmachine]

Using a global connection like here #164 for use Transaction. I think this works but how i can do to resolve a array of queries using transaction or multiples transactions, the code i put works only for one querie.

routes/set1Transaction.js

var sql     = require('mssql');

module.exports = function(globalConnection) {
   return function(req, res, next) {
      var transaction = new sql.Transaction(globalConnection);
      transaction.begin(function(err) {
          // ... error checks

             var request = new sql.Request(transaction);
             request.query('insert into mytable (mycolumn) values (12345)', function(err, recordset) {
                 // ... error checks

             transaction.commit(function(err, recordset) {
                  // ... error checks

              console.log("Transaction commited.");
                  });
            });
       });
       req.results=recordset;
       next();

    }

};

[patriksimek]

Try this:

var sql = require('mssql');

module.exports = function(globalConnection) {
    return function(req, res, next) {
        var transaction = new sql.Transaction(globalConnection);
        transaction.begin(function(err) {
            // ... error checks

            var request = new sql.Request(transaction);
            request.query('insert into mytable (mycolumn) values (12345)', function(err, recordset) {
                // ... error checks

                transaction.commit(function(err) {
                    // ... error checks

                    console.log("Transaction commited.");
                    req.results = recordset;
                    next();
                });
            });
        });
    }
};

[estvmachine]

The question goes for the line of what happen if i want to do make two queries or array of queries. Somethinkg like this:

ar sql = require('mssql');

module.exports = function(globalConnection) {
    return function(req, res, next) {
        var transaction = new sql.Transaction(globalConnection);
        transaction.begin(function(err) {
            // ... error checks

            var request = new sql.Request(transaction);
            request.query('insert into mytable (mycolumn) values (12345)', function(err, recordset) {
                // ... error checks

                   //MULTIPLES QUERIE HERE BEFORE COMMIT
                    request.query2(...)
                    request.query3(...)

                transaction.commit(function(err) {
                    // ... error checks

                    console.log("Transaction commited.");
                    req.results = recordset;
                    next();
                });
            });
        });
    }
};

[estvmachine]

Sorry, how can i make two queries in one transaction at least?, not works for me :/

[estvmachine]

Here is my try...

module.exports = function(globalConnection) {
    return function(req, res) {

      var results=[];
        var transaction = new sql.Transaction(globalConnection);
        transaction.begin(function(err) {
            // ... error checks

            var request = new sql.Request(transaction);
            request.query('SELECT 1', function(err, recordset) {
              results.push(recordset);
                // ... error checks

                request.query('SELECT 1', function(err, recordset) {
                  results.push(recordset);
                    // ... error checks

                    transaction.commit(function(err) {
                        // ... error checks

                        console.log("Transaction commited.");
                        console.log(results);
                        res.status(200).json(results: results);
                    });
                });
            });
        });
    }
};

This works, but how can i make this more dynamicall like works with an array of queries for example.
The results is:

"results": [
[
{
"": 1
}
],
[
{
"": 1
}
]
]

[christiaanwesterbeek]

How about using async

var sql = require('mssql');
var async = require('async');

var transaction = new sql.Transaction(globalConnection);
transaction.begin(function(err) {
    if (err) {
        return console.error('Error in transaction begin', err);
    }

    var request = new sql.Request(transaction);

    async.series({
        one: function(callback){
            request.query('SELECT 1 as a', callback);
        },
        two: function(callback){
            request.query('SELECT 2 as b', callback);
        }
    },
    function(err, results) {
        // results is now equal to: {one: [{a: 1}], two: [{b: 2}]}
        if (err) {
            console.error('Error in queries, rolling back', err);
            return transaction.rollback();
        }
        transaction.commit(function(err) {
            if (err) {
                return console.error('Error in commit', err);
            }
            console.log("Transaction commited.");
            console.log(results);
        });
    });
});

_Untested code_

If you have an array of queries, you can use async.each if you want to execute them in parallel or async.eachSeries if you want to run them consecutively. Both have a final callback function where you can commit or rollback the transaction.

[estvmachine]

That actually works, but my try with async.eachSeries doesnt xD.

var sql = require('mssql');
var async = require('async');
var transaction = new sql.Transaction(globalConnection);
var resultsList=[];

      transaction.begin(function(err) {
      if (err) {
          return console.error('Error in transaction begin', err);
      }

      var request = new sql.Request(transaction);

      var hugeArray= ['SELECT 1 as a', 'SELECT 1 as b'];
      async.eachSeries(hugeArray, function iterator(item, callback) {
          console.log(item);
          request.query(item, callback);


      },
      function(err, results) {

          if (err) {
              console.error('Error in queries, rolling back', err);
              return transaction.rollback();
          }
   // Here i try push the results
          resultsList.push(results);
          transaction.commit(function(err) {
              if (err) {
                  return console.error('Error in commit', err);
              }
              console.log("Transaction commited.");

              next();
          });
      }
    );
  });

[christiaanwesterbeek]

@estvmachine What doesn't work? Your code looks fine. What did you expect and what is your actual result?

[estvmachine]

Ah sorry, the actual result is a list of undefined.

[christiaanwesterbeek]

@estvmachine essentially your code is fine. I feel my work is done explaining how you can approach the problem. Now you may address your own development skills and debug. And when you're done debugging and still have a problem, you could ask a question at Stackoverflow or post a new issue here if you think something is broken with mssql.

[estvmachine]

Yeah you are right, thanks for the advice.

[estvmachine]

Hi, only im passing for says thanks and told how i resolved this, i used a constructor function named 'setUpMultipleQueries', is a forEach using the 'listQuery'. Well i leave the code. Thanks!

var sql = require('mssql');
var async = require('async');

var transaction = new sql.Transaction(globalConnection);
transaction.begin(function(err) {
    if (err) {
        return console.error('Error in transaction begin', err);
    }

      var request = new sql.Request(transaction);
      var listQuery= setUpMultipleQueries(req.consultas, request);  /*HERE IS THE MAGIC*/

      async.series( listQuery,

    function(err, results) {
        // results is now equal to: {one: [{a: 1}], two: [{b: 2}]}
        if (err) {
            console.error('Error in queries, rolling back', err);
            return transaction.rollback();
        }
        transaction.commit(function(err) {
            if (err) {
                return console.error('Error in commit', err);
            }
            console.log("Transaction commited.");
            console.log(results);
        });
    });
});

function setUpMultipleQueries(listQuery, request){
  var requestObject= {};
  listQuery.forEach(function(query, index){
    console.log(index);
      requestObject['q'+index]= function(callback){
            request.query(query, callback);
      };
  });
  return requestObject;
};

[melounek]

I found this during my solution research and I think for v4.x this is nicer with ES6 and async/await

    const transaction = await new sql.Transaction()
    transaction.begin(async err => {
      try{
        const request = new sql.Request(transaction)
        const result1 = await request.query`query1`
        const result = await request.query`query2`
        transaction.commit(tErr => tErr && next('transaction commit error'))
      }catch(err){
        transaction.rollback(tErr => tErr && next('transaction rollback error'))
        next('unknown error inside transaction = rollback')
      }
    })