Getting Output parameters from a stored procedure that returns a query

[markovar]

I did this test on SQL Server but I asumme it will happend on Oracle and other relational databases.

If we have a stored procedure that have output parameters and it returns a set of data, there is no way to get the content of the parameters and the Cursor returned by the Stored procedure.

Example:

The following stored procedure have two output parameters and inside has a Select Statement:

CREATE PROCEDURE [dbo].[play_GetData]
@returnedStatus INT OUTPUT,
@returnedMessage VARCHAR(2000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
--* Assume the query is quite more complicated.
SELECT Column1, Column2, Column3
FROM TableName
--*
SET @returnedStatus = 0
SET @returnedMessage = 'SUCCESS'
END TRY
--
BEGIN CATCH
--
SET @returnedStatus = ERROR_NUMBER()
SET @returnedMessage = ERROR_MESSAGE()
RAISERROR(@returnedMessage, 16, 1)
--
END CATCH
--
END

There is no way to get both returned data and the updated content in the parameters.

Thanks!

[dimzon]

can anyone write plain old JDBC code to show how it works?

[aaberg]

Example stolen from here

public static void executeStoredProcedure(Connection con) {
    try {
        CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
        cstmt.setInt(1, 5);
        cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
        cstmt.execute();
        System.out.println("MANAGER ID: " + cstmt.getInt(2));
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

@dimzon I have been working on a solution for this issue, and is almost done. I create a ProcedureCall class that represents a CallableStatement. This will have some of the same methods as the Query class (addParameter, executeAndFetch etc) and some new methods (addOutParameter, execute, getOutParameter etc)

[dimzon]

can you reproduce this on H2 as test-case ?

2014-04-10 1:48 GMT+04:00 Lars Aaberg notifications@github.com:

Example stolen from herehttp://technet.microsoft.com/en-us/library/ms378108.aspx

public static void executeStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
}
catch (Exception e) {
e.printStackTrace();
}
}

Reply to this email directly or view it on GitHubhttps://github.com//issues/31#issuecomment-40020787
.

[dimzon]

@aaberg

DECLARE @returnedStatus INT
DECLARE @returnedMessage VARCHAR(2000)
exec  [dbo].[play_GetData]  @returnedStatus, @returnedMessage
SELECT @returnedStatus, @returnedMessage

this way all you need is just nextResultSet() support

[aaberg]

After some evaluation of the possible code-fix, I have decided to label this issue with "wontfix" label. The added complexity in the Sql2o core is not worth the slightly improved api.

When Sql2o version 2 is released, underlaying JDBC objects will be easily available from Sql2o objects, and therefor it will be easier to mix sql2o and plain JDBC.