ResultSet field indices off by one

Question

My SQL:

    String mainSql =
            " SELECT `subject`, `message_id`, `sender`, `date`, `type`, `order`, `body` "
            + " FROM `scan_result` sr LEFT OUTER JOIN `scan_result_body` srb ON `sr`.id = `srb`.scan_result_id "
            + " WHERE `user_id` = ? AND `message_id` = ? "
            + " ORDER BY `type`, `order` ASC ";

My code:

    try (PreparedStatement ps = connection.prepareStatement(mainSql)) {
        int idx = 1;
        ps.setInt(idx++, userId);
        ps.setString(idx++, messageId);

        try (ResultSet rs = ps.executeQuery()) {
            boolean firstRow = false;
            while (rs.next()) {
                if (!firstRow) {
                    firstRow = true;
                    builder.setSubject(rs.getString(1));
                    builder.setMessageId(rs.getString(2));
                    builder.setFrom(rs.getString(3));
                    builder.setDate(rs.getDate(4));

My error:

...whole 'body' field
</body>
</html>

' can not be represented as java.sql.Date
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:141)
    at com.mysql.jdbc.BufferRow.getDateFast(BufferRow.java:707)
    at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2210)
    at com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2172)
    at com.mchange.v2.c3p0.impl.NewProxyResultSet.getDate(NewProxyResultSet.java:3466)
    at me.unroll.bilbo.SQLRunner.getMime(SQLRunner.java:307)

When I run the SQL in MySQLWorkbench the result rows are as expected. date is the 4th column.

The first three lines for getting the subject, messageId and from (or sender) work as expected. Fourth line somehow jumps to body. That is very weird.

I'm inspecting into the ResultSet. In particular here are the fields:

Field[originalTableName=sr,columnName=subject,mysqlType=253(FIELD_TYPE_VAR_STRING)] Field[originalTableName=sr,columnName=message_id,mysqlType=253(FIELD_TYPE_VAR_STRING)] Field[originalTableName=sr,columnName=sender,mysqlType=253(FIELD_TYPE_VAR_STRING)] Field[originalTableName=sr,columnName=date,mysqlType=7(FIELD_TYPE_TIMESTAMP)] Field[originalTableName=srb,columnName=type,mysqlType=1(FIELD_TYPE_TINY)] Field[originalTableName=srb,columnName=order,mysqlType=1(FIELD_TYPE_TINY)] Field[originalTableName=srb,columnName=body,mysqlType=253(FIELD_TYPE_VAR_STRING)]

I abbreviated down to what I could ascertain is relevant (struck namespace on Field, memory address and irrelevant fields in the Field). You can clearly see all 7, in order with the 4th being the date with the FIELD_TYPE_TIMESTAMP, all as I would expect.

ResultSet.get methods are indexed from 1. This is true anyway and is further verified by the fact that I am accurately retrieving the subject and messageId, which I can tell by inspecting builder:

<1234567890.JavaMail.app@12345.abcde>
Voted Top 10 Best Magazines By Whoever

(Fields omitted but can clearly see is as expected).

Yet calling rs.getDate(4) blows everything up. Why? My next debugging step is to step my way through the implementation and see what's happening, but this is going to be a bit of work for me to set up properly, so posting on SO here.

Props on using the builder pattern. A timestamp may not be a date. try java.sql.timestamp for the timestamp field. — DwB, Dec 13 '13 at 18:30
@DwB same error - " can not be represented as java.sql.Timestamp" — djechlin, Dec 13 '13 at 18:31
try executing the query by hand and reviewing that the output of the query is what you expect. — DwB, Dec 13 '13 at 18:34
@DwB in my post: "When I run the SQL in MySQLWorkbench the result rows are as expected. date is the 4th column." — djechlin, Dec 13 '13 at 18:35
Try retrieving all columns as Strings to see the actual results. — DwB, Dec 13 '13 at 18:39
@DwB the error message is dumping the result of the `body` column when I retrieve the date column, so I am seeing the actual result. — djechlin, Dec 13 '13 at 19:32
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/43147/discussion-between-djechlin-and-dwb) — djechlin, Dec 13 '13 at 19:33

score 3 · Answer 1 · edited May 23 '17 at 10:25

This is a MySQL bug we've had before where the date 0000-00-00 breaks everything. One patch is to convert the date 0000-00-00 to NULL before reading. This is well-known and can be fixed via driver or connection properties. I'm positive there's a more standard way to do this, but this does the trick anyway:

String url = env.database().url;
char separator = url.contains("?") ? '&' : '?';
url += separator + "zeroDateTimeBehavior=convertToNull";

cpds.setJdbcUrl(url);

See:

https://stackoverflow.com/a/1180131/1339987

https://stackoverflow.com/a/2524493/1339987

ResultSet field indices off by one

1 Answers1