12.6.x has broken the ability to call procedures with output parameters by their four-part syntax

[mjadczak]

Driver version

12.6.1 (issue introduced in 12.6.0)

SQL Server version

Microsoft SQL Server 2019 (RTM-CU25) (KB5033688) - 15.0.4355.3 (X64) 
	Jan 30 2024 17:02:22 
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

Client Operating System

Linux

JAVA/JVM version

Java 21.0.1

Table schema

N/A

Problem description

Since 12.6.0, stored procedure calls using the four-part syntax (i.e. including a linked server name) are broken, at least when using output parameters and being executed using the CALL JDBC escape syntax. I have not tested other permutations, such as the EXEC syntax and no return parameters.

Here is a short script to reproduce the issue:

package mssql.repro;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

import java.sql.JDBCType;
import java.sql.SQLException;

public class MsSqlRepro {
    private static final String server = ""; // the server to connect to
    private static final String linkedServer = "LOCAL_L"; // the linked server pointing back to the same server
    private static final String procDefinition = """
            create or alter procedure dbo.TestAdd(@Num1 int, @Num2 int, @Result int output) as
            begin
                set @Result = @Num1 + @Num2;
            end;
            """;

    public static void main(String[] args) throws SQLException {
        System.setProperty("sun.security.jgss.native", "true");
        System.setProperty("javax.security.auth.useSubjectCredsOnly", "false");
        var ds = new SQLServerDataSource();
        ds.setServerName(server);
        ds.setDatabaseName("tempdb");
        ds.setTrustServerCertificate(true);
        ds.setIntegratedSecurity(true);
        ds.setAuthenticationScheme("JavaKerberos");
        ds.setUseDefaultGSSCredential(true);

        try (var conn = ds.getConnection()) {
            try (var stmt = conn.createStatement()) {
                stmt.execute(procDefinition);
            }
            try (var stmt = conn.prepareCall("{call %s.tempdb.dbo.TestAdd(?, ?, ?)}".formatted(linkedServer))) {
                stmt.setInt(1, 1);
                stmt.setInt(2, 2);
                stmt.registerOutParameter(3, JDBCType.INTEGER);
                stmt.execute();
                System.out.println(stmt.getInt(3));
            }
        }
    }
}

Expected behavior

The above code snippet prints "3", as it does if used with driver version 12.4.x and earlier, or when used without the linked-server call syntax.

Actual behavior

An exception is thrown within the driver when stmt.getInt(3) is called.

Error message/stack trace

Mar 07, 2024 4:50:15 PM com.microsoft.sqlserver.jdbc.SQLServerCallableStatement skipOutParameters
INFO: sp_executesql SQL: EXEC LOCAL_L.tempdb.dbo.TestAdd  @P0 ,  @P1 ,  @P2  OUT Unexpected outParamIndex: 0; adjustment: 0
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The TDS protocol stream is not valid.
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:4266)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:4255)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.throwInvalidTDS(SQLServerConnection.java:4238)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.skipOutParameters(SQLServerCallableStatement.java:431)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getOutParameter(SQLServerCallableStatement.java:226)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getterGetParam(SQLServerCallableStatement.java:517)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getValue(SQLServerCallableStatement.java:522)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getInt(SQLServerCallableStatement.java:563)
	at mssql.repro.MsSqlRepro.main(MsSqlRepro.java:37)

Any other details that can be helpful

From what I know of the changes in the 12.6.x series, I believe the issue here may be that the code which tries to detect whether a statement is using exec or call syntax already (and therefore for which the additional sp_executesql wrapping layer can be skipped) does not correctly recognise the four-part syntax being used here.

JDBC trace logs

Mar 07, 2024 5:04:08 PM com.microsoft.sqlserver.jdbc.SQLServerCallableStatement skipOutParameters
INFO: sp_executesql SQL: EXEC LOCAL_L.tempdb.dbo.TestAdd  @P0 ,  @P1 ,  @P2  OUT Unexpected outParamIndex: 0; adjustment: 0
Mar 07, 2024 5:04:08 PM com.microsoft.sqlserver.jdbc.SQLServerCallableStatement skipOutParameters
INFO: sp_executesql SQL: EXEC LOCAL_L.tempdb.dbo.TestAdd  @P0 ,  @P1 ,  @P2  OUT Unexpected outParamIndex: 0; adjustment: 0

[Jeffery-Wasty]

Hi @mjadczak,

We'll look into this immediately, thank you.