Description:
Hello,
I noticed a problem with Resultsets of Type UPDATABLE, SCROLL_SENSITIVE:
The MySQL Connector reports a java.sql.SQLException: Bad format for DATE '...' when i move the cursor with absolute(2) and fetch a Date with getDate(x).
How to repeat:
Table - Definition / Data:
==========================
CREATE TABLE test (
i int NOT NULL,
d date NOT NULL
)
INSERT INTO test VALUES (1, NOW());
INSERT INTO test VALUES (2, NOW());
INSERT INTO test VALUES (3, NOW());
Java-Code - with SQLException:
==============================
try {
Connection conn = openConnection();
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
ResultSet rs = stmt.executeQuery(
"SELECT * FROM test"
);
rs.last();
int rowCount = rs.getRow();
for (int rowIndex=1; rowIndex<=rowCount; rowIndex++) {
rs.absolute(rowIndex);
System.out.println (
"i="+rs.getObject("i")+", "+
"d="+rs.getObject("d")
);
}
} catch (SQLException exc) {
System.err.println (exc);
}
Output:
=========
i=1, d=2007-11-23
java.sql.SQLException: Bad format for DATE '2007-11-23' in column 2.
As you can see, the first row can be read, but not the second row.
Java-Code - without SQLException:
=================================
try {
Connection conn = openConnection();
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
ResultSet rs = stmt.executeQuery(
"SELECT * FROM test"
);
while (rs.next()) {
System.out.println (
"i="+rs.getObject("i")+", "+
"d="+rs.getObject("d")
);
}
} catch (SQLException exc) {
System.err.println (exc);
}
Output:
=========
i=1, d=2007-11-23
i=2, d=2007-11-23
i=3, d=2007-11-23
As you can see, all rows can be read.
Suggested fix:
The sources of mysql-connector-java-5.1.5 I contain the class
com.mysql.jdbc.RowDataStatic
the Method
public ResultSetRow next() throws SQLException
initializes the Metadata of the current ResultSetRow, before it is returned. This Method is called by absolute(1);
The Method
public ResultSetRow getAt(int atIndex);
returns the ResultSetRow at Index atIndex without initializing the Metadata.
In class
com.mysql.jdbc.ResultSetRow
the Method
protected final java.sql.Date getDateFast(
int columnIndex,
byte[] dateAsBytes, int offset, int length, ConnectionImpl conn,
ResultSetImpl rs
) throws SQLException;
contains a catch-Clause which catches all Exceptions an throws a java.sql.SQLException with the above mentioned Errormessage.
I fixed the problem by inserting the initialization-code in the method
public ResultSetRow getAt(int atIndex);
Another solution (without modifying the sources) could be to iterate over the ResultSet (by calling next()) before calling absolute(x)
Description: Hello, I noticed a problem with Resultsets of Type UPDATABLE, SCROLL_SENSITIVE: The MySQL Connector reports a java.sql.SQLException: Bad format for DATE '...' when i move the cursor with absolute(2) and fetch a Date with getDate(x). How to repeat: Table - Definition / Data: ========================== CREATE TABLE test ( i int NOT NULL, d date NOT NULL ) INSERT INTO test VALUES (1, NOW()); INSERT INTO test VALUES (2, NOW()); INSERT INTO test VALUES (3, NOW()); Java-Code - with SQLException: ============================== try { Connection conn = openConnection(); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ); ResultSet rs = stmt.executeQuery( "SELECT * FROM test" ); rs.last(); int rowCount = rs.getRow(); for (int rowIndex=1; rowIndex<=rowCount; rowIndex++) { rs.absolute(rowIndex); System.out.println ( "i="+rs.getObject("i")+", "+ "d="+rs.getObject("d") ); } } catch (SQLException exc) { System.err.println (exc); } Output: ========= i=1, d=2007-11-23 java.sql.SQLException: Bad format for DATE '2007-11-23' in column 2. As you can see, the first row can be read, but not the second row. Java-Code - without SQLException: ================================= try { Connection conn = openConnection(); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ); ResultSet rs = stmt.executeQuery( "SELECT * FROM test" ); while (rs.next()) { System.out.println ( "i="+rs.getObject("i")+", "+ "d="+rs.getObject("d") ); } } catch (SQLException exc) { System.err.println (exc); } Output: ========= i=1, d=2007-11-23 i=2, d=2007-11-23 i=3, d=2007-11-23 As you can see, all rows can be read. Suggested fix: The sources of mysql-connector-java-5.1.5 I contain the class com.mysql.jdbc.RowDataStatic the Method public ResultSetRow next() throws SQLException initializes the Metadata of the current ResultSetRow, before it is returned. This Method is called by absolute(1); The Method public ResultSetRow getAt(int atIndex); returns the ResultSetRow at Index atIndex without initializing the Metadata. In class com.mysql.jdbc.ResultSetRow the Method protected final java.sql.Date getDateFast( int columnIndex, byte[] dateAsBytes, int offset, int length, ConnectionImpl conn, ResultSetImpl rs ) throws SQLException; contains a catch-Clause which catches all Exceptions an throws a java.sql.SQLException with the above mentioned Errormessage. I fixed the problem by inserting the initialization-code in the method public ResultSetRow getAt(int atIndex); Another solution (without modifying the sources) could be to iterate over the ResultSet (by calling next()) before calling absolute(x)