Add support to consume return values from stored procedures

[mp911de]

It would make sense to consume out parameters returned from a stored procedure. Since the R2DBC spec doesn't define how to consume out params, we could map these onto a Row. R2DBC 0.9 allows defining out/in-out parameters so we could leverage Statement.bind(…) to register out parameter declarations. The invocation syntax would follow SQL Server defaults without having the driver to rewrite a stored procedure call.

Schema setup:

CREATE PROCEDURE test_proc
    @TheName nvarchar(50),
    @Greeting nvarchar(255) OUTPUT
AS

    SET NOCOUNT ON;  
    SET @Greeting = CONCAT('Hello ', @TheName)

SQL statement:

EXEC test_proc @P0, @Greeting OUTPUT

Java code:

MssqlConnection connection = …;

connection.createStatement("EXEC test_proc @P0, @Greeting OUTPUT")
    .bind("@P0", "Walter")
    .bind("@Greeting", Parameters.out(R2dbcType.VARCHAR))
    .execute()
    .flatMap(it -> it.map((row, metadata) -> {
        return row.get(0); // row.get("@Greeting");
    }))
    .as(StepVerifier::create)
    .expectNext("Hello Walter")
    .verifyComplete();

See: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure?view=sql-server-ver15

[mp911de]

Proof of concept at: https://github.com/r2dbc/r2dbc-mssql/tree/result-segment-mapping (requires https://github.com/r2dbc/r2dbc-spi/pull/215).

[lukaseder]

Would this be how an IN/OUT parameter is bound?

.bind("@Greeting", Parameters.out(R2dbcType.VARCHAR, "in value"))

[lukaseder]

Also, is there a specified order in which the OUT parameters and other result sets are returned? E.g. what should happen if the procedure was like this?

CREATE PROCEDURE test_proc
    @TheName nvarchar(50),
    @Greeting nvarchar(255) OUTPUT
AS
    SET NOCOUNT ON;  
    SET @Greeting = CONCAT('Hello ', @TheName);
    SELECT 1;

[mp911de]

Would this be how an IN/OUT parameter is bound? Parameters.out(R2dbcType.VARCHAR, "in value")

Yes. The server protocol requires the registration of all parameters consisting of a type, name, and direction. IN/IN/OUT parameters can also hold a value.

Re Parameters.out, this comes from the SPI and we could add there also a inOut factory variant. I think it would serve primarily the API and not really any functionality.

Also, is there a specified order in which the OUT parameters and other result sets are returned?

Yes, there there are ordinals involved. But there's also a bit of additional complexity as the general mechanism for parametrized query execution is RPC-based using built-in stored procedures. SQL Server responds with return values in the order of their declaration and the first returned user-space value is @Greeting so either row.get(0) or row.get("@Greeting") works for retrieval. There's also RowMetadata available reflecting names, types, and index positions.

E.g. what should happen if the procedure was like this?

SQL Server responds with a row first and sends the out params at the end. It seems there's no way to know for the consumer in which order any assignments happen. From a code interpretation perspective, it even makes sense, because out params become available after the procedure has terminated, there's no way to push out params from within the procedure.

Protocol debug log

``` 2021-04-20 | 14:25:19.026 | main | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Request: RPCRequest [procName='null', procId=10, optionFlags=io.r2dbc.mssql.message.token.RpcRequest$OptionFlags@66e8997c, statusFlags=0, parameterDescriptors=[RpcString [name='null', value=EXEC test_proc @P0, @Greeting OUTPUT], RpcString [name='null', value=@P0 nvarchar(4000),@Greeting nvarchar(4000) OUTPUT], EncodedRpcParameter [name='P0', value=io.r2dbc.mssql.codec.CharacterEncoder$NvarcharEncoded@6f0cb5a1], EncodedRpcParameter [name='Greeting', value=io.r2dbc.mssql.codec.CharacterEncoder$NvarcharEncoded@1b29d52b]]] 2021-04-20 | 14:25:19.035 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Response: ColumnMetadataToken [columns=[Column [name='", type=MutableTypeInformation [maxLength=4, lengthStrategy=FIXEDLENTYPE, precision=10, displaySize=11, scale=0, flags=32, serverType=int, userType=0, udtTypeName="null", collation=null, charset=null], table=null]]] 2021-04-20 | 14:25:19.036 | reactor-tcp-nio-2 | DEBUG | i.r.m.MssqlSegmentResult | [cid: 0x1] Creating new result 2021-04-20 | 14:25:19.038 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Response: io.r2dbc.mssql.message.token.RowToken@4f927e50 2021-04-20 | 14:25:19.039 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Response: DoneInProcToken [done=false, hasCount=false, rowCount=1, hasMore=true, currentCommand=0] 2021-04-20 | 14:25:19.039 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Response: DoneInProcToken [done=false, hasCount=false, rowCount=0, hasMore=true, currentCommand=0] 2021-04-20 | 14:25:19.039 | reactor-tcp-nio-2 | DEBUG | i.r.m.MssqlSegmentResult | [cid: 0x1] Creating new result 2021-04-20 | 14:25:19.040 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Response: ReturnStatus [status=0] 2021-04-20 | 14:25:19.092 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Response: ReturnValue [ordinal=3, parameterName='@Greeting', value=PooledSlicedByteBuf(ridx: 0, widx: 26, cap: 26/26, unwrapped: PooledUnsafeDirectByteBuf(ridx: 112, widx: 125, cap: 256)), type=MutableTypeInformation [maxLength=8000, lengthStrategy=USHORTLENTYPE, precision=4000, displaySize=4000, scale=0, flags=0, serverType=nvarchar, userType=0, udtTypeName="null", collation=Collation [encoding=CP1252], charset=UTF-16LE]] 2021-04-20 | 14:25:19.093 | reactor-tcp-nio-2 | DEBUG | i.r.m.MssqlSegmentResult | [cid: 0x1] Creating new result 2021-04-20 | 14:25:19.093 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Response: DoneProcToken [done=true, hasCount=false, rowCount=0, hasMore=false, currentCommand=0] 2021-04-20 | 14:25:19.098 | reactor-tcp-nio-2 | DEBUG | i.r.m.c.ReactorNettyClient | [cid: 0x1] Conversation complete ```

