Bug #2692 PreparedStatement binding malfunction

• Submitted: 9 Feb 2004 12:40
• Modified: 25 Feb 2004 0:45
• Reporter: [ name withheld ]
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.1.1
• OS: Windows (Win98)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[9 Feb 2004 12:40] [ name withheld ]

Description:
Another weird malfunction of PreparedStatement. The binding works well, if there is only one '?' in the query. With two or more parameters, the system gets totally confused. The code sample above returns '1' as the result of the count(1) select even though not only the order of the binding is wrong, but the filed 'idb' absolutely not contains the value binded.

See bug 2671, probably it is related.

Bugs 2623,2671 and this makes PreparedStatement almost unusable. :(

Environment:
- MySQL server 4.1.1-alpha on Linux. (Mandrake 8.1)
- mysql-connector-java-3.1-nightly-20040203-bin.jar (on Win98) 
- JSDK 1.4.2 b-28 (on Win98)
- Apache Tomcat 4.1.29 (on Win98)

How to repeat:
The script:
drop table test4;
CREATE TABLE test4 (
  ida int(8),
  idb int(8),
  code varchar(30) binary
) TYPE=InnoDB DEFAULT CHARSET=utf8;
insert into test4 values ('1','2','blewis');

The code:
  String qry="                " +
    "select count(1) from     " +
    "  test4                  " +    
    "where                    " +
    "  idb = ?                " +
    "  and code = ?           ";
   
  try
  {
    java.sql.PreparedStatement pstmt=conn.prepareStatement(qry);

    //Watch the order, and even value!!!
    pstmt.setString(1 ,"blewis");
    pstmt.setInt   (2 ,123); //<- value is not in table!

    java.sql.ResultSet rs=pstmt.executeQuery();
    rs.next();
    out.println(rs.getInt(1));

    rs.close();
    pstmt.close();
  }
  catch (java.sql.SQLException sqlEx)
  {
    // handle any errors 
    out.println("Error."); 
    out.println(" SQLException: " + sqlEx.getMessage()); 
    out.println(" SQLState:     " + sqlEx.getSQLState()); 
    out.println(" VendorError:  " + sqlEx.getErrorCode());
  }

[10 Feb 2004 7:11] Mark Matthews

This issue doesn't seem to appear with the latest code for Connector/J 3.1 and MySQL-4.1.2 (from BitKeeper). Please see a nightly snapshot build of Connector/J 3.1 after 00:00 GMT, Febuary 11th.

[25 Feb 2004 0:37] [ name withheld ]

Yes, the problem seems to be eliminated in the new versions. You can close this bug. Thx.