Prepared statement with named parameters throws index out of range erroneously

[tboemker]

Driver version

8.2.2

SQL Server version

Microsoft SQL Server 2017 (RTM-CU13-OD) (KB4483666) - 14.0.3049.1 (X64) 
	Dec 15 2018 11:16:42 
	Copyright (C) 2017 Microsoft Corporation
	Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Client Operating System

Microsoft Windows [Version 10.0.17134.1304]

JAVA/JVM version

openjdk version "1.8.0_181-1-ojdkbuild"
OpenJDK Runtime Environment (build 1.8.0_181-1-ojdkbuild-b13)
OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)

Table schema

There are no tables. There is one stored procedure:

CREATE PROCEDURE demo_index_bug
  @x VARCHAR = NULL
, @y VARCHAR
AS
BEGIN
 SELECT 1
END

Problem description

1. Expected behaviour:
   No exception.
2. Actual behaviour:
   SQLException thrown.
3. Error message/stack trace:

com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1116)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:1130)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setString(SQLServerCallableStatement.java:1900)
	at Main.run(Main.java:35)
	at Main.main(Main.java:15)

4. Any other details that can be helpful:

JDBC trace logs

Reproduction code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class Main {

	private final String host;
	private final String database;
	private final String user;
	private final String password;

	public static void main(String[] args) {
		try {
			new Main(args).run();
		}
		catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public Main(String[] args) {
		this.host = args[0];
		this.database = args[1];
		this.user = args[2];
		this.password = args[3];
	}
	
	public void run()
	throws Exception {
		String connectionUrl = String.format("jdbc:sqlserver://%s;databaseName=%s", host, database);
        Connection conn = DriverManager.getConnection(connectionUrl, user, password);
		CallableStatement cs = conn.prepareCall("{call demo_index_bug(@y=?)}");
		cs.setString("y", "y");
		cs.executeQuery();
	}
}

[rene-ye]

Hi @tboemker, I can confirm the reproduction code works and we will get back to you soon.

[ulvii]

Duplicate of #553
This is a limitation in the driver. As a workaround you can set optional parameters in CallableStatement.

        Connection conn = DriverManager.getConnection(connectionUrl, user, password);
                CallableStatement cs = conn.prepareCall("{call demo_index_bug(@x=?, @y=?)}");
                cs.setString("x", "x");
                cs.setString("y", "y");
                cs.executeQuery();
        }

Closing the issue, as it is already being tracked in #553 . Please let us know if you have any questions.

[tboemker]

I think this code snippet from SQLServerCallableStatement illustrates the crux of the probem:

    @Override
    public void registerOutParameter(String parameterName, int sqlType) throws SQLServerException {
        if (loggerExternal.isLoggable(java.util.logging.Level.FINER))
            loggerExternal.entering(getClassNameLogging(), "registerOutParameter",
                    new Object[] {parameterName, sqlType});
        checkClosed();
        registerOutParameter(findColumn(parameterName), sqlType);
        loggerExternal.exiting(getClassNameLogging(), "registerOutParameter");
    }

findColumn returns the index of a parameter in the definition of the stored procedure, but the result is being used as the index of an argument in the call. Those are two completely different lists, and the indices for one do not apply to the other.

[tboemker]

If the arguments are not specified in the same order as the parameters, the driver assigns values incorrectly.

Stored procedure:

CREATE PROCEDURE demo_index_bug
  @x VARCHAR = NULL
, @y VARCHAR
AS
BEGIN
 SELECT @x, @y
END

Sample code:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class Main {

    private final String host;
    private final String database;
    private final String user;
    private final String password;
    private final String params;

    public static void main(String[] args) {
        try {
            new Main(args).run();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

    public Main(String[] args) {
        this.host = args[0];
        this.database = args[1];
        this.user = args[2];
        this.password = args[3];
        this.params = args[4];
    }

    public void run()
    throws Exception {
        String connectionUrl = String.format("jdbc:sqlserver://%s:1433;databaseName=%s%s", host, database, params);
        Connection conn = DriverManager.getConnection(connectionUrl, user, password);
        CallableStatement cs = conn.prepareCall("{call demo_index_bug(@y=?, @x=?)}"); // different order than in definition of stored procedure
        cs.setString("x", "x");
        cs.setString("y", "y");
        ResultSet rs = cs.executeQuery();
        while(rs.next()) {
            String x = rs.getString(1);
            String y = rs.getString(2);
            System.out.printf("x=%s, y=%s\n", x, y);
        }
    }
}

Output:

x=y, y=x

At this point, a more realistic stored procedure would likely have stored the value "y" where "x" was supposed to go and vice versa; the database would now be corrupted.

[ulvii]

Hi @tboemker
Another workaround would be to use indexes instead of named parameters.

        CallableStatement cs = conn.prepareCall("{call demo_index_bug(@y=?, @x=?)}"); // different order than in
                                                                                      // definition of stored procedure
        cs.setString(1, "y");
        cs.setString(2, "x");
        ResultSet rs = cs.executeQuery();
        while (rs.next()) {
            String x = rs.getString(1);
            String y = rs.getString(2);
            System.out.printf("x=%s, y=%s\n", x, y);
        }

We are aware of the deficiencies you mentioned. #547 is meant to redesign the way CallableStatements work in the driver. We will make sure to consider this issue too.