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

maybe try removing the semi colon at

@rank=0;

Tried that and it did'nt work :sad:

> 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.

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)");

@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.

This article has been dead for over six months. Start a new discussion instead.