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,

SET @i = 0; SELECT POSITION FROM ( SELECT name, @i:=@i+1 AS POSITION FROM test_database.test_table ti WHERE name='Johny' ORDER BY points ASC) t WHERE name='Johny' — user1972670
– user1972670, Commented Dec 16, 2013 at 13:57
This will return the position of the result in the result set. For example : If 'Johny' is in second row in result set it will return '2' in POSITION column — user1972670
– user1972670, Commented Dec 16, 2013 at 13:59

zergtant · Accepted Answer · 2016-06-08 15:30:08Z

9

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.

answered Jun 8, 2016 at 15:30

zergtant

911 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sijo vijayan Over a year ago

This may lead to an SQL Injection attack , refer npmjs.com/package//mysql#multiple-statement-queries

Jon Saw · Accepted Answer · 2015-06-05 00:31:02Z

3

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.

edited Jun 5, 2015 at 0:31

answered Jun 2, 2015 at 9:10

Jon Saw

8,1306 gold badges52 silver badges60 bronze badges

2 Comments

Thomas Cayne Over a year ago

I have been searching for this for a year. Thanks you. I wonder why it never showed up. For some reason the SET @variable wasn't working with all suggestions made elsewhere. Yours worked flawlessly.

Jon Saw Over a year ago

Glad it helped 😊

Mostafa Mohamed · Accepted Answer · 2022-06-03 13:17:09Z

0

solution: you don't have to use the @x or @y, it won't work in node-mysql you must use this.

SELECT * from (SELECT * as column1 FROM table1) as x, (Select * as column2 From table2) as y);

this works for me.

answered Jun 3, 2022 at 13:17

Mostafa Mohamed

12 bronze badges

Collectives™ on Stack Overflow

MySQL query variables in node

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Collectives™ on Stack Overflow

MySQL query variables in node

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related