Commons DbUtils
  1. Commons DbUtils
  2. DBUTILS-56

QueryRunner exception using Oracle: Too many parameters: expected 0, was given 1 Query: ...

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2
    • Fix Version/s: 1.3
    • Labels:
      None
    • Environment:

      JDK 6
      OJDBC14.JAR
      Oracle 9i

      Description

      We use an Oracle 9i environment and I have recently upgraded from dbutils-1.1-dev.jar (1.1 with a necessary Oracle fix in the previous release) to dbutils 1.2. It appears we are completely dead-in-the-water with the 1.2 release due to all QueryRunner statements blowing up at runtime.

      Example output:
      Too many parameters: expected 0, was given 1 Query: SELECT count FROM C1IMAN.IMAN_ADD WHERE IMAN_DOC=? Parameters: [0]

      Example code:
      int macId = 0;
      final QueryRunner runner = new QueryRunner();
      final Integer ii = (Integer) runner.query(
      imanConnection,
      "SELECT count "
      + "FROM C1IMAN.IMAN_ADD "
      + "WHERE MACID=?",
      new Object[]

      { new Long(macId) }

      ,
      new ResultSetHandler()
      {
      public Object handle(ResultSet rs) throws SQLException

      { rs.next(); return new Integer( rs.getInt(1) ); }

      });

      ===========================
      I didn't see a defect issued for this yet, so am doing so. There is some discussion of the problem here:
      http://www.nabble.com/-ANNOUNCEMENT--Commons-DbUtils-1.2-released-td23335162.html

      The mentioned workaround is to upgrade from ojdbc14.jar (JDK 1.4+) to ojdbc5.jar (JDK 5.0+) or ojdbc6.jar (JDK 6.0+). I have not yet confirmed if this works, still trying to acquire those later jars and they may be an Oracle 11g only thing (i.e. not compatible with Oracle 9i). I do note that dbutils claims to work with JDK 1.4 and up, so a ojdbc14.jar solution ought to be devised.

        Activity

        Jeffrey Bennett created issue -
        Hide
        yuhl added a comment -

        hi,i also got this problem.

        i use an Oracle 9i environment and JDK1.4,and got the same execption:
        Too many parameters: expected 0, was given 2

        i found the fillStatement() method in QueryRunner, remove:

        //if (pmd.getParameterCount() < params.length)

        { // throw new SQLException("Too many parameters: expected " // + pmd.getParameterCount() + ", was given " + params.length); // }


        then everything is fine.

        Show
        yuhl added a comment - hi,i also got this problem. i use an Oracle 9i environment and JDK1.4,and got the same execption: Too many parameters: expected 0, was given 2 i found the fillStatement() method in QueryRunner, remove: – //if (pmd.getParameterCount() < params.length) { // throw new SQLException("Too many parameters: expected " // + pmd.getParameterCount() + ", was given " + params.length); // } – then everything is fine.
        Hide
        Julien Aymé added a comment -

        It seems that this issue is similar to the one I encountered (as described in DBUTILS-58), in that the Oracle 9i JDBC driver has a incorrect implementation of the ParameterMetaData#getParameterCount() method : it returns 0 instead of returning the number of parameters required for the PreparedStatement.

        As yuhl stated, this should be resolved by commenting this code block :

                if (pmd.getParameterCount() < params.length) {
                    throw new SQLException("Too many parameters: expected "
                        + pmd.getParameterCount() + ", was given " + params.length);
                }
        
        Show
        Julien Aymé added a comment - It seems that this issue is similar to the one I encountered (as described in DBUTILS-58 ), in that the Oracle 9i JDBC driver has a incorrect implementation of the ParameterMetaData#getParameterCount() method : it returns 0 instead of returning the number of parameters required for the PreparedStatement. As yuhl stated, this should be resolved by commenting this code block : if (pmd.getParameterCount() < params.length) { throw new SQLException( "Too many parameters: expected " + pmd.getParameterCount() + ", was given " + params.length); }
        Hide
        Dan Fabulich added a comment -

        This isn't exactly fixed, but by fixing DBUTILS-58, you can now work around this issue by building your QueryRunner with pmdKnownBroken. I think this is appropriate, because in this case, Oracle's ParameterMetaData is clearly broken.

        Show
        Dan Fabulich added a comment - This isn't exactly fixed, but by fixing DBUTILS-58 , you can now work around this issue by building your QueryRunner with pmdKnownBroken. I think this is appropriate, because in this case, Oracle's ParameterMetaData is clearly broken.
        Dan Fabulich made changes -
        Field Original Value New Value
        Status Open [ 1 ] Closed [ 6 ]
        Fix Version/s 1.3 [ 12313966 ]
        Resolution Fixed [ 1 ]

          People

          • Assignee:
            Unassigned
            Reporter:
            Jeffrey Bennett
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development