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:
      MULE-3238JdbcConnector & JdbcMessageDispatcher are leaving connections open
      MULE-6525Unnecessary connector query parameter is sent
      MULE-619Query parameter execution in JDBC provider
      MULE-7449Add a way to define parameter types in query templates
      MULE-7440New database: query template's parameter types are not resolved
      MULE-8043Query parameters are incorrectly processed when defined in different order than in the query text
      MULE-8101HTTP requester not sending query parameters when processing a redirect under HTTPS
      MULE-1180Getting parameters from message properties
      MULE-8204ordering for stored procedure parameters causes errors
      MULE-8437Query parameters missed, if the first call you don't pass it

      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:

                Development