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

SAP HANA: CREATE PROCEDURE statements with embedded semicola don't work #1531

Closed
ace130-github opened this issue Feb 15, 2017 · 1 comment
Closed

Comments

@ace130-github
Copy link

@ace130-github ace130-github commented Feb 15, 2017

What version of Flyway are you using?

Current master (03136dc, date: Feb 15, 2017)

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

Command line

What database are you using (type & version)?

SAP HANA Express 2.0 SPS 0

What operating system are you using?

Windows 7

What did you do?

Try to have flyway import the following file:

-- just to have one statement before the actual test statement
CREATE VIEW all_misters AS SELECT * FROM test_user WHERE name LIKE 'Mr.%';

-- create statement with embedded ';'
-- it is expected that this statement is read as a single statement ending after 'END;'
-- and not broken into separate statements ending after each ';' withing the BEGIN...END-block.
-- this example statement is borrowed from the HANA reference for
-- CREATE PROCEDURE (http://help-legacy.sap.com/saphelp_hanaplatform/helpdata/en/20/d467407519101484f190f545d54b24/content.htm)
CREATE PROCEDURE orchestrationProc
 LANGUAGE SQLSCRIPT AS
 BEGIN
   DECLARE v_id BIGINT;
   DECLARE v_name VARCHAR(30);
   DECLARE  v_pmnt BIGINT;
   DECLARE v_msg VARCHAR(200);
   DECLARE CURSOR c_cursor1 (p_payment BIGINT) FOR
     SELECT id, name, payment FROM control_tab
       WHERE payment > :p_payment ORDER BY id ASC;
   CALL init_proc();
   OPEN c_cursor1(250000);
   FETCH c_cursor1 INTO v_id, v_name, v_pmnt; v_msg := :v_name || ' (id ' || :v_id || ') earns ' || :v_pmnt || ' $.';
   CALL ins_msg_proc(:v_msg);
   CLOSE c_cursor1;
 END;

-- just to have a trailing statement for testing
DROP VIEW all_misters;

which is syntactically correct in the SAP HANA SQL dialect (in fact, the main part is copied from the HANA SQL reference for CREATE PROCEDURE).

What did you expect to see?

The file should be split into three statements (CREATE VIEW..., CREATE PROCEDURE..., DROP VIEW) and the statements should be executed against the DB.

What did you see instead?

The first statement is successfully executed, but the second statement is truncated at the first line that ends with a semicolon. Therefore it has an illegal syntax and is rejected by the db.

@axelfontaine axelfontaine added this to the Flyway 4.1.2 milestone Feb 17, 2017
axelfontaine added a commit that referenced this issue Mar 3, 2017
#1531: Fix for CREATE PROCEDURE in SAP HANA dialect
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Mar 3, 2017
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Mar 3, 2017

Fixed by PR.

dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
flyway#1531: Fix for CREATE PROCEDURE in SAP HANA dialect
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.