Skip to Content
0

Call HANA stored procedure from JDBC with custom type output parameter

Jan 03 at 01:25 PM

332

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
Jan 05 at 12:10 AM
0

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();    
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for the answer. It does the trick.

0