Apache Dbutils changing column name in update Sql

Question

I am having a strange problem with Dbutils , I am trying to run a parameterized update sql, I am supplying correct number of arguments , but dbutils is modifying the timestamp column name by changing the name of modifying it

when timestamp columnname is one alphabet

java.sql.SQLException: Wrong number of parameters: expected 4, was given 5 Query: UPDATE WEATHER_2 SET WEATHER=? , O=TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS.FF') , HUMIDITY=? , TEMP=? WHERE ID=? Parameters: [804, 2015-06-05 17:21:05.809, 16.0, 25.15, 1347927]

when timestamp columnname is normal..it will ommit the second alphabet

java.sql.SQLException: ORA-00904: "OSTIME": invalid identifier Query: UPDATE WEATHER_2 SET WEATHER=? , OBSTIME=TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS.FF') , HUMIDITY=? , TEMP=? WHERE ID=? Parameters: [804, 2015-06-05 17:27:46.139, 16.0, 25.15, 1347927]

could this be a database thing? Also this is only happening with column whose type is Date or Timestamp.

Answer 1

I had a similar issue. I think it is a bug in the Oracle JDBC 7 Driver (ojdbc7.jar). The bug could be in the PreparedStatement.getParameterMetaData method.

This method is used internally by the Apache DBUtils. So it would not be a bug of DBUtils, but a bug from Oracle JDBC driver distributed with Oracle 12c.

Same Query will probably work fine if you use the Oracle 11g ojdbc6.jar driver. It at least worked for me.

If you want to see how the Query is wrongly processed internally by the Oracle ojdbc7.jar driver, you can use the main method included in the oracle.jdbc.driver.OracleParameterMetaDataParser class. Try this:

java -classpath ojdbc7.jar oracle.jdbc.driver.OracleParameterMetaDataParser "YOUR SQL HERE"

e.g.

java -classpath ojdbc7.jar oracle.jdbc.driver.OracleParameterMetaDataParser "UPDATE PERSON SET LASTNAME=?, FIRSTNAME=? WHERE PERSONID=?"

The output is your SQL Sentence parsed and converted to a SQL Query that is used internally by the driver to identify the parameter datatypes:

SQL:UPDATE PERSON SET LASTNAME=:1 , FIRSTNAME=:2 WHERE PERSONID=:3 SqlKind:UPDATE, Parameter Count=3 Parameter SQL: SELECT LASTNAME, F, PERSONID FROM PERSON

But as you can see in the sample, the FIRSTNAME is wrongly parsed just as "F".

Using one of the Queries you put in your question, the result is that one of the parameters just disappear... so the parser says "5" params but the internal Query used to get the datatypes has indeed only "4" (HUMIDITY has gone from the SELECT).

java -classpath ojdbc7.jar oracle.jdbc.driver.OracleParameterMetaDataParser "UPDATE WEATHER_2 SET WEATHER=? , OBSTIME=TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS.FF') , HUMIDITY=? , TEMP=? WHERE ID=?"

output:

SQL:UPDATE WEATHER_2 SET WEATHER=:1 , OBSTIME=TO_TIMESTAMP(:2 ,'YYYY-MM-DD HH24:MI:SS.FF') , HUMIDITY=:3 , TEMP=:4 WHERE ID=:5
SqlKind:UPDATE, Parameter Count=5
Parameter SQL: SELECT WEATHER, OBSTIME, TEMP, ID FROM WEATHER_2

How to fixit? No idea, but as I said above, using the Oracle 11g ojdbc6.jar driver, same query works (even connecting with an Oracle 12c database...).

The behaviour is pretty random. It looks like it depends on the first letter of the column used in the UPDATE. If it begins with F and H always fails, but I do not know if there is any other condition.