Skip to content

Example of one way to perform Bulk Inserts

Tom Conlon edited this page Oct 31, 2017 · 2 revisions
/*--------------- main.js -------------------*/
var 
   util = require('util'),
   dao = require('./app/dao');

var
   // if usepool is the 2nd command line param then it will use pooling, otherwise not
   want_to_use_pool = process.argv[2]=='usepool';


// lights, camera, action :) 
processFile();


function processFile(){
var
   fs = require('fs'),
   filename = './bulkInserts.csv',
   idx=0, data, lines, total;

   data  = fs.readFileSync(filename, "utf8");
   lines = data.split('\n');
   total = lines.length;
   console.log( 'total lines: ' + total);

   // posting loop, first call to this function is immediately after this
   function postRow(i) {
      var line, a;      

      if (i===total) {
         console.log('we\'re done');
         process.exit();
      }
      else {
         console.log('processing line ' + i);

         line = lines[i].replace(/(?:\\[rn])+/g, "");      
         a = line.split(',');

         if (a.length<20)
            postRow(i+1);
         else
         {
            insertCallRecord(a, function(err,res){
               console.log(idx++, 'success:', res, 'error:',  err );
               postRow(i+1);    // within call back so can now goto next row
            });
         }

      }
   }

   // we're off with the first row
   postRow(0);
}



function insertCallRecord(a, cb){
   var callid, direction, extension, duration;
   var sql, sql_base = 
   "INSERT INTO CALLS (callid, direction, extension, duration) " +
   "VALUES ('%d', '%s', '%d', %s' ); ";

   callid            = a[1 ].trim();
   duration          = a[2 ].replace('0.', '').trim();
   ....
   sql = util.format( sql_base, callid, direction, extension, duration );

   if (want_to_use_pool)
      dao.execQuery( sql, null, cb );
   else 
      dao.execQuerySimple( sql, null, cb );
}


/*--------------- app/config.js -------------------*/
var
   creds = require('./creds');

module.exports = {
   environment: 'development' /*'production'*/ ,
   dbpoolsize: 7,
   dbsettings: {
      database: 'xyz.fdb',
      host: '127.0.0.1',
      port: 3050,
      user: creds.db.user,
      password: creds.db.password,
      role: null, // default
      timeout: 10000, // default query timeout         
      pageSize: 8192 // default when creating database
   }
};

/*--------------- app/dao.js -------------------*/
var
   fb = require('node-firebird'),
   ut = require('./utils'),
   cfg = require('./config.js'),
   pool = fb.pool(cfg.dbpoolsize, cfg.dbsettings);

exports.esc = function(s){
   return fb.escape(s);
};

exports.releasePool = function() {
   pool.destroy();
};

exports.execQuery = function(sql, params, cb) {

   pool.get(function(err, db) {
      if (err) {
         ut.logError(err);
         return cb(err, null);
      }
      else {
         console.log(sql);
         db.query(sql, params, function(err, result) {
            db.detach(); // IMPORTANT: release the pool db
            var res = result;
            if (err) console.log(err);
            return cb(err, res);
         });
      }
   });
};

exports.execQuerySimple = function(sql, params, cb) {
   var options = cfg.dbsettings;

   fb.attach(options, function(err, db) {
    
       if (err)
           throw err;
    
       // db = DATABASE
       db.query(sql, params, function(err, result) {
         db.detach();
         var res = result;
         if (err) console.log(err);
         return cb(err, res);
       });
   });
};