Skip to Content

Call HANA stored procedure from JDBC with custom type output parameter

I created the following procedure on HANA (Express Edition 2 with Applications on local VM):

CREATE PROCEDURE MYDB.UNIVERSITY.TestProc(out out_students MYDB.UNIVERSITY.TT_STUDENTS)
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER READS SQL DATA AS
BEGIN
  out_students = SELECT NAME, FIRST_NAME, SEMESTERS FROM MYDB.UNIVERSITY.STUDENT;
END;

Now, I want to call it and get the result of parameter "out_students" using JDBC. As far as I found out, I can use class "CallableStatement" for calling procedures and its method "registerOutParameter" for registering an output parameter at a specific position. Here is the relevant code snippet:

CallableStatement stmt = connection.prepareCall("{CALL MYDB.UNIVERSITY.TestProc(?)}");
stmt.registerOutParameter(1, Types.STRUCT, "MYDB.UNIVERSITY.TT_STUDENTS");
stmt.execute();

However, I get the following error:

Query failed: SAP DBTech JDBC: Invalid parameter index: 1
com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Invalid parameter index: 1
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._newInstance(SQLExceptionSapDB.java:191)
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.newInstance(SQLExceptionSapDB.java:26)
at com.sap.db.jdbc.PreparedStatementSapDB._registerOutParameter(PreparedStatementSapDB.java:2357)
at com.sap.db.jdbc.CallableStatementSapDB.registerOutParameter(CallableStatementSapDB.java:708)
at HANAConnector.main(HANAConnector.java:32)

Changing the type of the output parameter to a primitive type works fine, so there must be some problem with the custom type.

Add comment
10|10000 characters needed characters exceeded

  • Hi Luca Toldo ,

    this utility function is something I just wrote quick quick to have readable output for my testing/debugging purposes. As I'm not particularly proud of that bit of code (it's not even half-assed) I won't put it up here.

    I'll answer your email anyhow...

    Cheers,

    Lars

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 05 at 12:10 AM

    Table typed out parameters are mapped to JDBC result sets and not JDBC out parameters).

    An example could look like this:

    CallableStatement stmt = connection.prepareCall("{CALL getusers(?, ?)}"); 
    boolean moreResults = stmt.execute();
    while (moreResults) {
        ResultSet rs = stmt.getResultSet();
        Utils.printResultSetData(rs); // this is just a utility function to print out result sets 
        rs.close();
        moreResults = stmt.getMoreResults();
    } 
    stmt.close();    
    
    Add comment
    10|10000 characters needed characters exceeded