Querying multiple rows using node-mysql

Question

This issue pertains to node-mysql.

I seem to be having a problem with the following code, and it seems to be related to the use of user defined variables. The rendered query runs fine inside of any mysql IDE, returning multiple records. However when executed by node-mysql, it only returns a single record.

I've verified that eliminating the WHERE clause specifying T3.gender_rank creates predictable results in both node-mysql and my mysql IDE.

Has this issue been raised before, or is it something that I'm doing wrong?

var mysql = require('mysql'); // Establish SQL Support
var sql =
    "select team, `div`, sum(points) AS teampoints "+
    "from ( "+
    "   select "+
    "       *, "+
    "       (@genderRank := CASE WHEN @genderTMP <> gender THEN 1 ELSE @genderRank+1 END) AS gender_rank, "+
    "       (@genderTMP := gender) AS _gt "+
    "   from ( "+
    "       select * "+
    "       from racepoints "+
    "       WHERE "+
    "           ltrim(rtrim(race)) = '"+data.eventName+"' AND "+
    "           racedate = '"+getSimpleDate(new Date(data.eventStart))+"' "+
    "       ORDER BY team,gender,points DESC "+
    "   ) T1, "+
    "   (select @genderRank:=0 AS gender_rank_start) T2 "+
    ") T3 "+
    "WHERE "+
    "   (T3.gender_rank <= 4 AND rtrim(ltrim(`div`)) = 'D2') OR "+
    "   (T3.gender_rank <= 3 AND rtrim(ltrim(`div`)) = 'D1') "+
    "GROUP BY team, `div` "+
    "ORDER BY `div`, teampoints DESC, team";


var db = new mysql.createConnection({
    host : 'localhost',
    user : 'root',
    password : 'admin',
    database : 'nps',
    multipleResults : true
});
db.connect();
db.query(
    sql,
    function(err,rows,fields){
        if(err){
            console.log(err);
            return;
        }
        console.log(rows);
    }
);
db.end();

The SQL database file is also available here: https://snipt.net/download/dde8b4b5ce8cd5fca21ac2334bae634f/-5369.sql

what's in the `data` and `getSimpleDate`? – Andrey Sidorov Jul 09 '13 at 00:36 — Andrey Sidorov, Jul 09 '13 at 00:36

score 1 · Answer 1 · answered Jul 06 '13 at 18:14

1

Instead of directly adding your variables to the sql string, let the mysql package do it for you. This usually solves problems like this one and additionally protects against SQL injection.

This is done using question marks (one for every variable) followed by an array of variables.

For example

connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
  // ...
});

answered Jul 06 '13 at 18:14

ehsangh

311
2
6
16

ehsangh, This would work for the two values I'm concatenating, but not for the user variables. As you can see, they're actually dynamic within the SQL statement and not something that I can pass as a variable in the request. – clayperez Jul 07 '13 at 00:29

Querying multiple rows using node-mysql

1 Answers1