JDBC SQL Server Stored Procedure with ResultSet, return value, and output parameters

Question

I am in the process of converting an application from Jython to compiled Java. The application uses a host of SQL Server stored procedures to do CRUD operations. All of the procedures are defined with a return value that indicates status, and some output parameters used to provide feedback to the application. Most of the procedures also return a result set. I'm struggling with how to retrieve the return value and the result set and the output parameters.

I normally work with C# so the nuances of JDBC are new to me. I've been testing with one of the procedures that does an insert to the database and then does a select on the inserted object.

Here's a simplified example procedure just to use for the purpose of illustration. The actual procedures are more complex than this.

CREATE PROCEDURE [dbo].[sp_Thing_Add]
(
   @Name NVARCHAR(50),
   @Description NVARCHAR(100),
   @ResultMessage NVARCHAR(200) = N'' OUTPUT
)
AS
BEGIN
   SET NOCOUNT ON
   
   DECLARE @Result INT = -1
   DECLARE @ResultMessage = 'Procedure incomplete'
   
   BEGIN TRY
      INSERT INTO Things (Name, Description) VALUES (@Name, @Description)

      SELECT * FROM Things WHERE ThingID = SCOPE_IDENTITY()
   END TRY
   BEGIN CATCH
      SELECT @Result = CASE WHEN ERROR_NUMBER() <> 0 THEN ERROR_NUMBER() ELSE 1 END,
         @ResultMessage = ERROR_MESSAGE()
      GOTO EXIT_SUB
   END CATCH
SUCCESS:
   SET @Result = 0
   SET @ResultMessage = N'Procedure completed successfully'
   RETURN @Result
EXIT_SUB:
   IF @Result <> 0
   BEGIN
   -- Do some error handling stuff
   END
   RETURN @Result

      

I can successfully retrieve the ResultSet using the following code.

var conn = myConnectionProvider.getConnection();
String sql = "{? = call dbo.sp_Thing_Add(?, ?, ?)}"

call = conn.prepareCall(sql);
call.registerOutParameter(1, TYPES.Integer); // Return value
call.setString("Name", thing.getName());
call.setString("Description", thing.getDescription());
call.registerOutParameter("ResultMessage", TYPES.NVARCHAR);
ResultSet rs = call.executeQuery();

// Try to get the return value. This appears to close the ResultSet and prevents data retrieval.
//int returnValue = call.getInt(1);
// Normally there'd be a check here to make sure things executed properly, 
// and if necessary the output parameter(s) may also be leveraged

if (rs.next()) {
   thing.setId(rs.getLong("ThingID"));
   // Other stuff actually happens here too...
}

If I try retrieving the return value using the line that's commented out, I get an error stating that the ResultSet is closed.

com.microsoft.sqlserver.jdbc.SQLServerException: The result set is closed.

I've been through the documentation and have seen how to do return values, output parameters, and result sets. But how can I leverage all 3?

Answer 1

Given the order of processing in your stored procedure (insert, select, then populate result parameters), you need to process the result set before you retrieve the return value with CallableStatement.getXXX.

Answer 2

The output is in the ResultSet rs retrieved from executeQuery().

You may want to use the excute method as such:

    call.execute();
    String returnValue = call.getString("ResultMessage");

You also want to map correctly to the output type.

Answer 3

Your connection got closed once the execute query is executed. Basically mysql jdbc connection extends to AutoCloseable implicitly. Since your result is only entity from procedure,please get the value by index 0 and do a proper index out of bound exception handling.