Return a Value from Stored Procedure to Java

Question

What is the best way to return a value from a SQL Stored Procedure and pass that returned value to Java code.

Greg Merrill · Answer

You are correct that SqlService.executeStoredProcedure doesn't support those.

However, i belive you could use com.onenetwork.platform.data.sql.SqlService.executeQuery(String, SqlParams) to achieve the same, leveraging the JDBC syntax for executing a stored proc:

"{call MY_PROC($PARAM1$, $PARAM2$}"

I have not tried this but i suspect it will work.

Marco Gouveia · Answer

Here's an example.

Create a Stored Procedure that has an output parameter:

CREATE OR REPLACE PROCEDURE getStringLength(inputText IN VARCHAR2, outputNumber OUT NUMBER)
AS BEGIN
outputNumber := LENGTH(inputText);
END;
/

Then use the following java code to run it:

String res;
String inputText="AAAAAA";
try {
  CallableStatement cs = null;
  Connection conn = DataSourceFactory.getDataSource().getConnection();
  cs = conn.prepareCall("{ call getStringLength(?,?) }");
  cs.setString(1, inputText);
  cs.registerOutParameter(2, java.sql.Types.INTEGER);
  cs.execute();
  res = "Text length = " + cs.getInt(2);
}
catch(Exception e){
  res = "Error: " + e.getMessage();
}

Muhammad Majid · Answer

I'm also facing this problem. But i cannot find solution yet.

[Solved] Return a Value from Stored Procedure to Java