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.

Answer 2

oracle 12.1.0.1.0 jdbc oracle.jdbc.driver.OracleParameterMetaDataParser suck !

on my test:

oracle.jdbc.driver.OracleParameterMetaDataParser.main(new String[]{"update test set ORDX=?,A123=?,FABCDEFG=? where A2C=?"})

==>

SQL:update test set ORDX=:1 ,A123=:2 ,FABCDEFG=:3 where A2C=:4 SqlKind:UPDATE, Parameter Count=4 Parameter SQL: SELECT OX, A23, F, AC FROM test

field start with "O" will trim 1-2 char

field start with "F" will trim all

field start with "A" will trim 1 char

on oralce 12.1.0.2.0 still have on issue: field start with "F" will trim all

http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html

Answer 3

I encounter the same problem, using DBUtils and from ojdbc6 upgrade to ojdbc7. Then I am aware of this is a bug on parameter, so I fill it by myself. As this:

update():

        Connection conn = dataSource.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql.toUpperCase());
        this.fillStatement(ps, param);
        int rs = ps.executeUpdate();
        ps.close();
        conn.close();

fillStatement():

private void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {
    int i = 1;
    for (Object o: params){
        fill(stmt, i, o);
        i ++;
    }
}

private void fill(PreparedStatement stmt, int index, Object param) throws SQLException {
    if (param == null) {
        stmt.setObject(index, null);
    } else if (param instanceof String) {
        stmt.setString(index, (String) param);
    } else if (param instanceof Boolean) {
        stmt.setBoolean(index, (Boolean) param);
    } else if (param instanceof Integer) {
        stmt.setInt(index, (Integer) param);
    } else if (param instanceof Long) {
        stmt.setLong(index, (Long) param);
    } else if (param instanceof Double) {
        stmt.setDouble(index, (Double) param);
    } else if (param instanceof Float) {
        stmt.setFloat(index, (Float) param);
    } else if (param instanceof Short) {
        stmt.setShort(index, (Short) param);
    } else if (param instanceof Clob) {
        stmt.setClob(index, (Clob) param);
    } else if (param instanceof Blob) {
        stmt.setBlob(index, (Blob) param);
    } else if (param instanceof java.sql.Timestamp) {
        stmt.setTimestamp(index, (java.sql.Timestamp) param);
    } else if (param instanceof BigDecimal) {
        stmt.setBigDecimal(index, (BigDecimal) param);
    }else if (param instanceof java.sql.Time) {
        stmt.setTime(index, (java.sql.Time) param);
    }  else if (param instanceof java.sql.Date) {
        stmt.setDate(index, (java.sql.Date) param);
    } else if (param instanceof Date) {
        stmt.setDate(index, new java.sql.Date(((Date) param).getTime()));
    } else {
        stmt.setObject(index, param);
    }
}