Can you add support for returning Stream on Call

[metaforte]

Hi,

I have a few MS-SQL server stored procedures that return result set. I am not having any Out Params in the signature.
Is it possible to let

Call.stream() like in Query class

Thanks

[hgschmie]

the best approach is probably to use OutParameters and return a result cursor. Within the outparameter class, you can then stream the results.

What database are you using?

[metaforte]

I am using SQL server. SQL server JDBC driver does not support cursor out parameter. That's why I requested this

microsoft/mssql-jdbc#98

[hgschmie]

Wow, never thought that I would be dealing with MS SQLServer on non-Windows. :-)

Thank you for reporting this. I think this is the first time someone asked a question about MS SQLServer and Jdbi.

I got this working:

    @Test
    public void testMsSqlServerCall() {
        Jdbi db = extension.getJdbi();
        Handle h = extension.getSharedHandle();
        h.execute("CREATE PROCEDURE simpleCursor\n"
            + "AS\n"
            + "BEGIN\n"
            + "    SELECT 'hello' UNION ALL SELECT 'world'\n"
            + "END;");

        try (SqlServerCall call = new SqlServerCall(h, "{ call simpleCursor()}")) {
            List<String> results = call.mapTo(String.class).list();

            assertThat(results).isNotEmpty().containsExactly("hello", "world");
        }

    }

    public static class SqlServerCall extends Call implements ResultBearing {

        public SqlServerCall(Handle handle, CharSequence sql) {
            super(handle, sql);
        }

        public SqlServerCall(Handle handle, String sql) {
            super(handle, sql);
        }

        public <R> R execute(ResultProducer<R> producer) {
            try {
                return producer.produce(super::internalExecute, getContext());
            } catch (SQLException e) {
                cleanUpForException(e);
                throw new UnableToProduceResultException(e, getContext());
            }
        }

        @Override
        public <R> R scanResultSet(ResultSetScanner<R> resultSetScanner) {
            return execute(ResultProducers.returningResults()).scanResultSet(resultSetScanner);
        }
    }

So can we implement this (and we will) and you could almost work around it today, except that there is SqlStatement#internalExecute() which is package private and it would need to be protected to be used by outside code.

I will add this (and I will also add testcontainer support for MS SQL while doing so, because why not?) and we will ship it as part of 3.43.0. I still consider this somewhat unusual but it is MS and the tone on microsoft/mssql-jdbc#98 was pretty clear and if that helps you, we can surely do that.

I am not 100% certain whether to implement that as an addition to Call itself or as a different query type. I need to try out whether the call with result set works with other databases as well (e.g. Postgres or Oracle). If it does, it is a no-brainer, if it does not, it may end up as an additional module like the jdbi3-oracle module that adds MS SQL support to Jdbi.

[metaforte]

thank you.

[hgschmie]

So we will ship this as part of 3.43.0. See #2576 for details.

This is the syntax:

try (Call call = new SqlServerCall(h, "{ call simpleCursor()}")) {
    OutParameters out = call.invoke();
    List<String> results = out.getResultSet().mapTo(String.class).list();
    assertThat(results).isNotEmpty().containsExactly("hello", "world");
}

See https://github.com/jdbi/jdbi/pull/2576/files#diff-055d1b639319e597ee5f82d94203df27bbd3aacb49c81570b0ba97168e3802fdR48-R62 for an actual test running against SQLServer.