Procedure parsing error in SAP HANA

[stesix]

Which version and edition of Flyway are you using?

6.3.1 ce

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

command-line

Which database are you using (type & version)?

SAP HANA 2.0

What did you do?

sql file with a stored procedure inside. If the stored procedure happens to have a select with a CASE keyword and the next instruction is an IF the depth is not increased.

e.g.

create or replace procedure anothertest()
LANGUAGE SQLSCRIPT
AS
v_test varchar(33);
cursor mytable for
	select 'monkey test' mycolumn
	from dummy;
begin
    for cur_row as mytable() do
    	v_test := 'is mytest a "monkey test"? ' || 
    		case 
    			when cur_row.mycolumn = 'monkey test' then 'yes'
    			else 'no'
    		end;

		if cur_row.mycolumn is not null then
			-- whoops this IF is not considered correctly in the code
			v_test := v_test || '!!!';
		end if;
		-- the parser stops here processing, creating an invalid procedure.
	end for;
end;

The issue is in flyway-core/src/main/java/org/flywaydb/core/internal/database/saphana/SAPHANAParser.java.

BTW this proposed example can be used only after fixing #2724 .

[stesix]

here's another example that works without fixing the #2724

create or replace procedure another_example(in p_in integer, out o_result integer)
LANGUAGE SQLSCRIPT
AS
begin
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        o_result := 0;
    END;

    if p_in = 5 then
        o_result := 2
    end if;

    -- other code that might result in a SQLEXCEPTION
end;

As soon as the parser reaches the IF right after of the HANDLER declaration, the depth won't be increased correctly since the previous keyword was END.

[juliahayward]

Will be fixed by the 6.3.2 release.