MySQL JDBC does not behaves as the MySQL Workbench

Question

I'm running the following from the MySQL Workbench, and from my local tomcat using jdbc.

START TRANSACTION;
CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_tables` (
    `_id` BIGINT NOT NULL AUTO_INCREMENT,
    `class_type` VARCHAR(100) NOT NULL,
    `schema` VARCHAR(30) NOT NULL,
    `table_name` VARCHAR(30) NOT NULL,
    `server_id` VARCHAR(30) NULL,
    `fields` VARCHAR(0) NOT NULL, PRIMARY KEY (`_id`),
UNIQUE INDEX `_id_UNIQUE` (`_id` ASC));
COMMIT;

It is a copy of query right before the execution which I compose in order to run via the jdbc which fails time after time with the following error:

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 'CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_table' at line 2

So technically even if only execute the create table this fails with the same error, only line 1.

BUT, when I run the same query via the MySQL Workbench, it works wonderfully... time and time again!

Any idea what I'm missing?

Thanks.

Answer 1

Maybe the problem is that the Workbench is running your statements one at a time without you being noticed, while JDBC exactly sends to the database what you exactly ask it to send.

See this answer, where a parameter is used on the connection driver to allow many SQL statements over a single JDBC statement object.