SQLServerException: The index 58 is out of range - JDBC

I was executing a stored procedure against SQL SERVER 2008 database from Java program using CallableStatement, but unfortunately, I was getting 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 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.e. 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 SERVER 2008 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)

Reason: 
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 place holders 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 "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 placeholder was 34 but you are calling setXXX() method 35 times i.e. more than then 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 in JDBC then you can also refer JDBC Recipes: A Problem-Solution Approach book to learn more about how to connect database from Java application:

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


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)


Further Reading
If you are new in JDBC and looking for some good books to learn JDBC, then you can refer these books:
  • Practical Database Programming with Java By Ying Bai (see here)
  • JDBC Recipes: A Problem-Solution Approach (see here)

1 comment:

  1. I had spent 3 days to find the root cause of "com.microsoft.sqlserver.jdbc.SQLServerException: The index 46 is out of range" in one of Java application which uses SQL Server stored procedure to insert and update data. I checked that number of placeholder was equal to number of setString() or setXXX() call we are making, but still we are getting the same error. After much investigation and by luck I found that the stored procedure had 46 parameters but we are only setting 45 parameters from Java side because the last one was always populated by stored procedure. When I added two new parameters, I added them after this last parameter which are set in stored procedure, this caused the problem because now Java layer need to set parameter at 46th index but there was only 45 placeholder. This is quite weird to be honest, but if you face the issue to check stored proc as well. The issue resolved by changing the order of parameters in stored proc e.g. adding two new paramters before this last parameter which is set by default in stored procedure. This way, number of placeholder, number of setXXX() call and parameter index in stored proc all matched.

    ReplyDelete