JOOQ: How to create MockResult for stored procedure with out parameters?

When using the JOOQ MockDataProvider what is the correct way to build a MockResult for stored procedures?

The examples I’ve found show how to return results for tables and views by creating a result and record from generated table definitions and then returning them. For stored procedures I’m not sure how to build the Result.

How do I create a record that will map to the return parameter and out parameters of a stored procedure?

Is there some way to convert the out parameters on a routine to record fields?

Here is an example stored procedure that I would like to create results for:

DECLARE @return_value int,
        @StoredSystemId int

EXEC    @return_value = [dbo].[myProc]
        @StoredSystemId = @StoredSystemId OUTPUT,
        @UserName = 'user',
        @EmailAddress = '[e-mail]'

SELECT  @StoredSystemId as N'@StoredSystemId'

SELECT  'Return Value' = @return_value

Answer

From the MockDataProvider.execute Javadoc:

OUT parameters from stored procedures are generated from the first MockResult‘s first Record. If OUT parameters are requested, implementors must ensure the presence of such a Record.

An example would be:

MockConnection con = new MockConnection(ctx -> {
    Field<Integer> id = DSL.field("StoredSystemId", SQLDataType.INTEGER);
    return new MockResult[] { new MockResult(DSL.using(DEFAULT).newRecord(id).values(42)) };
});

try (CallableStatement cstmt = con.prepareCall("{ call xyz(?) }")) {
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.execute();

    System.out.println(cstmt.getInt(1));
}

It would print:

42

Notice that both the field name and actual SQL string are irrelevant to jOOQ’s MockDataProvider SPI. In your case, you will want additional results after the out parameters, which you can just append to the MockResult[] array. The important thing is that the OUT record is returned first.