Using Spring's "jdbc:initialize-database", how do I run a script with a stored procedure?

initialization, junit, mysql, spring, stored-procedures

Solution

`<jdbc:script />` doesn't allow usage of DELIMITER statement in the included script but you can use the `separator` property with same result.

In your `truncate_tables.sql` use only the `#` delimiter and specify it in the `separator` property like so:

drop procedure if exists truncate_tables#

create procedure truncate_tables()
begin
 declare tab_name varchar(64);
 declare done tinyint unsigned default 0;

 declare table_cur cursor for select t.table_name
 from
  information_schema.schemata s
  inner join information_schema.tables t on s.schema_name = t.table_schema
 where
   s.schema_name = database() and t.table_type = 'BASE TABLE';

 declare continue handler for not found set done = 1;

 open table_cur;
 repeat
   fetch table_cur into tab_name;
   set @cmd = concat('truncate table ', tab_name);

   prepare stmt from @cmd;
   execute stmt;
 until done end repeat;

 close table_cur;
end #

SET FOREIGN_KEY_CHECKS=0#
CALL truncate_tables#
SET FOREIGN_KEY_CHECKS=1#

and in the application context...

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script separator="#" location="classpath:truncate_tables.sql"/>
    <jdbc:script location="classpath:db-test-data.sql"/>    
</jdbc:initialize-database>

Problem

I'm using Spring 3.1.1.RELEASE, JUnit 4.8.1, and MySQL 5.5. I want to truncate all my table data before each test, but am getting errors in Spring even though I can execute the following script (truncate_tables.sql) fine via the command line … ``` drop procedure if exists truncate_tables; delimiter # create procedure truncate_tables() begin declare tab_name varchar(64); declare done tinyint unsigned default 0; declare table_cur cursor for select t.table_name from information_schema.schemata s inner join information_schema.tables t on s.schema_name = t.table_schema where s.schema_name = database() and t.table_type = 'BASE TABLE'; declare continue handler for not found set done = 1; open table_cur; repeat fetch table_cur into tab_name; set @cmd = concat('truncate table ', tab_name); prepare stmt from @cmd; execute stmt; until done end repeat; close table_cur; end # SET FOREIGN_KEY_CHECKS=0; CALL truncate_tables; SET FOREIGN_KEY_CHECKS=1; ``` However, using this Spring application context … ``` <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/my_db" /> <property name="username" value="myuser" /> <property name="password" value="bypass" /> </bean> ... <jdbc:initialize-database data-source="dataSource"> <jdbc:script location="classpath:truncate_tables.sql"/> <jdbc:script location="classpath:db-test-data.sql"/> </jdbc:initialize-database> ``` However, I get the error ``` Caused by: 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 &apos;delimiter # create procedure truncate_tables() begin declare tab_name varchar(6&apos; at line 1 at sun.reflect.GeneratedConstructorAccessor419.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:842) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:681) at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:184) ... 50 more ``` Any way I can fix the above script? It is not an option to use the "extends AbstractTransactionalJUnit4SpringContextTests" extension in my JUnit tests.

Original source