Notifications
Clear all

[Solved] Return a Value from Stored Procedure to Java

   RSS

0
Topic starter

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

After research, this appears like it may not be supported as of version NEO 3.3.

 

SqlResult seems to be the proper class to store and access the result from a SQL call. executeStoredProcedure seems to be the proper method to execute a stored procedure.

 

However, the executeStoredProcedure method has a void return method.

SqlService.executeStoredProcedure does not support those, and you are accurate.

However, I believe you could achieve the same result by calling com.onenetwork.platform.data.sql.SqlService.executeQuery(String, SqlParams) with the JDBC syntax for executing a stored procedure:

3 Answers
0

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.

 

Unlike SQL Server, Stored Procedures in Oracle cannot return a recordset. The only way to get a response from a Stored Procedure is through an output parameter. I'm currently running some tests to try to capture the value of the output parameter after the call.

0

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

 

0

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

Hi @muhammad-majid. @mgouveia provides an example of how to do this in his reply above. Were you not able to get it to work after trying that example? Thanks.

© 2020 One Network Enterprises. All rights reserved. Privacy Policy