01-03-2018 1:25 PM - edited 02-04-2024 2:30 AM
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.
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();
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.