-
Hi, I have a few MS-SQL server stored procedures that return result set. I am not having any Out Params in the signature. Call.stream() like in Query class Thanks |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 2 replies
-
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? |
Beta Was this translation helpful? Give feedback.
-
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 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. |
Beta Was this translation helpful? Give feedback.
-
So we will ship this as part of 3.43.0. See #2576 for details. This is the syntax:
See https://github.com/jdbi/jdbi/pull/2576/files#diff-055d1b639319e597ee5f82d94203df27bbd3aacb49c81570b0ba97168e3802fdR48-R62 for an actual test running against SQLServer. |
Beta Was this translation helpful? Give feedback.
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: