sql server - JDBC4: The Index 6 Is Out Of Range When 6th parameter is NOT NULL after a series of NULL parameters

Question

UnsignedByte is defined to be 0-255. .getValue() returns Short.

pts1, pts2, pts3 are ALL null or ALL not null (input checks beforehand guarantee).

addlevel(...)'s CallableStatement WORKS for these scenarios:

1. (gameNo, levelNo, null, null, null, null)
2. (gameNo, levelNo, pts1, pts2, pts3, null)
3. (gameNo, levelNo, pts1, pts2, pts3, typeNo)

but throws

com.microsoft.sqlserver.jdbc.SQLServerException: The index 6 is out of range.

on:

(gameNo, levelNo, null, null, null, typeNo)

the right statement called is marked with a comment in java code segment.

.......................................................................................................................................

SP:

CREATE PROCEDURE addLevel
@gameNo int,
@levelNo int,
@pts1 int = NULL,
@pts2 int = NULL,
@pts3 int = NULL,
@typeNo tinyint = NULL
AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblLevel (gameNo, levelNo, pointsForStar1, pointsForStar2, pointsForStar3, typeNo)
     VALUES (@gameNo, @levelNo, @pts1, @pts2, @pts3, @typeNo)
END
GO

java:

public boolean addLevel(int gameNo, int levelNo, Integer pts1star, Integer pts2star, Integer pts3star, UnsignedByte typeNo){
    try{
        CallableStatement cstmt;
        if (pts1star != null){
            if (typeNo != null)
                cstmt = database.getConnection().prepareCall("{call dbo.addLevel(?,?,?,?,?,?)}");
            else
                cstmt = database.getConnection().prepareCall("{call dbo.addLevel(?,?,?,?,?)}");
            cstmt.setInt("pts1", pts1star);
            cstmt.setInt("pts2", pts2star);
            cstmt.setInt("pts3", pts3star);
        }
        else{
            if (typeNo != null)
                cstmt = database.getConnection().prepareCall("{call dbo.addLevel(?,?,?)}"); //<<<<<--------------- THIS ONE
            else
                cstmt = database.getConnection().prepareCall("{call dbo.addLevel(?,?)}");
        }
        cstmt.setInt("gameNo", gameNo);
        cstmt.setInt("levelNo", levelNo);
        if (typeNo != null)
            cstmt.setShort("typeNo", typeNo.getValue());
        cstmt.executeUpdate();
        return true;
    }
    catch(SQLException e){
        e.printStackTrace();
        return false;
    }
}

Answer 1

Stored procedure support in Java is positional (even if you use the parameter names to set the parameter value). typeNo is parameter 6 of your stored procedure. Your 'faulty' call defines three parameters. Parameter three is pts1. You need to always call the six parameter version if you want to set typeNo and explicitly setNull the parameters that don't have a value.

Specifically for SQL Server, the {call dbo.addLevel(?,?)} is translated to something like the following (with positional parameters):

EXEC dbo.addLevel ?, ?, ?

When you set the parameter value by name, the driver looks up the parameter position, and attempts to set that parameter. typeNo is position six, so it tries to set parameter 6, which is not defined in this query.

In SQL Server when you execute a stored procedure, you can leave out trailing positional parameters with defaults, or you need to explicitly specify the parameter name (assuming all other parameters have a default):

EXEC dbo.addLevel @gameNo=?, @levelNo=?, @typeNo=?

As far as I know you should be able to use the above query with CallableStatement instead of the call-escape (although I believe this will require using the parameter position to set the value, and not by name).