0

I'm executing a script through a Java web service I wrote to execute on a MySQL database. When executing the query in phpMyAdmin it completes successfully, however as soon as it is executed by my web service it returns:

com.mysql.jdbc.exceptions.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 @OBJECTIVE_1_DESCRIPTION := OBJECTIVE_DESCRIPTION, @OBJECTIVE_1_LVL := OB' at line 1

The following query is executed as a prepared statement read from a .sql file:

(This is only a snippet of the relevant code, I have added new lines to improve readability, in the actual file the query there are only new lines after each ";")

SET @USER_ID = ?, 
@EMAIL = ?, 
@NAME = ?, 
@SURNAME = ?, 
@SUBURB = ?, 
@CITY = ?, 
@PASSWORD = ?, 
@RELIABLE_OCR_RESULTS = 0, 
@HOUSEHOLD_SIZE = 0, 
@DATE = NOW(), 
@OBJECTIVE_1_ID = '4E2DEA7B-037A-4958-8696-2B73EDD3E08E', 
@NEW_OBJECTIVE_1_ID = 'E46027A1-CE36-4413-9E64-7A217FFB9CEB', 
@OBJECTIVE_1_DESCRIPTION = REPLACE(@OBJECTIVE_1_DESCRIPTION, '*', @OBJECTIVE_1_TARGET), 
@OBJECTIVE_1_PROGRESS  = 0.0,  
@OBJECTIVE_1_COMPLETED  = 0, 
@COMPLETED_OBJECTIVES = NULL, 
@NEW_OBJECTIVE_1_DESCRIPTION = REPLACE(@NEW_OBJECTIVE_1_DESCRIPTION, '*', @NEW_OBJECTIVE_1_TARGET), 
@NUM_COMPLETED_OBJECTIVES = 0;

SELECT @OBJECTIVE_1_DESCRIPTION := OBJECTIVE_DESCRIPTION, @OBJECTIVE_1_LVL := OBJECTIVE_LEVEL, @OBJECTIVE_1_TARGET := OBJECTIVE_COMPETION_COUNT, @REWARD_1_CODE := OBJECTIVE_REWARD , @OBJECTIVE_1_REWARD := REWARD_VALUE, @OBJECTIVE_1_REWARD_TYPE := REWARD_TYPE 
FROM Objectives 
JOIN Objective_Reward_Codes 
ON REWARD_ID = OBJECTIVE_REWARD 
WHERE OBJECTIVE_ID = @OBJECTIVE_1_ID;

SELECT @NEW_OBJECTIVE_1_DESCRIPTION := Objectives.OBJECTIVE_DESCRIPTION, @NEW_OBJECTIVE_1_LVL := Objectives.OBJECTIVE_LEVEL, @NEW_OBJECTIVE_1_TARGET := Objectives.OBJECTIVE_COMPETION_COUNT, @NEW_REWARD_1_CODE := Objectives.OBJECTIVE_REWARD , @NEW_OBJECTIVE_1_REWARD := Objective_Reward_Codes.REWARD_VALUE, @NEW_OBJECTIVE_1_REWARD_TYPE := Objective_Reward_Codes.REWARD_TYPE 
FROM Objectives 
JOIN Objective_Reward_Codes 
ON Objective_Reward_Codes.REWARD_ID = Objectives.OBJECTIVE_REWARD 
WHERE OBJECTIVE_ID = @NEW_OBJECTIVE_1_ID;
DELIMITER ;

Is it possible that it is caused by the version of Tomcat or the encoding of the query sent by the JDBC?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Dévan Coetzee
  • 104
  • 8
  • There is a lot of proprietary MySQL syntax (like the `@` characters in the variables) in your SQL script and I don't think you can use any of that in a prepared statement. You could e.g. create a stored procedure based on the script and call that over JDBC. – Mick Mnemonic Sep 04 '16 at 10:17
  • @MickMnemonic, thanks for the info. But why isn't the @ character triggering a syntax error sooner? As I said the script worked perfectly when I tested it in phpMyAdmin. Why won't it work with a prepared statement? As far as I can tell the syntax error occurs right after the first ";" – Dévan Coetzee Sep 04 '16 at 10:31
  • 3
    Most JDBC drivers only allow executing a single statement per prepared statement. For this reason, semicolons are also not allowed. – Mick Mnemonic Sep 04 '16 at 10:45
  • Thank you @MickMnemonic and GordThompson, that was the issue. I changed DBs and accidentally removed that part of my connection string. Thanks for all the help! – Dévan Coetzee Sep 05 '16 at 07:06

0 Answers0