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

Anonymous block in SAP HANA is not working #1723

Closed
sasivarnans opened this issue Jul 27, 2017 · 1 comment
Closed

Anonymous block in SAP HANA is not working #1723

sasivarnans opened this issue Jul 27, 2017 · 1 comment

Comments

@sasivarnans
Copy link

@sasivarnans sasivarnans commented Jul 27, 2017

What version of Flyway are you using?

I am using 4.2 version of flyway.

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

I am using the JAVA API to do the migration.

What database are you using (type & version)?

SAP HANA Version SP 11

What operating system are you using?
What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)
I am trying to execute migration with following procedure

DO
BEGIN
    DECLARE v_count INT;
    CREATE TABLE TAB1 (I INTEGER); 
    FOR v_count IN 1..10 DO
        INSERT INTO TAB1 VALUES (:v_count);
    END FOR;
END;
What did you expect to see?

The script works successfully in SAP HANA when executed directly

What did you see instead?

SQL State : HY000
Error Code : 257
Message : SAP DBTech JDBC: [257] (at 26): sql syntax error: line 3 col 17 (at pos 26)
Location : db/migration/hana/V1__create.table.sql
Line : 1
Statement : DO
BEGIN
DECLARE v_count INT

@sasivarnans
Copy link
Author

@sasivarnans sasivarnans commented Jul 27, 2017

The following code in the SapHanaSqlStatementBuilder decides what is each statement

boolean insideStatementAllowingNestedBeginEndBlocks = statementStartNormalized.startsWith("CREATE PROCEDURE") || statementStartNormalized.startsWith("CREATE FUNCTION") || statementStartNormalized.startsWith("CREATE TRIGGER");

This above code only allows reading procedure, functions and triggers to be read as single statement.

Anonymous block started with DO statement also needs to be considered here to read the whole block as single statement as per the following syntax
DO BEGIN ... END Statement

boolean insideStatementAllowingNestedBeginEndBlocks = statementStartNormalized.startsWith("CREATE PROCEDURE") || statementStartNormalized.startsWith("CREATE FUNCTION") || statementStartNormalized.startsWith("CREATE TRIGGER") || statementStartNormalized.startsWith("DO");

Loading

@axelfontaine axelfontaine added this to the Flyway 5.0.0 milestone Nov 25, 2017
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Nov 25, 2017
@flyway flyway deleted a comment from Varma-kumar May 30, 2018
@flyway flyway locked as resolved and limited conversation to collaborators May 30, 2018
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants