Finn Finn - 4 months ago 8
SQL Question

JDBC throws MySQL syntax Exception, despite valid query



Hello helpful folks,

I've a small part in an application which creates a new db schema based on a mysqldump.
The relevant part looks like this:

log.info("= setup basic database");
// Execute each command in the dump
for (String query : dump.split(";")) {
statement.execute(query);
}


This works fine but some statements just break with something like

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 ''ot_subtotal.php' at line 1


The query in this case is


INSERT INTO `configuration` VALUES (194,'MODULE_ORDER_TOTAL_INSTALLED','ot_subtotal.php;ot_discount.php;ot_coupon.php;ot_shipping.php;ot_cod_fee.php;ot_gv.php;ot_subtotal_no_tax.php;ot_tax.php;ot_total_netto.php;ot_total.php',6,0,NULL,'2016-06-17 15:27:24',NULL,NULL);


If I replace the string "'ot_subtotal.php;ot_discount.php;ot_coupon.php;ot_shipping.php;ot_cod_fee.php;ot_gv.php;ot_subtotal_no_tax.php;ot_tax.php;ot_total_netto.php;ot_total.php'" with something like "hello world" it works and breaks at a another query later on.

The thing is, I'm able to import the dump via the terminal command "mysql ... < dump.sql" without a problem.

Any ideas?

Answer

This is happening because of

for (String query : dump.split(";")) {
    statement.execute(query);
}

You are splitting at ; which result in incomplete queries.

For example:

INSERT INTO `configuration` VALUES (194,'MODULE_ORDER_TOTAL_INSTALLED','ot_subtotal.php;ot_discount.php;ot_coupon.php;ot_shipping.php;ot_cod_fee.php;ot_gv.php;ot_subtotal_no_tax.php;ot_tax.php;ot_total_netto.php;ot_total.php',6,0,NULL,'2016-06-17 15:27:24',NULL,NULL);

Above query after split at ; will be

INSERT INTO `configuration` VALUES (194,'MODULE_ORDER_TOTAL_INSTALLED','ot_subtotal.php

Which is incorrect.

Comments