MySQL query variables in node

Question

I'm having trouble with this query in NodeJS.

SET @i = 0;
SELECT POSITION FROM (
    SELECT name, @i:=@i+1 AS POSITION
    FROM database.table ti WHERE name='Johny' ORDER BY points ASC) t WHERE name='Johny'

Query works in Heidi SQL without a problem, but when i execute it in Node, i get callback is undefined.

NodeJS code :

var query =
          "SET @i = 0;"
        + " SELECT POSITION FROM ("
        + "     SELECT name, @i:=@i+1 AS POSITION"
        + " FROM database.table ti WHERE name='Johny' ORDER BY points ASC) t WHERE name='Johny'";
mySQLconnection.query(query,function(err,rows){
    console.log(rows);
});

Thank You in advance,

Answer 1

i search this post by google and i found the reason why variables not work try this it's work for me:

var connection = mysql.createConnection({multipleStatements: true});

by default node-mysql will only execute only one query on time if you've enabled the multipleStatements on create connect setting then your code should work. hope it work for you.

Answer 2

I had to run the queries separately to get them to work.

E.g.,

var config = {user: "root", password: "root"}; // your config
var sql1 = "SET @i = 0";
var sql2 = " SELECT POSITION FROM ( " +
    "SELECT name, @i:=@i+1 AS POSITION " +
    "FROM database.table ti WHERE name='Johny' ORDER BY points ASC) t " +
    "WHERE name='Johny'";

var result = [];
var connection = mysql.createConnection(config);

var query1 = connection.query(sql1);
query1.on("error", function (err) {
    // handle error
});
query1.on("end", function () {
    var query2 = connection.query(sql2);
    query2.on("error", function (err) {
        // handle error
    });
    query2.on("result", function (row) {
        result.push(row);
    });
    query2.on("end", function () {
        connection.end(); // close connection
        console.log(result); // display result
    });
});

Hope this helps.