3

I am trying to end my mysql connection in my node program. I can successfully do that using something like connection.end(). However, when I do that it doesn't seem to run the queries before that. If I leave the end statement out my insert queries before this seem to run, however the program then hangs and it just never ends.

Here is my code

var mysql      = require('mysql');

var connection = mysql.createConnection({'...'});

 connection.connect(function(err){

 });

        for(var a=0; a<ticker.length;a++){
            if(result[i].tweetText.indexOf(ticker[a]) > -1){
                for(var j=0; j<positive.length;j++){
                    if((result[i].tweetText.indexOf(positive[j]) > -1) && check > -1){
                        console.log(result[i].tweetText + "\n")
                        plus++;
                        console.log(ticker[a] + "plus"+plus +"\n")
                        check = -1;
                        connection.query("insert into....", function(err, result) {
                        });
                    }
                }


            }
        }

   connection.end();

Having the end successfully terminates connection, but then all my insert statements do not run; when I remove the end connection statement, my insert statement runs but the program hangs.

Faran
  • 71
  • 2
  • 8

2 Answers2

4

You can do what I did and use a mysql pool. Store your connection settings in an object, create the pool using your config variable, and then pass around the con however you want, and then just grab the connection, query your db, and it takes care of everything for you. No hangups.

var db_config = {
    user:"",
    password:"",
    database:"",
    port:80
};

var con = mysql.createPool(db_config);

And then use the getConnection function to open and release the connection.

con.getConnection(function(err){
    con.query("SELECT * FROM tableName",function(err,result) {

    });
});

You can read more about pool connections via the github mysql source

Sterling Archer
  • 22,070
  • 18
  • 81
  • 118
  • 1
    This works, except now my variables are lost and come as undefined. What I don't get is why the mysql statements are running after the entire loop is complete; by then my variables are lost and are being inserted as undefined. – Faran Mar 19 '14 at 04:02
  • 1
    For some reason when I use connection.release() it still does not end the program. Connection.destroy() ends it, but again I have the same issue where my queries statements are not run. I'm confused as to why my queries are running after all the processing and not being run at the time it is executed in the for loop. – Faran Mar 19 '14 at 05:57
  • @Faran you need to pass around your variables like function parameters otherwise they will lose their scope. You don't need to call `release()` or `destroy()` with a connection pool – Sterling Archer Mar 19 '14 at 16:16
  • How about including an example of using con.release()? – Ken Ingram Dec 09 '19 at 14:32
0

According with documentation when you use conn.query, the connection automatically is open, so you don't need 

connection.connect(function(err){

});

Answering your question, the problem is because when you insert into db, node doesn't wait for and continues with the loop. You can push the data into an array and after the loop you can insert bulk data. like this:

How do I do a bulk insert in mySQL using node.js

var bulk = data; // 

connection.query("insert into table (columns) values ?", bulk, function(err, result) {
    connection.destroy();
});
Community
  • 1
  • 1
castci
  • 11