Simultaneous queries with node-mysql

Question

I have a Javascript program that is querying a MySQL table and I can't figure out why the results of simultaneous queries are not well handled.

There are multiple instances of an object, Zone. When instanciating the objects, I query the database to get some info.

My problem is that when I instanciate two objects at the same time, the result of the query of the first one is written in the second object.

I tried to create two different connections using a pool, but it doesn't change anything.

Here is my code :

Getter into database :

getOrdresAchat = function(aConnection, aIdZone, aCallback){
    aConnection.query("SELECT * FROM Ordre WHERE Entreprise_idEntreprise = "+aIdZone+" AND Sens = 'Achat' ORDER BY Valeur DESC;", aCallback); 
}

Objects definition :

//No need to check that object
Ordre = function(aNumero, aJoueur, aEntreprise, aSens, aTypeOrdre, aQuantite, aPrix, aBorneInf, aBorneSup) {
    this.numero = aNumero;
    this.entreprise = aEntreprise;
    this.joueur = aJoueur;
    this.sens = aSens;
    this.typeOrdre = aTypeOrdre; // 'cours_limite', 'meilleure_limite', 'seuil_declenchement' ou 'au_marche'
    this.quantite = aQuantite; // nombre entier positif
    this.prix = aPrix; //si le prix est nul il est considéré comme sans limite
    this.borneInf = aBorneInf;
    this.borneSup = aBorneSup; 
}

//kinda an array of Ordre
Carnet_achat = function() { 
    this.liste = [];
    this.addElement = function(aOrdre) {
        this.liste.push(aOrdre);
    };

    this.display= function(){
        console.log("begining carnet_achat.display | List.length : "+this.liste.length);
        for(i in this.liste){
            console.log("row "+i+" : " +this.liste[i].prix);
        }
    };

}

Zone = function(aIdZone, aConnection) {
    var idZone = aIdZone;

    this.setIdZone = function(aNewId){
        idZone = aNewId;
    }

    this.getIdZone =function(){
        return idZone;
    }

    this.getCarnetAchat = function(aConnection){
        rCarnet_achat = new Carnet_achat(); //rCarnet is temporary object

        //we query database and push every row into the temp object
        getOrdresAchat(aConnection, idZone, function(err, rows){
            if(err) throw err;
            for(i in rows){
                ordreToAdd = new Ordre(rows[i].idOrdre, 
                                        rows[i].Joueur_idJoueur, 
                                        rows[i].Entreprise_idEntreprise, 
                                        rows[i].Sens, 
                                        rows[i].Type, 
                                        rows[i].Quantite, 
                                        rows[i].Valeur,
                                        rows[i].BorneInf,
                                        rows[i].BorneSup);
                rCarnet_achat.addElement(ordreToAdd);
            }
        });
        return rCarnet_achat;
    }

    //init
    this.carnet_achat = new this.getCarnetAchat(aConnection);

}

main :

var mysql = require('mysql');
var pool = mysql.createPool({
    host     : 'localhost', 
    user     : 'Bibacoeur', 
    password : 'Bibacoeur2014', 
    database : 'bibacoeur'
});

pool.getConnection(function(err, connectionA) {
    if(err) throw err;
    GI = new Zone(1, connectionA); //connection is used to query database

    //timeOut to wait the end of the query
    setTimeout(function(){console.log("GI_ACHAT");GI.carnet_achat.display();},3000);

});

pool.getConnection(function(err, connectionB) {
    if(err) throw err;
    GE = new Zone(2, connectionB); //connection is used to query database

        //timeOut to wait the end of the query
    setTimeout(function(){console.log("GE_ACHAT");GE.carnet_achat.display();},3000);

});

DATABASE :

mysql> select Entreprise_idEntreprise, Sens, Valeur from ordre;
+-------------------------+-------+--------+
| Entreprise_idEntreprise | Sens  | Valeur |
+-------------------------+-------+--------+
|                       1 | Vente |      0 |
|                       1 | Vente |  90000 |
|                       1 | Vente |  91000 |
|                       1 | Vente |  92000 |
|                       1 | Vente |  95000 |
|                       1 | Vente | 100000 |
|                       1 | Achat |  88500 |
|                       1 | Achat |  90500 |
|                       1 | Achat |  90500 |
|                       2 | Achat |      0 |
+-------------------------+-------+--------+
10 rows in set (0.00 sec)

Result log :

C:\Users\QuentinB\Google Drive\Desktop\temp>node app.js
GI_ACHAT
begining carnet_achat.display | List.length : 0
GE_ACHAT
begining carnet_achat.display | List.length : 4
row 0 : 90500
row 1 : 90500
row 2 : 88500
row 3 : 0

It should be :

C:\Users\QuentinB\Google Drive\Desktop\temp>node app.js
GI_ACHAT
begining carnet_achat.display | List.length : 3
row 0 : 90500
row 1 : 90500
row 2 : 88500
GE_ACHAT
begining carnet_achat.display | List.length : 1
row 0 : 0

It seems that the result of the query done from the first object, GI, is received by the second object GE.

I tried to delay the creation of the second object with : setTimeout(function(){GE = new Zone(2, connectionB);},10); and it worked. But I don't want to have to delay every creation, because I have 40 more objects to instanciate.

What can I do if I want to be sure the results of my queries are received by the right object?

Thanks for reading !

Answer 1

Since you're not declaring rCarnet_achat with the var keyword, you're implicitly declaring it as a global variable. So, each time you call rCarnet_achat.addElement(ordreToAdd);, you're actually adding elements to the same Carnet_achat instance. This could be fixed by adding var rCarnet_achat; to the top of the Zone function.

p.s. your code is riddled with other problems, e.g.

• You should use node-mysql's query string escaping to avoid SQL injection attacks, e.g. aConnection.query("SELECT * FROM Ordre WHERE Entreprise_idEntreprise = ? AND Sens = 'Achat' ORDER BY Valeur DESC;", aIdZone, aCallback)
• Using a setTimeout to wait for your SQL query to finish is wrong. Just wait for the callback.
• Since you have "40 more objects to instanciate", you might consider writing a single query to fetch them all at once, e.g. select * from foo where id in (1,2,3);