FWIW, the actual call looks like:

sp_cursoropen cursor OUTPUT, 'EXEC test_proc @P0, @Greeting OUTPUT', scrollopt, ccopt,rowcount OUTPUT, '@P0 VARCHAR(4000),@Greeting VARCHAR(4000) OUTPUT', @P0, @Greeting

[lukaseder]

Re Parameters.out, this comes from the SPI and we could add there also a inOut factory variant. I think it would serve primarily the API and not really any functionality.

I was thinking the same thing. Also for clarity reasons, because a lot of RDBMS call the modifier INOUT or IN OUT or something similar. SQL Server is a bit special with their OUTPUT really meaning IN OUT and no OUT only parameter support.

SQL Server responds with a row first and sends the out params at the end. It seems there's no way to know for the consumer in which order any assignments happen. From a code interpretation perspective, it even makes sense, because out params become available after the procedure has terminated, there's no way to push out params from within the procedure.

Makes sense. I vaguely remember having to handle this differently per dialect even in JDBC for some dialects, where the order of fetching OUT params and other results is significant, probably even SQL Server again. Some JDBC drivers buffer the things to make them available in any order, but this isn't applicable in R2DBC. Perhaps an API note could be interesting?

Also, while I see that unifying OUT parameters and other types of Row simplifies the API and makes it more elegant as in my PostgreSQL example here: https://github.com/r2dbc/r2dbc-spi/issues/27#issuecomment-811955839, this example shows that it would probably be useful to be able to formally expose whether a Row is a set of OUT parameters or a result set. Perhaps two different Segment subtypes possibly sharing some common API could help?

[mp911de]

Re Parameters.out, this comes from the SPI and we could add there also a inOut factory variant. I think it would serve primarily the API and not really any functionality.

I was thinking the same thing. Also for clarity reasons, because a lot of RDBMS call the modifier INOUT or IN OUT or something similar. SQL Server is a bit special with their OUTPUT really meaning IN OUT and no OUT only parameter support.

Can you file either a SPI ticket or submit a PR if you like? The changes are limited to the Parameters type and basically a copy of the out methods with a separate type implementing In and Out interfaces.

Also, while I see that unifying OUT parameters and other types of Row simplifies the API and makes it more elegant as in my PostgreSQL example here: r2dbc/r2dbc-spi#27 (comment), this example shows that it would probably be useful to be able to formally expose whether a Row is a set of OUT parameters or a result set. Perhaps two different Segment subtypes possibly sharing some common API could help?

The idea to map out values to something Row-like goes into a proper direction. Thanks for this suggestion. Feel free to leave a comment on https://github.com/r2dbc/r2dbc-spi/pull/215. Coming up with a proper name for the interfaces might be a more difficult challenge than actually implementing it. Row extends <result-thing> for rows and <result-thing> for anything (including out params) that can be described with metadata and retrieved by name/index sounds seems a neat arrangement. On the Segment side, we can come up with a more fine-grained set of interfaces to represent row data and non-row data.

[lukaseder]

Can you file either a SPI ticket or submit a PR if you like? The changes are limited to the Parameters type and basically a copy of the out methods with a separate type implementing In and Out interfaces.

OK: https://github.com/r2dbc/r2dbc-spi/issues/216. I'll provide a PR suggestion also.

Feel free to leave a comment on r2dbc/r2dbc-spi#215.

I will