3

I've created this stored procedure in HANA database which is taking two parameters, one is a table type and other is varchar.

CREATE PROCEDURE UPDATE_GSTR(IN p_Input_Values "GSTR11".p_Input_Values , IN p_TRANS_ID VARCHAR(100))

Now I want to call this procedure in Java, I've written something like this.

Connection dbConnection = null;
CallableStatement callableStatement = null;

String storedProcedure = "{call UPDATE_GSTR(?,?)}";

dbConnection = jdbc.getDataSource().getConnection();
callableStatement = dbConnection.prepareCall(storedProcedure);

callableStatement.setString(1, "");
callableStatement.setString(2, "");

// execute store procedure
callableStatement.executeUpdate();

Can someone tell me how to pass the object as a table entity in the argument while calling this stored procedure?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nomaan
  • 33
  • 4

1 Answers1

2

The table typed input parameter cannot be created outside of SAP HANA. For client applications, one way to still use table typed parameters is to use temporary tables that have the same structure as the parameter table.

Your JAVA application would then first fill the temporary table and call the procedure in a second step

String storedProcedure = "{call UPDATE_GSTR("<temp_table_name>",?)}";
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Thanks for the comment. The intention to implement this concept is to transfer the load form java to database and increase the performance but it seems there is no way of doing it. – nomaan Aug 23 '17 at 05:40
  • Not sure I get what you mean. Either way you transfer the content of the table type parameter, you have to build the table somehow. Building a server-side temp table that you use to pass the data into the procedure is less convenient, but not that much more work to do. – Lars Br. Aug 23 '17 at 06:31