Transaction Global in Node-Firebird

Question

I'm using "node-firebird" in my Firebird 2.5 project and I would like to use a single transaction for multiple inserts or update in some batches, but I couldn't use a global transaction. Could someone help me with this?

This is the single form in the official example:

Firebird.attach(options, function(err, db) {

if (err)
    throw err;

// db = DATABASE
db.transaction(Firebird.ISOLATION_READ_COMMITED, function(err, transaction) {
    transaction.query('INSERT INTO users VALUE(?,?)', [1, 'Janko'], function(err, result) {

        if (err) {
            transaction.rollback();
            return;
        }

        transaction.commit(function(err) {
            if (err)
                transaction.rollback();
            else
                db.detach();
        });
    });
});

});

I trying this

const  NewTransaction=()=>{
  return new Promise((resolve, reject) => {
    
    firebirdPool.get((err, db) => {
      if (err) {
          reject(err);
        return;
      }
      
      DBGlobal=db;

      DBGlobal.transaction(Firebird.ISOLATION_READ_COMMITED, 
        function(err, transaction) {
          //here i trying save the transaction
          TransactionGlobal=transaction;

          if (err) {
            reject(err);
          return;
          }
          resolve(TransactionGlobal)
        });
    });//firebirdpool
  });//promisse
}//function



const CommitTransaction=()=>{
  return new Promise((resolve, reject) => {

    TransactionGlobal.commit(function(err) {
      if (err){
          transaction.rollback();
          reject(err);
          return;
      }
      else {
        DBGlobal.detach();
        resolve(true);
      }
    });//transaction

  });//promisse
}  


const RollbackTransaction=()=>{
  return new Promise((resolve, reject) => {

    try{
      TransactionGlobal.rollback();
      resolve(true);
    }
    catch(err){
      reject(err)
    }

  });//promisse
}


//usado com commit  
const QueryExecTransaction = (sql,arrayparams=[]) => {
  return new Promise((resolve, reject) => {

          TransactionGlobal.query(sql,arrayparams,function(err, result) {
  
              if (err) {
                  console.log('erro na execução da query');
                  TransactionGlobal.rollback();
                  reject(err);
                  return;
              }
              resolve(result);  
              return;
          });//query
          
  });//promisse
}

I run with this test

async function  test(){
  await NewTransaction();
  console.log('Transacao Global',TransactionGlobal);
  QueryExecTransaction(`insert into tabparametros(codigo,nome,valor) values (0,'teste1','')`);
  CommitTransaction();
}
test();

But i received this error:

(node:9232) UnhandledPromiseRejectionWarning: Error: invalid transaction handle (expecting explicit transaction start)

score 0 · Answer 1 · answered Aug 09 '21 at 15:07

I managed to solve, the alteration was in "commitretaining" and others small alters

below the code

//variaveis de "ambiente" salvas na raiz do projeto
//.env  e .env.testing
require('dotenv').config({  
    path: (process.env.NODE_ENV === "test")||(process.env.NODE_ENV === "development") ? ".env.testing" : ".env"
  })
  
  var Firebird = require('node-firebird');
  var fs = require('fs');
  var options = {};
   
  options.host = process.env.DB_HOST;
  options.port = process.env.DB_PORT;
  options.database = process.env.DB_DATABASE;
  options.user = process.env.DB_USER;
  options.password = process.env.DB_PASSWORD;
  
  options.lowercase_keys = false; // set to true to lowercase keys
  options.role = null;            // default
  options.pageSize = 4096;        // default when creating database
  
  //console.log(options);
  
  //Conexao
  // 5 = the number is count of opened sockets
  var firebirdPool = Firebird.pool(5, options);
  
  
  
  
  const  NewTransaction=()=>{
    return new Promise((resolve, reject) => {
      
      firebirdPool.get((err, db) => {
        if (err) {
            reject(err);
          return;
        }
        
        //aqui eu salvo o DB retornando
        //DBGlobal=db;
  
        db.transaction(Firebird.ISOLATION_READ_COMMITED, 
          function(err, transaction) {
            
            //aqui eu salvo a transacao retornada
            //TransactionGlobal=transaction;
  
            if (err) {
              reject(err);
            return;
            }
            resolve({transaction,db});
          });
      });//firebirdpool
    });//promisse
  }//function
  
  
  
  const CommitTransaction=(transaction,db)=>{
    return new Promise((resolve, reject) => {
  
      transaction.commitRetaining(function(err) {
        if (err){
            transaction.rollback();
            reject(err);
            return;
        }
        else {
          db.detach();
          resolve(true);
        }
      });//transaction
  
    });//promisse
  }  
  
  
  const RollbackTransaction=(transaction,db)=>{
    return new Promise((resolve, reject) => {
  
      try{
        transaction.rollback();
        db.detach();
        resolve(true);
      }
      catch(err){
        reject(err)
      }
  
    });//promisse
  }
  
  
  //usado com commit  
  const QueryExecTransaction = (transaction,sql,arrayparams=[]) => {
    return new Promise((resolve, reject) => {
  
        transaction.query(sql,arrayparams,function(err, result) {
    
                if (err) {
                    console.log('erro na execução da query');
                    transaction.rollback();
                    reject(err);
                    return;
                }
                resolve(result);  
                return;
            });//query
            
    });//promisse
  }
  
  
  
  
  
  
  async function  testa(){
    const {transaction,db}=await NewTransaction();
    //console.log('Transacao Global',transaction);
    let psql='';
    try{
        for (let i=1;i<101;i++){
        
            
            psql=`insert into tabparametros(codigo,nome,valor) values (0,'teste${i}-${new Date()}','')`

            if (i==79){
                //psql='forcando o erro';
            }
            
            await QueryExecTransaction(transaction,psql);


        }
        await CommitTransaction(transaction,db);
    }
    catch(e){
        console.log('Erro no SQL');
        console.log(e);

        await RollbackTransaction(transaction,db);
        
    }

    console.log('Finalizado')
  }
  testa();

Transaction Global in Node-Firebird

1 Answers1