0

When i have multiple statements in my MySQL query, it is giving me the error: SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server.

Here is my query:

SET @loner_900_id = 1722;
SELECT ds.device_id AS deviceID, dsl.* 
FROM devices_statuses AS ds 
INNER JOIN devices_statuses_location AS dsl ON ds.id = dsl.device_status_id 
WHERE ds.device_id IN (@loner_900_id);

In this query if i remove the SET @loner_900_id = 1722; and replace the variable @loner_900_id with the value 1722, then it works without a problem. But, i would like to run with the "SET @loner_900_id = 1722;" statement. Please share your thoughts on this.

This is what i do to retrieve the data from the above query. I used JDBC connection. 

String url = "jdbc:mysql://<HOST>/test?user=xyz&password=xyz&allowMultiQueries=true";
conn = DriverManager.getConnection(url);
Statement st = conn.createStatement();
conn.createStatement();
resultSet = st.executeQuery(sql);

The error is thrown on the line "resultSet = st.executeQuery(sql);"

Forrest R. Stevens
  • 3,435
  • 13
  • 21
sridhar249
  • 4,471
  • 6
  • 26
  • 27
  • 1
    most mysql drivers do NOT allow multiple statements in a single query() call. it's a basic defense against some forms of sql injection attack. You'll have to call query() multiple times: `query('set ...'); query('select ...');` – Marc B Jul 15 '15 at 17:56
  • 1
    Also, when you set user-defined variable like this one, it should be `SET @variable := value`. See http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference – kittykittybangbang Jul 15 '15 at 17:58
  • @Marc, I can do that, but in that case, since each statement is run in a separate session, the 2nd query that i run, will not have any idea about the variable that i have set in my 1st statement. Right? – sridhar249 Jul 15 '15 at 17:59
  • 1
    mysql vars are done on a per-connection basis. as long as you use the SAME connection for the two queries, things will "just work". – Marc B Jul 15 '15 at 18:04
  • @MarcB, thanks a lot, Your approach worked for me. – sridhar249 Jul 15 '15 at 18:11
  • @kittykittybangbang, i agree on your syntax. Thank you! – sridhar249 Jul 15 '15 at 18:12
  • Why don't you use a `PreparedStatement` instead and its `setInt` instead? – Mark Rotteveel Jul 16 '15 at 10:50
  • @MarkRotteveel, thanks for the suggestion. Will give that a try. – sridhar249 Jul 20 '15 at 05:19

0 Answers0