Sunday, July 5, 2009

The mystery of setNull unleashed!


I was recently developing a data migration module to transfer data from Database A to Database B using an intermediate Java stub. Most of the data in the source database had null values in them. The most logical option was to use
preparedStatement.setObject
(int parameterIndex, Object x)

which should ideally take care of any object reference that comes its way even a 'null'. But this didnt seem to work. The next option was to use
preparedStatement.setNull(int parameterIndex, int sqlType)

This would need an if-else block which is very costly for large number of tupples.
eg :
if(value == null) {
preparedStatement.setNull(index, OracleTypes.VARCHAR);
}else {
preparedStatement.setObject(index, value);
}

After many workarounds , i finally concluded that the version of ojdbc14.jar used on the server was incorrect .It was pointing to 9i version. After replacing the ojdbc14.jar , the application started working smoothly with setObject() function. After decompling some Oracle packages , the implementation of setObject on 9i seemed to be flawed. But thankfully it has been corrected in 10g version. This problem sure took up some bandwidth , but worth a learning!




No comments: