Reading null values from ResultSet

Question

There is the following table that may contain empty values for STA, ETA or ATA:

CREATE TABLE `flightschedule` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `flightNum_arr` varchar(40) DEFAULT NULL,
  `from_ICAO` varchar(20) DEFAULT NULL,
  `STA` datetime DEFAULT NULL,
  `ETA` datetime DEFAULT NULL,
  `ATA` datetime DEFAULT NULL,
  `pk_arr` varchar(10) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5611 DEFAULT CHARSET=latin1;

The query is:

SELECT flightNum_arr,STA,ETA,ATA,airlineICAO,aircraftType,pk_arr FROM flightschedule";

When I try to read data from this table in my Java code, the following error occurs:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 2 to TIMESTAMP.

It occurs at line record[i] = rs.getString(i + 1);

I introduced the following checking, but it does not seem to be working:

if(record[i] == null)
{
   record[i]= "";
}

The code:

public void setQuery(String query) {
        cache = new Vector();
        try {
          // Execute the query and store the result set and its metadata
          Connection con = getConnection();
          Statement statement = con.createStatement();
          ResultSet rs = statement.executeQuery(query);
          ResultSetMetaData meta = rs.getMetaData();
          colCount = meta.getColumnCount();
          // Rebuild the headers array with the new column names
          headers = new String[colCount];
          for (int h = 1; h <= colCount; h++) {
            headers[h - 1] = meta.getColumnName(h);
          }
          while (rs.next()) {
            String[] record = new String[colCount];
            for (int i = 0; i < colCount; i++) {
              record[i] = rs.getString(i + 1);
              if(record[i] == null)
              {
                  record[i]= "";
              }
            }
            cache.addElement(record);
          }
          fireTableChanged(null);         
          rs.close();
          if (con.getAutoCommit() != false) {
            con.close();
          }         
        } catch (Exception e) {
          cache = new Vector(); // blank it out and keep going.
          e.printStackTrace();
        }
      }

Please provide the entire stack trace. At what line of code in the above does the exception occur? Does your query contain a `WHERE` clause that uses one of the timestamp columns? — John B, Jun 06 '13 at 10:43

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

I believe the issue is that you need to configure the connection to convert a zero datetime to null per the following post:

handling DATETIME values 0000-00-00 00:00:00 in JDBC

edited May 23 '17 at 12:28

Community

1
1

answered Jun 06 '13 at 10:46

John B

32,493
6
77
98

Thanks. I introduced this jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull. Now it works like a charm!!! – Klausos Klausos Jun 06 '13 at 10:52

score 0 · Answer 2 · answered Jun 06 '13 at 10:50

0

You should use rs.getTimeStamp() or rs.getDate() instead of rs.getString for DateTimeColum

answered Jun 06 '13 at 10:50

Zapateus

516
3
8
21

Reading null values from ResultSet

2 Answers2