JdbcMessageDispatcher failure when performing a query with null parameters

• Log Output:

  Hide

   java.sql.SQLException: Invalid column type Query: INSERT INTO FPA.ESPECIES (rest of the query here)...
  
        at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330)
  
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:399)
  
        at org.mule.providers.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:98)
  
        at com.lumina.integration.mule.providers.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:61)
  
        at org.mule.providers.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:196)

  Show

   java.sql.SQLException: Invalid column type Query: INSERT INTO FPA.ESPECIES (rest of the query here)...       at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330)       at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:399)       at org.mule.providers.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:98)       at com.lumina.integration.mule.providers.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:61)       at org.mule.providers.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:196)
• Similar Issues:

  None

I'm trying to perform an insert on an Oracle Database. The problem I have is with null parameters to be passed to the statement.

The jdbc dispatcher performs a:

int nbRows = new QueryRunner().update(con, writeStmt, paramValues);

Looking at the QueryRunner code I can see that the update method invokes the fillStatement operation:

protected void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {

if (params == null) { return; }

for (int i = 0; i < params.length; i++) {

if (params[i] != null) { stmt.setObject(i + 1, params[i]); } else { stmt.setNull(i + 1, Types.OTHER); }

}

}

When a null value is found, the stmt.setNull(i + 1, Types.OTHER); is called and the following exception is thrown:

java.sql.SQLException: Invalid column type Query: INSERT INTO FPA.ESPECIES (rest of the query here)...

at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330)

at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:399)

at org.mule.providers.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:98)

at com.lumina.integration.mule.providers.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:61)

at org.mule.providers.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:196)

Apparently the error is because of the Types.OTHER sql type. I know that this is an issue involving Jakarta dbutils package but maybe someone has already got this problem and found a workaround to this.

At the Jakarta mailing list a workaround is mentioned by subclassing the QueryRunner class. In order to do that, I'll need to subclass the JDBC provider completely (the dispatcher and factory at least). Perhaps a custom QueryRunner could be injected to the JdbcMessageDispatcher in a future version.

Regards,

Juan Manuel