"NaN" and "Infinity" values in files causes SqlException

[Nicklas Nordborg]

The string "NaN" when parsed by Java is seen as a valid numeric value and is converted to either Double.NaN or Float.NaN. Databases usually don't like NaN values:

Caused by: net.sf.basedb.core.BaseException: 'NaN' is not a valid numeric or approximate numeric value
        at net.sf.basedb.core.BasicBatcher.addToBatch(BasicBatcher.java:564)
        at net.sf.basedb.core.BasicBatcher.insert(BasicBatcher.java:315)
        at net.sf.basedb.core.RawDataBatcher.doInsert(RawDataBatcher.java:405)
        at net.sf.basedb.core.RawDataBatcher.insert(RawDataBatcher.java:349)
        at net.sf.basedb.plugins.IlluminaRawDataImporter.handleData(IlluminaRawDataImporter.java:404)
        at net.sf.basedb.plugins.AbstractFlatFileImporter.doImport(AbstractFlatFileImporter.java:605)
        ... 5 more
Caused by: java.sql.SQLException: 'NaN' is not a valid numeric or approximate numeric value
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
        at com.mysql.jdbc.PreparedStatement.setDouble(PreparedStatement.java:2682)
        at com.mysql.jdbc.PreparedStatement.setNumericObject(PreparedStatement.java:2909)
        at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3126)
        at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3032)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:391)
        at net.sf.basedb.core.BasicBatcher.addToBatch(BasicBatcher.java:553)
        ... 10 more

So, we have to convert NaN to null at the batcher level, or possible catch this at the string parsing level and use the regular error handling. It would be best to do it at the batcher level, otherwise we have to go through a lot of places with string parsing code.

This is related to ticket #520, but that only solved the problem for intensity columns in the spot batcher.

[Nicklas Nordborg]

Doing it at the batcher level turned out to a bad idea. The reason is that we have validation code checking for null values happening before we get to that part of the code. Since we want to handle null values properly at all times, the NaN->null conversion must happen at the parsing level. As it turns out, this can be fixed in the Type class.

[Nicklas Nordborg]

(In [3624]) Fixes #708: "NaN" and "Infinity" values in files causes SqlException?