Skip to Content
2
May 13, 2018 at 02:05 PM

Creating stored procedure via JDBC fails with syntax error

184 Views

For our application on HANA Express 2.0 SP03 we require some SQL-behaviour mimicking "DROP ... IF EXISTS".

To do so, we created some stored procedures and inserted them using the WebIDE. Works fine so far.

However, when I try to insert the same stored procedure via JDBC, it fails with a syntax error.

One of them is:

create procedure dropindexifexists(IN indexname VARCHAR(40)) LANGUAGE SQLSCRIPT AS
BEGIN
    DECLARE myrowid INT;
    select count(*) into myrowid from "PUBLIC"."INDEXES" where schema_name = (SELECT CURRENT_SCHEMA FROM DUMMY) and index_name=:indexname;
    IF (:myrowid > 0 ) then
        exec 'DROP INDEX '||:indexname;
    END IF;
END;

Using JDBC I receive the following error for this statement:

create procedure dropindexifexists(IN indexname VARCHAR(40)) LANGUAGE SQLSCRIPT AS
BEGIN
    DECLARE myrowid INT

SAP DBTech JDBC: [257] (at 108): sql syntax error: line 3 col 18 (at pos 108)

Can anybody explain this behaviour and a possible solution to me?


Thanks,

Philipp