Mule
  1. Mule
  2. MULE-761

JdbcMessageDispatcher failure when performing a query with null parameters

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.3.2
    • Component/s: Transport: JDBC
    • Labels:
      None
    • Environment:

      N/A

    • 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

      Description

      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

        Issue Links

          Activity

          Hide
          Alan Cassar added a comment -

          I have tried this simple insert query with the jdbc connector:
          <property name="writeQuery" value="INSERT INTO table1 (name, place, ack) VALUES ($

          {NOW}

          , NULL, 0)"/>

          this worked fine for me without giving any exceptions.

          should this issue be closed?

          Show
          Alan Cassar added a comment - I have tried this simple insert query with the jdbc connector: <property name="writeQuery" value="INSERT INTO table1 (name, place, ack) VALUES ($ {NOW} , NULL, 0)"/> this worked fine for me without giving any exceptions. should this issue be closed?
          Hide
          Alan Cassar added a comment -

          if we look at the JdbcNonTransactionalFunctionalTestCase integration test, there are loads of NULL parameters passed to the execSqlUpdate() method which in turns calls the QueryRunner.update() as specified in the post of Juan

          Show
          Alan Cassar added a comment - if we look at the JdbcNonTransactionalFunctionalTestCase integration test, there are loads of NULL parameters passed to the execSqlUpdate() method which in turns calls the QueryRunner.update() as specified in the post of Juan
          Hide
          Alan Cassar added a comment -

          added new test case which shows that null params work well

          testcase name is: JdbcNullParamsTestCase

          Show
          Alan Cassar added a comment - added new test case which shows that null params work well testcase name is: JdbcNullParamsTestCase
          Hide
          Fabio Insaccanebbia added a comment -

          The original problem is an Oracle JDBC Driver problem...
          Jakarta DbUtils has introduced a workaround in the 1.1 version

          Show
          Fabio Insaccanebbia added a comment - The original problem is an Oracle JDBC Driver problem... Jakarta DbUtils has introduced a workaround in the 1.1 version
          Hide
          kenneth westelinck added a comment -

          I had the same problem. Another workaround is to modify your query as described here: http://kennywest.blogspot.com/2006/11/development-empty-strings-in-oracle.html

          Show
          kenneth westelinck added a comment - I had the same problem. Another workaround is to modify your query as described here: http://kennywest.blogspot.com/2006/11/development-empty-strings-in-oracle.html

            People

            • Assignee:
              Alan Cassar
              Reporter:
              Juan Manuel Sanmarco
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                4/Dec/06

                Development