ScriptUtils#splitSqlScript cannot deal with semicolons in stored procedures [SPR-15438]

[spring-projects-issues]

Phil Webb opened SPR-15438 and commented

Originally raised with Spring Boot the org.springframework.jdbc.datasource.init.ScriptUtils#splitSqlScript method cannot deal with semicolons that are part of a stored procedure.

For example:

CREATE FUNCTION count_clients() RETURNS integer
    LANGUAGE plpgsql
    AS $$begin
  select count(*) from clients;
end;$$;

---

Affects: 4.3.7

Issue Links:

• 19952 Oracle SQL hints are ignored as comments in ScriptUtils

2 votes, 5 watchers

[spring-projects-issues]

Juergen Hoeller commented

Like #19952, this is hard to address by default. Using a different quoting syntax or a different separator configuration is the easiest way out here for the time being.

[spring-projects-issues]

vrnsky commented

Juergen Hoeller  fake separator is not cool. For example in my case app get schema sql file outside from app. And schema valid, but have sql triggers which does not correct compile by 

ResourceDatabasePopulator

[spring-projects-issues]

jhvhs commented

I wonder whether the org.flywaydb.core.internal.database package can be leveraged. I believe there's about 6K+ lines of code the sole purpose of which is just to deal with this issue for about a dozen database engines.

See https://github.com/flyway/flyway/tree/e3c9adaac18f46017ad524cbe0df26d13ab33c00/flyway-core/src/main/java/org/flywaydb/core/internal/database for more details.

[spring-projects-issues]

vrnsky commented

jhvhs hm...looks like acceptable for me. But I think that spring community will be glad if we fix this ticket

[rj-hwang]

My solution: set spring.datasource.separator=^^^ END OF SCRIPT ^^^ .

By this setting, ScriptUtils does not separate the content of the SQL file into multiple statements.

[glickid]

ScriptUtils (spring version 5.2.5.RELEASE) failed parsing the following:

CREATE FUNCTION public.add_user(user_name character varying, user_id integer) RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$DECLARE
BEGIN
    INSERT INTO tbl_users (username, id) VALUES(user_name, user_id);
END$$;

with error:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement....nested exception is org.postgresql.util.PSQLException: Unterminated dollar quote started at position....Expected terminating $$

Is there a solution for this, yet?

[desarrollo-itic]

My solution: set spring.datasource.separator=^^^ END OF SCRIPT ^^^ .

By this setting, ScriptUtils does not separate the content of the SQL file into multiple statements.

Great. Really works!

[kavita234]

Hi team, I am still not able to use ScriptUtils to execute a stored procedure. Receiving stacktrace that says "You have an error in your SQL Syntax" . Is there any way to resolve it?

[kavita234]

My solution: set spring.datasource.separator=^^^ END OF SCRIPT ^^^ . By this setting, ScriptUtils does not separate the content of the SQL file into multiple statements.

Great. Really works!

Can you share the entire procedure, I am still not able to work it out

[sbrannen]

@kavita234, various overloaded versions of the executeSqlScript(...) method in ScriptUtils allow you to provide the separator to use.

See the Javadoc for details.

separator - the script statement separator; defaults to ";" if not specified and falls back to "\n" as a last resort; may be set to "^^^ END OF SCRIPT ^^^" to signal that the script contains a single statement without a separator

What you're looking for is the EOF_STATEMENT_SEPARATOR constant.

[julian-ev]

Thanks it works. I used ScriptUtils.executeSqlScript(conn, new EncodedResource(resource), false, false, "--", "^^^ END OF SCRIPT ^^^","/*", "*/");.

[MarcoMartins86]

Not sure what this does spring.datasource.separator=^^^ END OF SCRIPT ^^^, I've tried and didn't work, I'm using the @Sql annotation for integration tests. What worked was this https://stackoverflow.com/a/52173527/3561396, replace $$ with '.