Sqlserverexception: The Index 58 Is Out Of Make - Jdbc

I was executing a stored physical care for against SQL SERVER 2008 database from Java program using CallableStatement, simply unfortunately, I was getting next fault "SQLServerException: The index 58 is out of range". Since I am passing a lot of parameters I idea that something is incorrect amongst a release of parameters I was passing to the stored proc. My stored physical care for had 58 INPUT parameters, every bit presently every bit I removed the 58th INPUT parameter the fault goes away, which confirmed my belief that SQL SERVER supports a maximum of 57 INPUT parameters inwards stored physical care for via JDBC. This  seems the actually meaning limitation, peculiarly for big tables in addition to I was thinking that It's to a greater extent than probable that 58 is non the actual bound in addition to I mightiness convey missed something.

My suspicion was correct fifty-fifty though the fault goes away every bit presently every bit I removed the 58th parameter, it was nix to exercise amongst SQL SERVER bound on stored proc parameters simply it was the release of placeholders I had defined entirely 57 placeholders simply I was setting information for 58th parameter using setString() method in addition to that was causing this exception.  How exercise I acquire to know that? Luckily I in 1 lawsuit again got the same fault simply this time, it was complaining well-nigh 35 beingness 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 acquire the next fault land executing stored physical care for against SQL SERVER 2008 or whatever 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 argue was  not the SQL SERVER bound simply it was the release of placeholders. I had defined 34 placeholder simply setting information for the 35th column using setString(), setInt() method in addition to that's why JDBC complaining that index 35 is out of range. Remember, the first column inwards JDBC starts amongst index 1 in addition to non zero.


Java Example
This fault tin likewise come upward when you lot are using PreparedStatement or CallableStatement in JDBC. Here is an SQL interrogation to think mass details using ISBN release past times calling a stored physical care for 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 volition throw:

com.microsoft.sqlserver.jdbc.SQLServerException: The index two 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 entirely 1 interrogation parameter required past times the stored physical care for "?" in addition to index starts from 1 inwards JDBC rather than zero. Hence, index two is invalid because there is no instant placeholder inwards your SQL query.

This fault tin likewise come upward when you lot are executing PreparedStatement based SQL interrogation which contains house holders or bind parameters every bit 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 volition throw, the index three is out of make error, every bit you lot tin run across it below:

com.microsoft.sqlserver.jdbc.SQLServerException: The index three 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 lot run across SQLServerException: The index 1 is out of range, it agency at that topographic point is no placeholder inwards PreparedStatement of CallableStatement simply you lot are notwithstanding setting values past times calling diverse setXXX() methods.


That's all well-nigh the actual drive of "com.microsoft.sqlserver.jdbc.SQLServerException: The index 35 is out of range" fault in addition to how to cook it. The actual argue was that release of placeholder was 34 simply you lot are calling setXXX() method 35 times i.e. to a greater extent than than hence release of defined placeholders. Once you lot take the extra setXXX() telephone band or increased the release of placeholders, the fault was fixed.

If you lot are novel inwards JDBC hence you lot tin likewise refer JDBC Recipes: H5N1 Problem-Solution Approach mass to acquire to a greater extent than well-nigh how to connect database from Java application:

Related JDBC tutorials in addition to troubleshooting guides

• Difference betwixt type 1 in addition to type four JDBC drivers inwards Java (answer)
• Top 10 JDBC Interview Questions for Java programmers (list)
• How to solve java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver? (solution)
• Cause in addition to 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 inwards JDBC Java? (solution)
• Top 10 JDBC Best Practices for Java develoeprs (list)
• java.sql.SQLException: No suitable driver constitute for jdbc:jtds:sqlserver (solution)
• java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver (solution)

Further Learning
JSP, Servlets in addition to JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 in addition to 2
Java Platform: Working amongst Databases Using JDBC