3

I have a stored procedure that works fine in the MySql workbench but when it is run by Spring, using the ResourceDatabasePopulator, it fails with a syntax error. Reading around there are similar issues where the delimiter should be set in the script, I have followed these but the same error persists.

This is the SP

DELIMITER $$
CREATE PROCEDURE userAttributesOrder()
READS SQL DATA
    BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i INT DEFAULT 0;
    DECLARE attCount INT DEFAULT 0;
        DECLARE cod VARCHAR(64) DEFAULT NULL;
        DECLARE curs CURSOR FOR SELECT CODE FROM PA_ATTR_TYPE ORDER BY NAME ASC;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        SET i = 0;    
        OPEN curs;
            read_loop: LOOP
                FETCH curs INTO cod;
                IF done THEN
                    LEAVE read_loop;
                END IF;
                INSERT INTO LAYOUT(ELEMENT_TYPE, ELEMENT_CODE, LAYOUT_ORDER) VALUES ('PA_ATTRIB', cod, i);
                SET i = i + 1;
            END LOOP;
        CLOSE curs;
    END$$
DELIMITER ;

And this is the exception

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #4 of resource class path resource [database/updates/23-update.sql]: DELIMITER $$ CREATE PROCEDURE user
AttributesOrder() READS SQL DATA BEGIN DECLARE done INT DEFAULT FALSE; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corre
sponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE PROCEDURE userAttributesOrder() READS SQL DATA BEGIN DECLARE' at line 1
        at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:472) ~[spring-jdbc-4.1.6.RELEASE.jar:4.1.6.RELEASE]

Does anyone have a clue how to fix this? It is just Spring which I find frustrating as using plain Java I would not have the issue.

UPDATE Spring code included As requested here is the Spring code I have inherited that runs the scripts.

DataSourceInitializer initializer = new DataSourceInitializer();
initializer.setDataSource(dataSource);
ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();

for (String script : orderedScripts) {
    databasePopulator.addScript(new ClassPathResource(script));
}

initializer.setDatabasePopulator(databasePopulator);
initializer.afterPropertiesSet();
M. Deinum
  • 115,695
  • 22
  • 220
  • 224
Gurnard
  • 1,773
  • 22
  • 43
  • 1
    That script won't work with the `ResourceDatabasePopulator` as that expects that `;` is the delimiter and it uses that to determine the end of the line. – M. Deinum Mar 10 '16 at 08:32
  • @M. Denium hello thanks for the reply. Yes I realise that is the issue but the question is how to fix it so it will run. You surely can't say that stored procedures cannot be used with ResourceDatabasePopulator! Setting the DELIMITER makes no difference and setting the delimiter in Spring with databasePopulator.setSeparator("$$"); Causes all the other scripts to fail, which is expected. Anyone know how I can fix this so I can run a stored procedure with ResourceDatabasePopulator? – Gurnard Mar 10 '16 at 08:54
  • You cannot. Also you aren't running a stored procedure you are defining it. As a work around you can try putting this in a separate `ResourceDatabasePopulator` or implement your own `DatabasePopulator` with the delimiter set, and use a default one for the other scripts. – M. Deinum Mar 10 '16 at 09:02
  • yeah I define it and run it in the next line... but thanks. Nice to know that Spring once more restricts what I want to do. I'll create a temp table, form my data, then copy across. Thanks for the response. – Gurnard Mar 10 '16 at 09:49

2 Answers2

1

I use ;; as my separator setting it with ResourceDatabasePopulator.setSeperator()

This doesn't seem to break other stuff and runs in my DB IDE. My DB IDE (dbVisualizer) sees 2 seperators and doesn't care.

denov
  • 11,180
  • 2
  • 27
  • 43
0

I had been facing this issue too and just now I found a solution. Actually 3! So you can choose any of them which suits your needs. But the key to it is the correctly formatted String which represents the DB scripts to create a SP. I haven't figure out how to combine dropping and creation of a SP in one SQL script/String, but I'm happy with those solutions anyway.

FYI:

Solution 1: using JdbcTemplate and Java String

It is very important to have trailing spaces in your nicely formatted String sql script

        String sqlSP = "CREATE PROCEDURE test_stored_proc (IN pInstanceId varchar(255)) " +
                "BEGIN " +
                    "SELECT * FROM vw_subscriptions WHERE instanceId = pInstanceId; " +
                "END";

        jdbcTemplate.execute(sqlSP);

Solution 2: using ResourceDatabasePopulator & ClassPathResource to load SQL script in a file

        ClassPathResource resource = new ClassPathResource("/data/create-sp_TEST_STORED_PROC.sql");
        jdbcTemplate.execute("DROP procedure IF EXISTS `test_stored_proc`;");

        ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
        databasePopulator.setSeparator(ScriptUtils.EOF_STATEMENT_SEPARATOR);
        databasePopulator.execute(testSetupDataSource);

Solution 3: using ScriptUtils & ClassPathResource to load SQL script in a file

        ClassPathResource resource = new ClassPathResource("/data/create-sp_TEST_STORED_PROC.sql");
        jdbcTemplate.execute("DROP procedure IF EXISTS `test_stored_proc`;");

        ScriptUtils.executeSqlScript(Objects.requireNonNull(testSetupDataSource).getConnection(), new EncodedResource(resource),
                false, false,
                ScriptUtils.DEFAULT_COMMENT_PREFIX, ScriptUtils.EOF_STATEMENT_SEPARATOR,
                ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER, ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER);

Content of the create-sp_TEST_STORED_PROC.sql script

For solution #1 and #2 the same rule as for the 1st solution applies:

  • The formatting and presence of whitespace characters is important especially an extra trailing space at the end of each line.

So the below code is archived by my vim setup of representing whitespace characters

CREATE·PROCEDURE·test_stored_proc·(IN·pInstanceId·varchar(255))~¬
BEGIN~¬
–→SELECT·*·FROM·vw_subscriptions·WHERE·instanceId·=·pInstanceId;~¬
END;~¬

I believe it is internally represented as one line piece of string: CREATE PROCEDURE test_stored_proc (IN pInstanceId varchar(255)) BEGIN SELECT * FROM vw_subscriptions WHERE instanceId = pInstanceId; END

Almost full source-code can be found at my GitHub

DelphyM
  • 322
  • 2
  • 14