Driver version

10.2.1

SQL Server version

Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06

Client Operating System

Windows 10

JAVA/JVM version

11.0.10

Table schema

none

Problem description

SQLServerPreparedStatement.retryBasedOnFailedReuseOfCachedHandle is not properly detecting if the handle is still valid.
After DB server restart server handle IDs are reset and the numbering starts from 1. So the handle ID from cache may refer to a completely different statement after server restart. The result is unpredictable and may lead to data loss/damaging.

----------sample code-----------------------
public class ZopaSqlTest {

public static void main(String[] args) throws SQLException, IOException {
    String statement1 = "select SYSTEM_USER where SYSTEM_USER not in (?)";
    String statement2 = "select SYSTEM_USER where SYSTEM_USER not in (?, ?)";
    try(Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=master;sendStringParametersAsUnicode=false;disableStatementPooling=false;statementPoolingCacheSize=999999;encrypt=false", "username", "password")){

        runPrepared2(connection, statement1, "1");
        runPrepared2(connection, statement2, "1", "2");
        runPrepared2(connection, statement1, "1");
        runPrepared2(connection, statement2, "1", "2");
        System.out.println("Restart DB Server and press enter...");
        System.in.read();
        runPrepared2(connection, statement2, "1", "2");
        runPrepared2(connection, statement1, "1");//here the statement2 is executed instead of statement1
    }
}

private static void runPrepared2(Connection connection, String sql, String... params) throws SQLException {
    try(PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            preparedStatement.setString(i + 1, params[i]);
        }
        preparedStatement.execute();
    }
}

}

Expected behavior

After restart the server handle ID should not be used.

Actual behavior

The the server handle ID should is used, a wrong statement is executed and a "missing parameter" exception is thrown.

Error message/stack trace

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The parameterized query '(@p0 varchar(8000),@p1 varchar(8000))select SYSTEM_USER where SY' expects the parameter '@p1', which was not supplied.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7730)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3786)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:515)
at com.edifecs.archival.etl.ZopaSqlTest.runPrepared2(ZopaSqlTest.java:32)
at com.edifecs.archival.etl.ZopaSqlTest.main(ZopaSqlTest.java:23)

Any other details that can be helpful