Get Out Parameters on Stored Procedures (MSSQL Server)

[Marco Vargas]

Hi,

 If I have a stored procedure as the following (MSSQL Server):

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

How could I execute the stored procedure, get the data and check the content of the output parameters using sql2o???

Thanks

marco.

[Lars Aaberg]

Hi,

I got the following example to work.

On the Sql Server i created this stored procedure:

CREATE PROCEDURE [dbo].[output_param_test]
    @inputParam varchar(100),
    @outputParam varchar(100) OUTPUT

AS
BEGIN
    SET NOCOUNT ON;

    set @outputParam = @inputParam;
END

To get the output parameter from java with sql2o:
        String sql =
                "declare @outputParam as varchar(100) " +
                "exec output_param_test @inputParam = :param, @outputParam = @outputParam OUTPUT " +
                "select @outputParam as myval";

        String outputVal = sql2o.createQuery(sql).addParameter("param", "foo").executeScalar(String.class);
        assertThat(outputVal, is(equalTo("foo")));

If you need to fetch more than one output parameter, you can use executeAndFetchTable() method instead of the executeScalar() method.

Regards
Lars Aaberg

[Marco Vargas]

But in this case we just can get one cursor from the database side, I mean, if the stored procedure performs a select statement we won't be able to get the output parameters, since the executeAndFetch will just bring the select statement specified inside the stored procedure.

Or is there any way to get more than one cursor from the database?

By the way, thanks Lars for your quick responses.

[Lars Aaberg]

Hi Marco,

The preferred way of getting output parameters with JDBC is using the CallableStatement, which has a registerOutParameter() method. Unfortunately there is no wrapper around this functionality in sql2o. So I don't think it's possible to get both the resultset and the output parameters with Sql2o at this time.

It would be great if you made an issue on github about this, then I will see if I can fix this in the near future.

Regards

Lars Aaberg

--
You received this message because you are subscribed to the Google Groups "sql2o" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql2o+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Marco Vargas]

Thanks Lars for your quick replies!

I just added the issue in Github.

Please keep me posted.

By the way!

Merry Christmas!

[VAMSI KRISHNA MOGALIPUVVU]

Hi Lars Aaberg,

We would like to implement database interaction with sql2o and java restful api  for an angular project . we are using some stored procedures which returns both resultset and output parameters. with sql2o.  Is this possibe ?

Did you fix this in latest version of sql2o ?

Thanks in advance

To unsubscribe from this group and stop receiving emails from it, send an email to sq...@googlegroups.com.