Skip to Content

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

Add comment
10|10000 characters needed characters exceeded

  • 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.

  • Get RSS Feed

0 Answers