SQL Scripts with newline separators no longer work [SPR-11560]

[spring-projects-issues]

Gary Russell opened SPR-11560 and commented

Attempting to execute the following SQL script using a ResourceDatabasePopulator...

CREATE ALIAS IF NOT EXISTS GET_PRIME_NUMBERS FOR "org.springframework.integration.jdbc.storedproc.h2.H2StoredProcedures.getPrimes"
CREATE ALIAS IF NOT EXISTS GET_RANDOM_NUMBER FOR "org.springframework.integration.jdbc.storedproc.h2.H2StoredProcedures.random"

... now results in the following exception:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE ALIAS IF NOT EXISTS GET_PRIME_NUMBERS FOR ""org.springframework.integration.jdbc.storedproc.h2.H2StoredProcedures.getPrimes"" CREATE[*] ALIAS IF NOT EXISTS GET_RANDOM_NUMBER FOR ""org.springframework.integration.jdbc.storedproc.h2.H2StoredProcedures.random"""; SQL statement:
CREATE ALIAS IF NOT EXISTS GET_PRIME_NUMBERS FOR "org.springframework.integration.jdbc.storedproc.h2.H2StoredProcedures.getPrimes" CREATE ALIAS IF NOT EXISTS GET_RANDOM_NUMBER FOR "org.springframework.integration.jdbc.storedproc.h2.H2StoredProcedures.random" [42000-175]

Using ; as the statement separator in the script file resolves this particular case.

This appears to have been caused by the fix to #14165

---

Affects: 4.0.3

Issue Links:

• Support EOF as statement separator in SQL scripts [SPR-11687] #16310 Support EOF as statement separator in SQL scripts ("is depended on by")
• H2 aliases are not supported in SQL ScriptUtils [SPR-15896] #20450 H2 aliases are not supported in SQL ScriptUtils
• Support multi-line SQL comments in ResourceDatabasePopulator and JdbcTestUtils [SPR-9531] #14165 Support multi-line SQL comments in ResourceDatabasePopulator and JdbcTestUtils

Referenced from: commits cc0ae3a, bb67cd4, 9230b38

[spring-projects-issues]

Sam Brannen commented

Fixed as described in the comments for GitHub commit bb67cd4:

Fix regression for newline separators in SQL scripts

Changes made in conjunction with #14165, introduced a regression with regard to support for using a single newline character as the statement separator within SQL scripts. Investigation of the cause of this issue resulted in the discovery of another, similar issue: support for multiple newlines as a statement separator has been broken for years but has gone unnoticed until now.

The reason that both of these issues have gone unnoticed is a result of the fact that the test suite only executes SQL script integration tests against HSQL DB, and HSQL does not care if two statements occur on the same line; whereas, the H2 database will throw an exception if multiple statements are included on the same line when executing an update.

This commit addresses both of these issues and provides further enhancements to Spring's SQL script support as follows.

• ScriptUtils now properly checks if the supplied script contains the custom statement separator or default separator before falling back to the 'fallback' separator (i.e., newline).
• Introduced FALLBACK_STATEMENT_SEPARATOR constant in ScriptUtils.
• ScriptUtils.readScript() no longer omits empty lines from the input file since a statement separator string may in fact be composed of multiple newline characters.
• Introduced overloaded variants of splitSqlScript() and executeSqlScript() in ScriptUtils with smaller argument lists for common use cases.
• Extracted AbstractDatabasePopulatorTests from DatabasePopulatorTests and introduced concrete HsqlDatabasePopulatorTests and H2DatabasePopulatorTests subclasses for testing against HSQL and H2.
• Split ScriptUtilsTests into ScriptUtilsUnitTests and ScriptUtilsIntegrationTests for faster builds.

[spring-projects-issues]

Sergej Koščejev commented

I used to specify separator="SOME_STRING_THAT_DOES_NOT_EXIST_IN_THE_FILE" to make Spring execute my SQL script in its entirety without splitting it. Now ScriptUtils tries to be smarter than me and ignores the separator I've specified if it doesn't exist in the file. Can you bring the old behavior back or introduce some way to execute the whole script as a single statement? Thanks.

[spring-projects-issues]

Sam Brannen commented

Sergej Koščejev, could you please tell me exactly how you were executing your scripts previously (i.e., via ResourceDatabasePopulator and DatabasePopulatorUtils or via EmbeddedDatabaseBuilder or perhaps via the <jdbc:embedded-database ... /> or <jdbc:initialize-database ... />)?

Thanks,

Sam

[spring-projects-issues]

Sergej Koščejev commented

Like this:

<jdbc:initialize-database ignore-failures="DROPS">
    <jdbc:script location="classpath:scripts/create_schema.sql"/>
    <jdbc:script location="classpath:scripts/create_static_data.sql" separator="NO_SEPARATOR" />
</jdbc:initialize-database>

[spring-projects-issues]

Sam Brannen commented

Sergej Koščejev,

Thanks for the feedback. I've created #16310 in order to investigate this further.

Regards,

Sam

[spring-projects-issues]

Sam Brannen commented

Sergej Koščejev,

FYI: with the change introduced in #16310, you will now be able to configure your script as follows:

<jdbc:initialize-database ignore-failures="DROPS">
  <jdbc:script location="classpath:scripts/create_schema.sql"/>
  <jdbc:script location="classpath:scripts/create_static_data.sql"
      separator="^^^ END OF SCRIPT ^^^" />
</jdbc:initialize-database>

Note that ^^^ END OF SCRIPT ^^^ is the value of the new org.springframework.jdbc.datasource.init.ScriptUtils.EOF_STATEMENT_SEPARATOR constant.

Regards,

Sam

[spring-projects-issues]

Sergej Koščejev commented

Thank you for the quick fix!