SQL Invalid Syntax in Java but not in Workbench

Question

Why does this statement work fine in workbench but not in Java.

SET @sqlstmt := IF( @exist <= 0, 'select ''INFO: Key does not exist.''', 'ALTER TABLE `SOMETABLE` DROP FOREIGN KEY `SOMEKEY`');

In Java I get

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 'SET @sqlstmt := IF( @exist <= 0, 'select ''INFO: Key does not exist.''', 'ALTER ' at line 1

Those quotes look weird. Shouldn't it be " instead of two single quotes? — 11684
– 11684, Commented Jun 24, 2014 at 15:10
have you tried simplifying the query to isolate the part causing the error? — djechlin
– djechlin, Commented Jun 24, 2014 at 15:10
I had thought a single quote should be escaped by a single quote when already inside single quotes. Isn't that true? — ademartini
– ademartini, Commented Jun 24, 2014 at 15:11
I haven't seen it before. Could you try to escape these quotes not with other quotes but with backslashes? — 11684
– 11684, Commented Jun 24, 2014 at 15:16

Andy · Accepted Answer · 2014-06-24 17:54:09Z

1

It turned out to be the fact that multiple statements were being executed (separated by semicolons). In JDBC MYSQL you need allowMultiQueries=true

edited Jun 24, 2014 at 17:54

Andy♦

50.8k62 gold badges181 silver badges240 bronze badges

answered Jun 24, 2014 at 17:51

ademartini

1,4511 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Invalid Syntax in Java but not in Workbench

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related