Multiple sql statments (execute all) with eclipse data tools

I use the Eclipse SQL Editor (Eclipse Data Tool Platform 1.9.0) in STS 2.7.1 based on Eclipse 3.7. (Database is MySQL 5.5)

If I have the SQL Scrapbook (or any other file) and put some SQL DDL statements in it where every single statement is written on a single line ending with ; (that is the configured delimiter in eclipse) then I can execute all statements together (Execute All Ctrl+Alt+X). That works without problems, eclipse execute each statment as a single statement.

But if i have normal insert statements in the same format (each line/statement ending with ;) then executing all statements together fails. Eclipse seams to try to handle all the statements in one SQL statement. That fails and MySql report an syntax error.

INSERT INTO `folder` (`id`, `businessId`, `status`, `title`, `parent_Folder_fk`) VALUES(1, 1544565486, 'ACTIVE', 'Root', NULL);
INSERT INTO `folder` (`id`, `businessId`, `status`, `title`, `parent_Folder_fk`) VALUES(6, 1802811831392782301, 'ACTIVE', 'Ralphz', 1);
...

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 'INSERT INTO folder (id, businessId, status, title, parent_Folder_fk)' at line 2

I can execute each lin开发者_运维百科e separate (Select the line and Excecute Selection Alt+X). So I guess there is no failure in the statements, but something is wrong with splitting the statements.

Where is my mistake?

Found the cause of the problem and a solution.

Execute all does not separate insert into statements properly along delimiters (works fine with other statements tho), and newer MySQL versions disallow multiple statements in a line, most likely to prevent SQL injections.

I doubt the former can be easily fixed, however there is a configuration property of MySQL to allow multiline SQL statements. You just have to add "allowMultiQueries=true" to Data Source Explorer / properties of a data source / Driver Properties / Optional / Additional Properties.

To add more than one row to a table you can use this command:

INSERT INTO `folder` (`id`, `businessId`, `status`, `title`, `parent_Folder_fk`) VALUES(1, 1544565486, 'ACTIVE', 'Root', NULL), (6, 1802811831392782301, 'ACTIVE', 'Ralphz', 1);

OR

Load using the plugin like explained HERE.

To insert in multiple tables please test more two things:

1. Create a stored procedure with the multiple inserts;
2. Create a stored procedure for each insert on a table and execute multiple stored procedures.