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

Errors while creating a function in SAP HANA #2724

Closed
stesix opened this issue Mar 16, 2020 · 2 comments
Closed

Errors while creating a function in SAP HANA #2724

stesix opened this issue Mar 16, 2020 · 2 comments

Comments

@stesix
Copy link

@stesix stesix commented Mar 16, 2020

Which version and edition of Flyway are you using?

flyway ce 6.3.1

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

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

Command-line (docker)

Which database are you using (type & version)?

SAP Hana 2

What did you do?

I'm trying to create a function structured this way:

create or replace function my_test_func(in test_par integer)
returns o_code integer
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
AS
  length integer;
BEGIN
    o_code := test_par;
END;
What did you expect to see?

I expect to have a function created in the DB

What did you see instead?
create or replace function my_test_func(in test_par integer)
returns o_code integer
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
AS
  length integer
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:274)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:220)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:127)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:88)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.access$000(SqlMigrationExecutor.java:33)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1.call(SqlMigrationExecutor.java:77)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor$1.call(SqlMigrationExecutor.java:74)
	at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:28)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:74)
	at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:366)
	... 21 more
Caused by: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257] (at 229): sql syntax error: line 6 col 11 (at pos 229)
	at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._newInstance(SQLExceptionSapDB.java:191)
	at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.newInstance(SQLExceptionSapDB.java:42)
	at com.sap.db.jdbc.packet.HReplyPacket._buildExceptionChain(HReplyPacket.java:973)
	at com.sap.db.jdbc.packet.HReplyPacket.getSQLExceptionChain(HReplyPacket.java:158)
	at com.sap.db.jdbc.packet.HPartInfo.getSQLExceptionChain(HPartInfo.java:40)
	at com.sap.db.jdbc.ConnectionSapDB._receive(ConnectionSapDB.java:3115)
	at com.sap.db.jdbc.ConnectionSapDB.exchange(ConnectionSapDB.java:1518)
	at com.sap.db.jdbc.StatementSapDB._executeDirect(StatementSapDB.java:1419)
	at com.sap.db.jdbc.StatementSapDB._execute(StatementSapDB.java:1398)
	at com.sap.db.jdbc.StatementSapDB._execute(StatementSapDB.java:1383)
	at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:444)
	at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:244)
	at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:111)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:208)

I did perform another test using the following code:

create or replace function my_test_func(in test_par integer)
returns o_code integer
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
AS
BEGIN
    o_code := test_par;
END;

It works without problems. I think the problem is related to the fact that create function (as well as create procedure do not increase the block depth.

I'll see if I can create a PR solving this issue.

@MikielAgutu MikielAgutu added this to the On the Radar milestone Mar 17, 2020
@Lyeeedar Lyeeedar assigned Lyeeedar and unassigned Lyeeedar Mar 17, 2020
@stesix
Copy link
Author

@stesix stesix commented Mar 18, 2020

Depending on the solution implemented, this example might also create problems (at least it's creating problems to me):

create or replace procedure my_test()
language sqlscript
SQL SECURITY INVOKER
as
    cursor test for
        select table_name,
            case when table_name = 'the_table' then 1 else 0 end
        from tables;

begin
    -- do stuff
end;

stesix added a commit to stesix/flyway that referenced this issue Mar 18, 2020
@Lyeeedar Lyeeedar self-assigned this Mar 20, 2020
@Lyeeedar Lyeeedar removed this from the On the Radar milestone Mar 20, 2020
@Lyeeedar Lyeeedar added this to the Flyway 6.3.2 milestone Mar 20, 2020
@juliahayward
Copy link
Member

@juliahayward juliahayward commented Mar 23, 2020

Will be fixed in 6.3.2 release.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants