Add support for T-SQL optional INTEGER return values from stored procedures #4106

Closed
lukaseder opened this Issue Mar 2, 2015 · 1 comment

Projects

None yet

1 participant

@lukaseder
Member

Assuming this procedure:

CREATE PROCEDURE [dbo].[foo] (
    @param1 INT,
    @param2 INT OUTPUT
) AS BEGIN
   SET @param2 = @param1
   IF @param1 = 5
       RETURN (2)
END
GO

Calling this:

CallableStatement call = connection.prepareCall("{ ? = call foo (?, ?) }");
call.registerOutParameter(1, Types.INTEGER);
call.registerOutParameter(3, Types.INTEGER);
call.setInt(2, 5); // This will return 2
System.out.println();
System.out.println(call.execute());
System.out.println(call.getObject(1));
System.out.println(call.wasNull());
System.out.println(call.getInt(3));

call.setInt(2, 4); // This will not return anything
System.out.println();
System.out.println(call.execute());
System.out.println(call.getObject(1));
System.out.println(call.wasNull());
System.out.println(call.getInt(3));

will yield

false
2
false
5

false
0
false
4

According to the documentation, all T-SQL procedures are allowed to return values of type INTEGER:

https://msdn.microsoft.com/en-US/library/ms174998.aspx


See also:

@lukaseder lukaseder added this to the Version 3.6.0 milestone Mar 2, 2015
@lukaseder
Member

This is an "incompatible change" for generated sources, as all Routine<Void> types will be generated as Routine<Integer> now, and routines with only one OUT parameter may have had convenience methods that returned only that OUT parameter:

String outValue = Routines.r(configuration, inValue, inOutValue);

Those convenience methods would now be generated differently, returning instead:

R result = Routines.r(configuration, inValue, inOutValue);
String outValue = result.getOutValue();
Integer returnValue = result.getReturnValue();

In order to allow users to keep current behaviour, they can use a new code generation flag (which is deprecated on introduction and will be removed again in jOOQ 4.0):

<ignoreProcedureReturnValues>true</ignoreProcedureReturnValues>
@lukaseder lukaseder added the R: Fixed label Mar 2, 2015
@lukaseder lukaseder closed this Mar 2, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment