How to Fix SQLServerException: The index is out of range? JDBC Example

I was executing a stored procedure against SQL SERVER 2008 database from Java program using CallableStatement, but unfortunately, I was getting the following error "SQLServerException: The index 58 is out of range". Since I am passing a lot of parameters I thought that something is wrong with a number of parameters I was passing to the stored proc. My stored procedure had 58 INPUT parameters, as soon as I removed the 58th INPUT parameter the error goes away, which confirmed my belief that SQL Server supports a maximum of 57 INPUT parameters in stored procedure via JDBC. 

This seems the really significant limitation, especially for big tables and I was thinking that It's more likely that 58 is not the actual limit and I might have missed something.

My suspicion was right even though the error goes away as soon as I removed the 58th parameter, it was nothing to do with the SQL Server limit on stored proc parameters but it was the number of placeholders I had defined only 57 placeholders but I was setting data for 58th parameter using setString() method and that was causing this exception.  

How do I get to know that? Luckily I again got the same error but this time, it was complaining about 35 being out-of-range, I mean com.microsoft.sqlserver.jdbc.SQLServerException: The index 35 is out of range. which confirmed my suspicion that I was missing something.

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

You get the following error while executing stored procedure against SQL Sever 2008, 2012, 2016, and 2018 or any other version from Java program:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 58 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setString(Unknown Source)
at org.apache.commons.dbcp.DelegatingCallableStatement
.setString(DelegatingCallableStatement.java:219)
at org.apache.commons.dbcp.DelegatingCallableStatement
.setString(DelegatingCallableStatement.java:219)

or

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 35 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setString(Unknown Source)

Root Cause of the Index out of Range SQLServerException in JDBC

As I said, the actual reason was not the SQL SERVER limit but it was the number of placeholders. I had defined 34 placeholder but setting data for the 35th column using setString(), setInt() method and that's why JDBC complaining that index 35 is out of range. Remember, the first column in JDBC starts with index 1 and not zero.

Java Example

This error can also come when you are using PreparedStatement or CallableStatement in JDBC. Here is an SQL query to retrieve book details using ISBN number by calling a stored procedure from Java Program using Callable Statement:

String SQL = "{call Books.dbo.usp_BookDetails_Get(?)}";
CallableStatement cs = con.prepareCall(SQL);
cs.setString(1, "978-0132778046");
cs.setString(2, "978-0132778047");
ResultSet rs = cs.executeQuery();

This code will throw:

com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
at com.microsoft.sqlserver.jdbc.
SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.
setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.
setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.
setString(SQLServerPreparedStatement.java:1048)

Why? because there is only one query parameter required by the stored procedure "?" and index starts from 1 in JDBC rather than zero. Hence, index 2 is invalid because there is no second placeholder in your SQL query.

This error can also come when you are executing PreparedStatement based SQL query which contains placeholders or bind parameters as shown below:

PreparedStatement ps = con.prepareStatement("SELECT * from Books WHERE ISBN=?");
ps.setString(1, "978-0132778047");
ps.setString(3, "1");

ResultSet rs = ps.executeQuery();

This code will throw, the index 3 is out of range error, as you can see it below:

com.microsoft.sqlserver.jdbc.SQLServerException: The index 3 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException
.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
.setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
.setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
.setString(SQLServerPreparedStatement.java:1048)

Similarly, if you see SQLServerException: The index 1 is out of range, it means there is no placeholder in PreparedStatement of CallableStatement but you are still setting values by calling various setXXX() methods.


That's all about the actual cause of the "com.microsoft.sqlserver.jdbc.SQLServerException: The index 35 is out of range" error and how to fix it. The actual reason was that number of placeholders was 34 but you are calling the setXXX() method 35 times i.e. more than the number of defined placeholders. Once you remove the extra setXXX() call or increased the number of placeholders, the error was fixed.  If you are new to JDBC then you can also refer to these free JDBC courses to learn more about how to connect databases from Java applications:

Related JDBC tutorials and troubleshooting guides

• Difference between type 1 and type 4 JDBC drivers in Java (answer)
• Top 10 JDBC Interview Questions for Java programmers (list)
• How to solve java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver? (solution)
• Cause and solution of java.lang.ClassNotFoundException: com.mysql.jdbc.Driver (solution)
• How to connect MySQL database from Java program? (guide)
• How to convert java.util.Date to java.sql.Date in JDBC Java? (solution)
• Top 10 JDBC Best Practices for Java develoeprs (list)
• java.sql.SQLException: No suitable driver found for jdbc:jtds:sqlserver (solution)
• java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver (solution)