Prepared statement with PreparedStatement.RETURN_GENERATED_KEYS throws SQLServerException when invoked with SET NOCOUNT ON;

[martinoconnor]

Driver version

12.8.1.jre11

SQL Server version

Microsoft SQL Server 2022 (RTM-CU16) (KB5048033) - 16.0.4165.4 (X64) Nov 6 2024 19:24:49 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS)

Client Operating System

Linux - Fedora 41

JAVA/JVM version

openjdk 21.0.5 2024-10-15 LTS
OpenJDK Runtime Environment Temurin-21.0.5+11 (build 21.0.5+11-LTS)
OpenJDK 64-Bit Server VM Temurin-21.0.5+11 (build 21.0.5+11-LTS, mixed mode, sharing)

Table schema

CREATE TABLE [dbo].[test] (
[Id] INT IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Problem description

The following code throws an exception:
try (Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=my_database;trustServerCertificate=true", "username", "password")) { PreparedStatement ps = con.prepareStatement("SET NOCOUNT ON; INSERT INTO dbo.test DEFAULT VALUES ;", PreparedStatement.RETURN_GENERATED_KEYS); ps.executeUpdate(); }

Expected behavior

I should be able to retrieve the generated ID

Actual behavior

Exception thrown

Error message/stack trace

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:242)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:696)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:594)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7745)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4391)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:276)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:537)
at Main.main(Main.java:11)

Any other details that can be helpful

State management of the ResultSet is incorrect if the update count is missing in the stream

JDBC trace logs

trace.txt

[machavan]

SET NOCOUNT ON; is blocking the generated keys to be returned appropriately. Any specific reason this is added in the sql statement ?

Can you try this ?

        try (Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=my_database;trustServerCertificate=true", "username", "password")) { 
    PreparedStatement ps = con.prepareStatement("INSERT INTO dbo.test DEFAULT VALUES PreparedStatement.RETURN_GENERATED_KEYS); 
    ps.executeUpdate(); 
} 

Also, in order to fetch the generated IDs, they will need to be fetched explicitly with a code similar to the below :

         try (Connection con = getConnection()) {
                String sql = "INSERT INTO dbo.test DEFAULT VALUES";
                try(PreparedStatement pstmt = con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS)) {
                    pstmt.executeUpdate();
                    try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                        if (generatedKeys.next()) {
                            int id = generatedKeys.getInt(1);
                           System.out.println(id);              
                        }
                    }
                }
            }

https://learn.microsoft.com/en-us/sql/connect/jdbc/using-auto-generated-keys?view=sql-server-ver16

[machavan]

@martinoconnor We are looking into this further as well on state management when SET NOCOUNT ON;

[martinoconnor]

The example code attached was the minimum required to demonstrate the issue. You can imagine in a larger application that SET NOCOUNT ON is issued earlier, perhaps when the connection is first opened. With the subsequent code that attempts to retrieve the generated key, the resulting exception will likely be surprising to the developer.

[martinoconnor]

To follow up, the code without the SET NOCOUNT ON works as expected

[machavan]

UPDATE: A fix (+ testcases) for this issue are being attempted as part of the already ongoing PR for generated keys related functionality #2550

PR: #2554