CODEWITHSUNDEEP
javascriptmysqldiscord.jsmysql-error-1064
KB_not_a_php_lover
KB_not_a_php_lover

Reputation: 15

unable to find mysql syntax error in discordjs

I'm trying to make a sql query in discordjs using the mysql package. The query (if used in the sql server) works perfectly as intended, but I get a PARSE ERROR trying to use the same query in js:

SET @ROW_NUMBER = 0; SELECT s.level,s.exp,s.number 
FROM ( SELECT userID,level,exp,(@ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number FROM levels ORDER BY level DESC) AS s 
WHERE userID = "${userID}";

The code in discordjs:

//some code...
con.query(`SET @ROW_NUMBER = 0; SELECT s.level,s.exp,s.number 
FROM ( SELECT userID,level,exp,(@ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number 
FROM levels ORDER BY level DESC) AS s 
WHERE userID ="${userID}";`,(err,rows) =>{
 if(err)return console.log(err); 
//other code

The error I get:

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'SELECT s.level,s.exp,s.number FROM ( SELECT userID ,level ,exp ,(@ROW_NUMBER:...' at line 1",
sqlState: '42000',
index: 0,
sql: 'SET @ROW_NUMBER = 0; SELECT s.level,s.exp,s.number FROM ( SELECT
userID ,level ,exp ,(@ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number FROM levels
ORDER BY level DESC) AS s WHERE userID = "258217948819357697";'

Upvotes: 0

Views: 117

Answers (1)

Akina
Akina

Reputation: 42622

Error message shows that your connector does not allow multiquery (two queries sent as one batch).

Move variable initialization into FROM clause.

SELECT s.level,s.exp,s.number 
FROM ( SELECT userID, level, exp, ( @ROW_NUMBER:=@ROW_NUMBER + 1 ) AS number 
       FROM levels, (SELECT @ROW_NUMBER := 0) init_variable
       ORDER BY level DESC ) AS s 
WHERE userID = "${userID}";

PS. If your MariaDB version is 10.2.1 or above then use ROW_NUMBER() window function in CTE instead of the variable.

Upvotes: 1

Related Questions