Error in SQL syntax when running SQL in Java but not in HeidiSQL

Question

I'm trying to run an insert or update on a table - the string generated from below works fine when copy pasted into HeidiSQL but throws SQLSyntaxErrorExceptions when run from Java:

Statement statement = con.createStatement();
String escapedXML = EscapeString(billboard.getXml());
String sql = String.format(
    "DELIMITER $ \r\nBEGIN NOT ATOMIC\r\n" +
    "IF EXISTS(SELECT * FROM billboards where Name='%s') THEN UPDATE billboards SET XML='%s' where Name='%s';\r\n" +
    "ELSE insert into billboards(Name, XML, CreatorName) values('%s', '%s', '%s');\r\n" +
    "END IF;\r\n" +
    "END $\r\n" +
    "DELIMITER ;", billboard.getName(), escapedXML, billboard.getName(), billboard.getName(), escapedXML, billboard.getCreatorName());
// Insert or update billboard
statement.execute(sql);

I can't figure out why.

Hi! Knowing the **whole** error it throws would be very helpful in order to try and find out what is happening. Could you please paste it here? — Mario MG, May 30 '20 at 09:47
Sure can: java.sql.SQLSyntaxErrorException: (conn=113) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER |\r — Connor Wilson, May 30 '20 at 09:50
@ConnorWilson Why your question has tag `sql-server` but not `MariaDB`? — SternK, May 30 '20 at 09:55
Hi have a look at the error log It usually hints you where the thing goes wrong. Perhaps that `\r` is not a proper delimiter for the SQL syntax. — Plain_Dude_Sleeping_Alone, May 30 '20 at 10:12

score 0 · Answer 1 · answered May 30 '20 at 10:32

I would recommend using the insert ... ok duplicate key syntax here rather than a code block. This is more efficient, and implements the lockout a single statement, which should avoid the problem you meet when running the query from your php code.

insert into billboards(Name, XML, CreatorName) 
values(?, ?, ?)
on duplicate key update set XML = values(XML)

For this to work, you need a unique (or primary key) constraint on column Name.

Also, consider using a parameterized query rather than concatenating variables in your query stringW Escaping is inefficient and does not really make your code safer.

Nowhere Man · Answer 2 · 2020-05-30T10:54:10.040

You should have tried NamedParameterStatement with your query to facilitate setting of string parameters and avoid their duplication (using refactored query suggested in GMB's earlier answer):

String sql = "INSERT INTO billboards (Name, XML, CreatorName) VALUES (:name, :xml, :creator) "
             + "ON DUPLICATE KEY UPDATE SET XML = :xml";

NamedParameterStatement statement = new NamedParameterStatement(con, sql);
statement.setString("name", billboard.getName());
statement.setString("xml", EscapeString(billboard.getXml()));
statement.setString("creator", billboard.getCreatorName());

// Insert or update billboard
statement.execute(sql);

Stephen C · Answer 3 · 2020-05-30T11:41:26.840

0

The reason that you are getting a syntax error is that DELIMITER is a MySQL client command and not an SQL statement. MySQL commands may not be used in with JDBC.

For more information:

Delimiters in MySQL

edited May 30 '20 at 11:41

answered May 30 '20 at 11:32

Stephen C

698,415
94
811
1,216

Error in SQL syntax when running SQL in Java but not in HeidiSQL

3 Answers3