cancel
Showing results for 
Search instead for 
Did you mean: 

Creating stored procedure via JDBC fails with syntax error

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

lbreddemann
Active Contributor

Can you show your code that you use to create the procedure via JDBC?

SAP HANA Studio, for example, uses JDBC and can execute the create procedure statement just fine.

Also: in your query, you don't need to write

schema_name = (SELECT CURRENT_SCHEMA FROM DUMMY) 

it's sufficient to use

schema_name = CURRENT_SCHEMA

instead.

Accepted Solutions (0)

Answers (0)