Error getting generated keys after insert

[OS-veracardoso]

Driver version

12.8.1.jre11

SQL Server version

Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) 
	Nov 14 2023 18:33:19 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Linux (Ubuntu 22.04.3 LTS) <X64>

Client Operating System

Linux (Ubuntu 22.04.3 LTS)

JAVA/JVM version

java-1.21.0-openjdk-amd64

Table schema

CREATE TABLE TEST_GENKEYS (ID int NOT NULL IDENTITY(1,1) PRIMARY KEY, NAME varchar(32));
CREATE TABLE TEST_GENKEYS_TRIGGER (ID int NOT NULL IDENTITY(1,1) PRIMARY KEY);
CREATE TRIGGER TRIGGER_ON_INSERT ON TEST_GENKEYS FOR INSERT AS INSERT INTO TEST_GENKEYS_TRIGGER DEFAULT VALUES;
INSERT INTO TEST_GENKEYS (NAME) VALUES('test');

Problem description

Afterperforming an INSERT opertaiont, the call to getGeneratedKeys throws an exception when there is a trigger on the table where the insert is made. Take for instance the following code snippet:

        String mssqlUrl = "jdbc:sqlserver://localhost:1433;user=[USER];password=[PWD];databaseName=my_database;"
                + "trustServerCertificate=true;calcBigDecimalPrecision=true";
        try (Connection connection = DriverManager.getConnection(mssqlUrl)) {
            try {
                Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                String sql = "INSERT INTO TEST_GENKEYS (NAME) VALUES('test')";
                statement.execute(sql, List.of("ID").toArray(String[]::new));
                ResultSet generatedKeys = statement.getGeneratedKeys();
                System.out.println(generatedKeys);
            } catch (SQLException e) {
                System.out.println(e);
            }
        }

Expected behavior

The record is correctly inserted, the generated keys are returned and no exception is thrown.

Actual behavior

The driver throws an exception and fails to return the generated keys.
The record is correctly inserted.

Error message/stack trace

The statement must be executed before any results can be obtained.

Any other details that can be helpful

If the insert statement has more than one value, the result is the same and the update count is returned as 1 also. This also seems wrong, because all the records are inserted.

JDBC trace logs

com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:242)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(SQLServerStatement.java:2319)

[machavan]

Changing

statement.execute(sql, List.of("ID").toArray(String[]::new));

to

statement.executeUpdate(sql, List.of("ID").toArray(String[]::new));

will resolve this issue.

We are investigating the observed behavior with respect to execute API.

We hope that the above workaround will unblock you.

[machavan]

#2554 merged