CODEWITHSUNDEEP
node.jstypescriptpromise
Mauro Sala
Mauro Sala

Reputation: 1186

nodejs mssql transaction pool

I have a typescript module.

public multipleQuery(queries: string[]) {
    return new Promise(async (resolve, reject) => {
      const cPool = new sql.ConnectionPool(this.room.db);
      await cPool.connect().then((pool: any) => {
        const transaction = new sql.Transaction(pool);
        return transaction.begin(async (err: any) => {
          const request = new sql.Request(transaction, { stream: true });
          try {
            queries.forEach(async (q) => {
              await request.query(q);
            });
            transaction.commit((err2: any) => {
              pool.close();
              if (err2) {
                reject(err2);
              } else {
                resolve(true);
              }
            });
          } catch (err) {
            transaction.rollback(() => {
               pool.close();
               reject(err);
            });
          }
        });
      }).catch((err: Error) => {
        cPool.close();
        reject(err);
      });
    });
}

queries variable is an array of string, I put inside a lot of sql insert queries. No matter what I write in queries, I still receive this error, why?

RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state TransactionError: Can't acquire connection for the request. There is another request in progress.

Upvotes: 1

Views: 6617

Answers (1)

Mauro Sala
Mauro Sala

Reputation: 1186

the solutions is to use async

const async = require("async");

public multipleQuery(queries: string[]) {
return new Promise((resolve, reject) => {
  const pool = new sql.ConnectionPool(this.room.db);
  return pool.connect().then((p: any) => {
    const transaction = new sql.Transaction(p);
    return transaction.begin((err: any) => {
        const request = new sql.Request(transaction);
        if (err) {
          reject(err);
        }
        return async.eachSeries(queries, async (query: any, callback: any) => {
            return request.query(query);
          }, async (err2: any) => {
            if ( err2 ) {
              await transaction.rollback(() => {
                pool.close();
                reject(err2);
              });
            } else {
              await transaction.commit(() => {
                pool.close();
                resolve(true);
              });
            }
          });
      });
  });
});
}

Upvotes: 1

Related Questions