Support comments within SQL statements in JdbcTestUtils [SPR-9982]

[spring-projects-issues]

Joram Agten opened SPR-9982 and commented

Status Quo

Executing an SQL script via JdbcTestUtils.executeSqlScript() with the following content fails.

INSERT INTO persons( person_id
                   , name)
VALUES( 1      -- person_id
      , 'Name' --name
);

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [INSERT INTO persons( person_id                   , name)VALUES( 1      - person_id      , 'Name' --name)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00917: missing comma

Proposed Solutions

1. keep the carriage returns after each line when building statements (line 388 389)
2. strip all characters (per line) after the DEFAULT_COMMENT_PREFIX (including the prefix)

I think solution 1 is preferred, since 2 might become db dependent.

Deliverables

1. Ensure that multi-line statements which contain comments can be executed.

---

Affects: 3.1.3

Issue Links:

• JdbcTestUtils should not interpret comments as a separate statement [SPR-9593] #14227 JdbcTestUtils should not interpret comments as a separate statement
• JdbcTestUtils readScript fails to see commented lines with tabs [SPR-10330] #14964 JdbcTestUtils readScript fails to see commented lines with tabs
• Support comments within SQL statements in ResourceDatabasePopulator [SPR-10075] #14708 Support comments within SQL statements in ResourceDatabasePopulator
• Support multi-line SQL comments in ResourceDatabasePopulator and JdbcTestUtils [SPR-9531] #14165 Support multi-line SQL comments in ResourceDatabasePopulator and JdbcTestUtils
• Improve comment handling in ResourceDatabasePopulator [SPR-9261] #13899 Improve comment handling in ResourceDatabasePopulator ("supersedes")

Referenced from: commits 8a422f4, d0f687f

[spring-projects-issues]

Chris Beams commented

Sam Brannen, this looks related to #14227. Can you take a look?

[spring-projects-issues]

Sam Brannen commented

Resolved as described in the comments for GitHub commit d0f687f028:

Support comments in statements in JdbcTestUtils

Prior to this commit, executing an SQL script with JdbcTestUtils would
fail if a statement in the script contained a line comment within the
statement.

This commit ensures that standard SQL comments (i.e., any text beginning
with two hyphens and extending to the end of the line) are properly
omitted from the statement before executing it.

In addition, multiple adjacent whitespace characters within a statement
but outside a literal are now collapsed into a single space.

[spring-projects-issues]

Sam Brannen commented

If you're watching or reading this issue, you may also be interested in #14708.