MySQL Bugs: #19724: ResultSet.updateXxx gives "Parameter index out of range" if MySQL in ansi mode

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:

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();
            }
        }
    }
}

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().

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

Java code to reproduce error

Attachment: 19724test.zip (application/zip, text), 1.26 KiB.

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.

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