| Bug #19724 | ResultSet.updateXxx gives "Parameter index out of range" if MySQL in ansi mode | ||
|---|---|---|---|
| Submitted: | 11 May 2006 14:18 | Modified: | 26 Jul 2006 20:17 |
| Reporter: | Tim Parish | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S2 (Serious) |
| Version: | Connector/J 3.1.12 | OS: | Microsoft Windows (Windows XP Prof/Linux (Debian sarge)) |
| Assigned to: | Tonci Grgin | Target Version: | |
[11 May 2006 14:32]
Tim Parish
The stack trace I submitted was for an occasion when I tried using updateInt() but the
code I submitted used updateString(). For the sake of completeness, here's the trace for
using updateString as my submitted code does:
Exception in thread "main" java.sql.SQLException: Parameter index out of range (2 > number
of parameters, which is 1).
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2250)
at com.mysql.jdbc.PreparedStatement.setBytes(PreparedStatement.java:2019)
at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1355)
at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:2298)
at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:2336)
at com.blah.db.MySqlAnsiBug.main(MySqlAnsiBug.java:36)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
...
It ends up blowing up in the same place anyway, it just goes via updateString().
[11 May 2006 15:40]
Tonci Grgin
Hi Tim. Thanks for your bug report. I was able to verify it. Attached test case runs if
mode <> ansi.
run:
java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is
1).
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2250)
at com.mysql.jdbc.PreparedStatement.setBytes(PreparedStatement.java:2019)
at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1355)
at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:2298)
at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:2336)
at test19724.mytest.runTest(mytest.java:57)
at test19724.mytest.main(mytest.java:111)
BUILD SUCCESSFUL (total time: 1 second)
MySQL 4.1.20 debug log on Suse 10 (bk build).
MySQL-connector-java-3-1-12-bin
JDK 1.6
WINXP Pro SP2
[11 May 2006 15:43]
Tonci Grgin
Java code to reproduce error
Attachment: 19724test.zip (application/zip, text), 1.26 KiB.
[11 May 2006 17:12]
Tim Parish
Hi Tonci, Thanks for your replies. I'm glad you could easily reproduce the issue. It might also be worth mentioning that I'm pretty sure this used to work with Connector/J 3.0.15 and MySQL 4.0.15 - 4.0.2x. At that time I think we were using Java 1.4.2. I forgot to mention previously that currently we are using Java 1.5.0_05.
[30 May 2006 21:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/7045
Description: When attempting to call updateString(colName, value) on an updateable ResultSet an SQLException is thrown complaining: "Parameter index out of range (5 > number of parameters, which is 4). This also happens with other update methods, like updateInt(colName, value) and also when the column index is specified instead of the name. The stack trace is as follows: Exception in thread "main" java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1). at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2250) at com.mysql.jdbc.PreparedStatement.setBytes(PreparedStatement.java:2019) at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1355) at com.mysql.jdbc.UpdatableResultSet.updateInt(UpdatableResultSet.java:1970) at com.blah.db.MySqlAnsiBug.main(MySqlAnsiBug.java:36) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) ... It seems to be blowing up in a PreparedStatement that the driver is producing to carry out the updateString() - a PreparedStatement which I have no control over. This only happens when MySQL is running in ansi mode - set by putting ansi in the [mysqld] section in my.ini/my.cnf. updateXxx(colName, value) works fine when MySQL is NOT in ansi mode. My workaround was to rewrite the offending code so that I didn't call updateXxx() - I used separate Statements for doing the update. Note that this seems very similar to bug 18722 and may well be a duplicate of that. Version info: MySQL 4.1.11 Connector/J 3.1.12 Windows XP Professional and Linux (Debian sarge) How to repeat: Simply try to use updateString()/updateInt() with MySQL in ansi mode. For example, the following code gives the exception when MySQL is in ansi mode, but runs fine when it is not: public class MySqlAnsiBug { public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); Connection connection = null; try { connection = DriverManager.getConnection("jdbc:mysql://localhost/testing", "testing", "testing"); Statement statement = connection.createStatement(); statement.executeUpdate("CREATE TABLE testing (col1 INTEGER NOT NULL, col2 VARCHAR(255) NULL, PRIMARY KEY (col1))"); statement.executeUpdate("INSERT INTO testing VALUES (0, 'Blah'),(1, 'Boo')"); statement.close(); Statement updateableResultStmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); ResultSet updateableResults = updateableResultStmt.executeQuery("SELECT * from testing"); updateableResults.next(); updateableResults.updateString("col2", "blah2"); updateableResults.updateRow(); updateableResults.close(); updateableResultStmt.close(); } finally { if (connection != null) { connection.close(); } } } }