MySQL Bugs: #5188: CachedRowSet errors using PreparedStatement

Bug #5188	CachedRowSet errors using PreparedStatement
Submitted:	24 Aug 2004 18:44	Modified:	28 Aug 2004 0:03
Reporter:	Ian Brandon	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.3b	OS:	Linux (Linux Fedora core 1)
Assigned to:	Eric Herman	CPU Architecture:	Any

Description:
When using 'CachedRowSetImpl to access query results via a ** PreparedStatement  **,  character fields are incorrectly returned - e.g. as an object address  ? rather than a String.

   Note:  CachedRowSetImpl works ok with ordinary Statements;
               Also ResultSets test ok using PreparedStatements;

                The above problem does NOT occur in mysql server ver. 4.0.18
   

How to repeat:
1) Create a simple 2 field table  with a primary key & populate it with some data e.g.  

Create table testTable(
  ID int NOT NULL AUTO_INCREMENT, 
  datafield VARCHAR(64), PRIMARY KEY(ID)
)

INSERT INTO testTable(datafield)  values('test data stuff !')
              

2)  On querying the above table via a 'CachedRowSet,  you would expect to see a 1 in the primary key field and the string value 'test data stuff ! in the varchar field  :- 

        ID                  datafield
       ===                =======
         1                    test data stuff !       // expected results
         1                   B@f18e8e              //  ** actual  results **

 
The actual results for the varchar field appear to be e.g. an object address as shown above.

<snippet>

   import com.sun.rowset.CachedRowSetImpl;
     .
     .
   CachedRowSetImpl crs = null;
   Connection connection = null;
   PreparedStatement pstmt = null;

   String driver = "com.mysql.jdbc.Driver";
   String dbURL = "jdbc:mysql://127.0.0.1/database_name";

   String sql = "SELECT * FROM testTable where ID = ?";

   try{

     Class.forName(driver);
     connection=DriverManager.getConnection(dbURL, "", "");

     pstmt = connection.prepareStatement(sql);

     pstmt.setString(1, "1");

     ResultSet rs = pstmt.executeQuery();

    // create a CachedRowSet and populate it  
    crs = new CachedRowSetImpl(); 
    crs.populate(rs);    

    // scroll through CachedRowSet & return results to screen ...
    if (crs.next)) {
      System.out.print(crs.getString("ID"));
      System.out.println(crs.getString("datafield"));
    }

  }  // end try

   catch(SQLException e) {
       .
       .
   }

</snippet>

ps  the snippet is just a guideline - I appreciate scrolling thru' the CachedRowSet should be of the form    if (crs.next()){  ..

One more thing - I'm using Connector J 3.13 beta...

finally - the java platform =  j2sdk1.4.2_04,  
IDE = netbeans 3.5.1  (uses tomcat 4.0..)

I could not repeat this bug within a *very* similar environment.

 * MySQL version 4.1.3b
 * java java version "1.4.2_05"
 * Linux Mandrake 10.0 

Because attempting to reproduce this bug requires the Sun reference implementation, I will not be checking this into the codebase. However I've included it here for review:

    /**
     * Tests fix for BUG#5188, CachedRowSet errors using PreparedStatement.
     *
     * @throws Exception
     */
    public void testBug5188() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5188");
            this.stmt.executeUpdate("CREATE TABLE testBug5188 "
                    + "(ID int NOT NULL AUTO_INCREMENT, "
                    + "datafield VARCHAR(64), " + "PRIMARY KEY(ID))");
                                                                                
            this.stmt.executeUpdate("INSERT INTO testBug5188(datafield) "
                    + "values('test data stuff !')");
                                                                                
            CachedRowSetImpl crs = null;
            String sql = "SELECT * FROM testBug5188 where ID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "1");
            rs = pstmt.executeQuery();
            // create a CachedRowSet and populate it
            crs = new CachedRowSetImpl();
            crs.populate(rs);
            // scroll through CachedRowSet & return results to screen ...
            assertTrue(crs.next());
            assertEquals("1", crs.getString("ID"));
            assertEquals("test data stuff !", crs.getString("datafield"));
            assertFalse(crs.next());
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5188");
        }
    }

One notable difference: I ran the test using the latest Connector/J 3.1 from source control, not a tagged version.

Yes thanks Eric !  .. looks like it was the Connector / J 3.1 beta.  Following your tip I tried using Connector J 3.014  *production ver.*  instead (I'm  not sure how to get stuff from 'source control')  and it worked !!