Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
spring-projects-issues opened this issue Apr 11, 2017 · 12 comments
Labels
in: data Issues in data modules (jdbc, orm, oxm, tx) type: enhancement A general enhancement

Comments

@spring-projects-issues
Copy link
Collaborator

spring-projects-issues commented Apr 11, 2017

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:

2 votes, 5 watchers

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Apr 12, 2017

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link

rj-hwang commented Jun 18, 2020

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
Copy link

glickid commented Jul 21, 2020

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?

@sbrannen sbrannen changed the title org.springframework.jdbc.datasource.init.ScriptUtils#splitSqlScript cannot deal with semicolons in stored procedures [SPR-15438] ScriptUtils#splitSqlScript cannot deal with semicolons in stored procedures [SPR-15438] Jul 21, 2020
@sbrannen sbrannen added the in: data Issues in data modules (jdbc, orm, oxm, tx) label Jul 21, 2020
@desarrollo-itic
Copy link

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
Copy link

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
Copy link

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
Copy link
Member

sbrannen commented Oct 2, 2021

@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
Copy link

julian-ev commented Apr 3, 2023

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

@MarcoMartins86
Copy link

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 '.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: data Issues in data modules (jdbc, orm, oxm, tx) type: enhancement A general enhancement
Projects
None yet
Development

No branches or pull requests

8 participants