Call HANA stored procedure from JDBC with custom type output parameter

[patricksteffens]

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.

[lbreddemann]

Hi lucatoldo ,

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

[lbreddemann · Accepted solution]

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();    

[patricksteffens · Accepted solution]

Thanks for the answer. It does the trick.

[LucaToldo · Accepted solution]

Hi can you please share

Utils.printResultSetData(rs)