Getting Stored Procedure results from handle.createCall()

[0x1F528]

Hi,

I am trying to use JDBI to retrieve a result set from a Stored Procedure but when I try accessing the data I get a null pointer exception. As far as I can tell from debugging and inspection the query result is indeed (unexpectedly) empty.

I am able to access the DB with query = handle.createQuery("SELECT * FROM Noodle") and retrieve a correct result set with query.mapToMap().list()

However, when I use query = handle.createCall("{call Noodle_Get (0, 1) }").invoke() the resulting query object doesn't contain a result set (or at least I haven't figured out how to access it - I get a null pointer exception somewhere down deep).

When I do EXEC Noodle_Get 0, 1 in Sql Server Manager I do get the expected result set.

I have also implemented the Stored Procedure access with straight JDBC and am able to pull the data. I am just not able to do it with JDBI.

Here is the relevant code:
` Map<String, Object> results2 = jdbi.withHandle(handle -> {
OutParameters query = handle.createCall("{call Noodle_Get (0, 1) }").invoke();

        ResultBearing resultBearing = query.getRowSet(0);

        if (resultBearing != null) {
            ResultIterable <Map<String,Object>> resultIterable = resultBearing.mapToMap();

            Optional<Map<String,Object>> resMap = resultIterable.findFirst();

            if (!resMap.isPresent()) {
                return resMap.get();
            } else {
                System.out.println("No results found.");
                return null;
            }
        } else {
            System.out.println("ResultBearing is null.");
            return null;
        }
    });`

I am getting the NullPointerException when executing the resultIterable.findFirst()

Any suggestions what I should be looking for to resolve this?
I am using JDBI3-core version 3.41.3 and Java 17

Thank you!

[stevenschlansker]

Hi @0x1F528, can you please share the declaration of your stored procedure? Ideally we could reproduce the issue ourselves, that will make fixing it easier.

[0x1F528]

Steven, thank you for taking the time to take a look.

Here is a simplified implementation of my stored procedure:

CREATE PROCEDURE dbo.Noodle_Get
@ownerID int,
@rootnode int,
@filter nvarchar(max) = NULL,
@start int = 1,
@Depth int = 0
AS
SELECT id, strainId, pos, header, content, createdOn, modifiedOn FROM noodle
WHERE pos >= @start
ORDER BY strainId, pos
GO

I am using the graph DB capabilities of sql server - though I can't imagine how that could affect the calling java code. In any case a record set is returned.

The noodle table is as follows:

CREATE TABLE dbo.Noodle
(
id int IDENTITY(1,1),
ownerId int NOT NULL FOREIGN KEY REFERENCES dbo.Owner(id),
strainId int NOT NULL FOREIGN KEY REFERENCES dbo.Strain(id),
pos int NOT NULL,
header nvarchar(256) NULL,
content nvarchar(MAX) NULL,
createdOn datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
modifiedOn datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,

CONSTRAINT PK_Noodle PRIMARY KEY (id),
INDEX ix_graphid UNIQUE ($node_id)

)
AS NODE
GO

[hgschmie]

This is an example with oracle and outparameters with cursors: https://github.com/jdbi/jdbi3-oracle12/blob/master/src/test/java/org/jdbi/v3/oracle12/TestOutparameterCursor.java

Could you post the stack trace for the NPE?

[0x1F528]

I have created and posted a minimal application to reproduce my issue,
https://github.com/0x1F528/JDBI_Test

I am getting the NPE when trying to extract the sproc results (findFirst()).

Exception in thread "main" java.lang.NullPointerException at java.base/java.util.Objects.requireNonNull(Objects.java:209) at org.jdbi.v3.core.statement.OutParameters.lambda$getRowSet$1(OutParameters.java:274) at org.jdbi.v3.core.result.internal.ResultSetResultIterable.iterator(ResultSetResultIterable.java:51) at org.jdbi.v3.core.result.ResultIterable.findFirst(ResultIterable.java:241) at com.noodletest.ServerMain.lambda$test_jdbi_sproc$1(ServerMain.java:77) at org.jdbi.v3.core.Jdbi.withHandle(Jdbi.java:409) at com.noodletest.ServerMain.test_jdbi_sproc(ServerMain.java:69) at com.noodletest.ServerMain.main(ServerMain.java:27)

[0x1F528]

As for using a Cursor, it would seem that SQLServer does not support:
com.microsoft.sqlserver.jdbc.SQLServerException: REF_CURSOR is not supported.

[hgschmie]

Thanks. I will take a look.

[hgschmie]

I think you have the same problem as #2557; you need ResultBearing support directly from Call, not through OutParameters. There seems to be a relevant MS JDBC driver issue here: microsoft/mssql-jdbc#98

[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.

[hgschmie]

Shipped as part of 3.43.0. Please test.