connection close before inserting done in node-mysql

Question

My code is

var thdb = require('./module/thdb.js');
var csv = require('./module/readcsv.js');

var db = new thdb.database('root','root','localhost','TH_DB');
var CSVdata= new Array();
var csv_row=0;
csv.readFile('data.txt',",",true,function(data){

    CSVdata[csv_row]=data;
    csv_row++;

},function(count){

    for(var k=0;k<CSVdata.length;k++)
    {
        var data=CSVdata[k];

        (function(){

            var data_copy=data;
            db.checkSymbol(data_copy.Symbol,function(exist){

            if(exist)
            {
                //write log
                console.log('Nost Inserting: '+data_copy.Symbol);
            }
            else {
                //write log
                var data_copy2=data_copy;
                var db_copy=db;
                console.log('Inserting: '+data_copy2.Symbol);
                //insert


                var DataObj = {
                    Exchange_id:1,
                    Currency_id:1,
                    symbol: data_copy2.Symbol,
                    name:data_copy2.Name
                };

                db_copy.insertSecurity(DataObj);

            }//close exist else

        });//close check db

        })();
    }
    db.close();
});

I got a error

Not Inserting: E
Not Inserting: X    
Not Inserting: Z

Error: No database selected
    at Function._packetToUserObject (/Users/saturngod/Dropbox/th/node_modules/mysql/lib/client.js:384:7)
    at Query._handlePacket (/Users/saturngod/Dropbox/th/node_modules/mysql/lib/query.js:33:33)
    at Client._handlePacket (/Users/saturngod/Dropbox/th/node_modules/mysql/lib/client.js:309:14)
    at Parser.<anonymous> (native)
    at Parser.emit (events.js:64:17)
    at /Users/saturngod/Dropbox/th/node_modules/mysql/lib/parser.js:71:14
    at Parser.write (/Users/saturngod/Dropbox/th/node_modules/mysql/lib/parser.js:576:7)
    at Socket.<anonymous> (native)
    at Socket.emit (events.js:64:17)
    at Socket._onReadable (net.js:678:14)

If I don't close the database connection ,it's working fine. However , I close the database connection at the end of the loop, I always got a error.

Answer 1

The problem is that javascript is asynchronous, so most of what you are doing there will not ever attempt to run until after db.close has executed. The for loop you have will call checkSymbol, but all that does is send the query. It will keep looping and sending more, and then close the connection, then if a response happens to come in, the connection will have been closed.

I would recommend installing the node async module, to help with some of this. https://github.com/caolan/async#iterator

I've never used thdb, but I am going to assume that there is a second parameter for 'insertSecurity', which is a function that is called when the insert completed.

Here is some sample code.

var async = require('async');
var thdb = require('./module/thdb.js');
var csv = require('./module/readcsv.js');

var db = new thdb.database('root','root','localhost','TH_DB');

var dataFunctions = [];

function processData(data, callback) {

  db.checkSymbol(data.Symbol, function(exist){

    if(exist) {
      console.log('Nost Inserting: '+data.Symbol);
      callback();
    }
    else {
      console.log('Inserting: '+data.Symbol);

      var DataObj = {
          Exchange_id:1,
          Currency_id:1,
          symbol: data.Symbol,
          name: data.Name
      };

      db_copy.insertSecurity(DataObj, function () {
        callback();
      });
    }
  });
}    

csv.readFile('data.txt', ',', true, function(data) {
  dataFunctions.push(function(cb) {
    processData(data, cb);
  });
}, function (count) {
  async.series(dataFunctions, function() {
    console.log('Everything inserted!');

  })
});

Answer 2

why not close after the function completed?