MySQL User-Defined Variables in PreparedStatement

[velr]

Hello!

I am working on a server program that has a connection to a MySQL database.
In one of my sql:s i need to use User-Defined Variables but i can't get it to work.

The sql looks something like this: "SET @rank=0; SELECT id FROM (SELECT @rank:=@rank+1 AS rank ..." I'm wondering if it's not possible to have two "statements" ending with ";"in one PreparedStatement. When i run the sql in phpMyAdmin it works but from the server i get an exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM (SELECT @rank:=@rank+1 AS rank, id, ((`wrong_answer_count` / (`co' at line 1 Best regards veLr

[DavidKroukamp]

maybe try removing the semi colon at

@rank=0;

[velr]

maybe try removing the semi colon at

@rank=0;

Tried that and it did'nt work :sad:

[~s.o.s~]

> The sql looks something like this [...]

AFAICT, the problem isn't the semicolon but the JDBC driver which refuses to consider the two parts as a single statement hence the exception. Also, the query doesn't look like standards compliant SQL (the SET part).

You have two options: wrap the "custom" query in a stored procedure and call the same using JDBC (look into CallableStatement) or convert the query to something which is valid SQL statement as per the SQL standard.

[gbulfon]

Hi, the solution is to issue the two statements in two subsequent executes.
Variables are kept within session, until closed:

stmt.execute("select @i := 0");
stmt.executeUpdate("update mytable set pos=(select @i := @i + 1)");

[JamesCherrill]

@gbulfon
Your conrtributions are very welcome here, but please check the dates before adding to an existing thread - this one is 5 months old, so either the OP has found a solution or he doesn't care any more.