Announcement Announcement Module
Collapse
No announcement yet.
New bug in the spring jdbc Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • New bug in the spring jdbc

    String in_clause = "(120,'JOHN'),(121,'SMITH')";

    String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
    + "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
    + "DEPTNO " + "FROM EMP "
    + "WHERE (empno,ename) not in (?) "
    + "ORDER BY 2";

    errors:

    08:08:26,233 WARN - [support.SQLErrorCodeSQLExceptionTranslator.transla te] Unab
    le to translate SQLException with errorCode '920', will now try the fallback tra
    nslator
    08:08:26,233 INFO - [support.SQLStateSQLExceptionTranslator.translate] Translat
    ing SQLException with SQLState '42000' and errorCode '920' and message [ORA-0092
    0: invalid relational operator
    ]; SQL was [SELECT EMPNO,ENAME,JOB,MGR,to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE,
    SAL,COMM,DEPTNO FROM EMP WHERE (empno,ename) not in (?) ORDER BY 2] for task [exe
    cuting PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$Sim
    plePreparedStatementCreator@25b72a]]
    08:08:26,233 INFO - [interceptor.TransactionInterceptor.onThrowable] Invoking r
    ollback for transaction on method 'dispEmp' in class [edu.umich.umms.springemp.s
    ervice.EmpService] due to throwable [org.springframework.jdbc.BadSqlGrammarExcep
    tion: Bad SQL grammar [SELECT EMPNO,ENAME,JOB,MGR,to_char(HIREDATE, 'MM/DD/YYYY'
    ) HIREDATE,SAL,COMM,DEPTNO FROM EMP WHERE (empno,ename) not in (?) ORDER BY 2] in
    task 'executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTe
    mplate$SimplePreparedStatementCreator@25b72a]'; nested exception is java.sql.SQL
    Exception: ORA-00920: invalid relational operator
    ]
    08:08:26,233 INFO - [datasource.DataSourceTransactionManager.rollback] Initiati
    ng transaction rollback
    org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [SELECT EMPNO,E
    NAME,JOB,MGR,to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE,SAL,COMM,DEPTNO FROM EMP W
    HERE (empno,ename) not in (?) ORDER BY 2] in task 'executing PreparedStatementCal
    lback [org.springframework.jdbc.core.JdbcTemplate$SimpleP reparedStatementCreator
    @25b72a]'; nested exception is java.sql.SQLException: ORA-00920: invalid relatio
    nal operator

    ----------------------------------------------------------------------------

    But, If i change the sql to the following, it works.

    String in_clause = "(120,'JOHN'),(121,'SMITH')";

    String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
    + "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
    + "DEPTNO " + "FROM EMP "
    + "WHERE (empno,ename) not in ("+in_clause+") "
    + "ORDER BY 2";

    Is this a bug in the spring jdbc?

    -Henry

  • #2
    No, it is not a bug in either Spring or JDBC. This is a limitation of JDBC. You can't pass in a variable list of values using a placeholder. It is just not supported.

    You will have to either add it to the sql string, like you did, or use a stored procedure where you can pass in an array.

    Comment


    • #3
      list of values

      But, this one works:

      String in_clause = "'120','121'";

      String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
      + "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
      + "DEPTNO " + "FROM EMP "
      + "WHERE ENAME not in (?) "
      + "ORDER BY 2";

      Henry

      ----------------------------------------------------------------------------------

      Originally posted by trisberg
      No, it is not a bug in either Spring or JDBC. This is a limitation of JDBC. You can't pass in a variable list of values using a placeholder. It is just not supported.

      You will have to either add it to the sql string, like you did, or use a stored procedure where you can pa?ss in an array.

      Comment


      • #4
        new bug in the spring jdbc?

        But, this one works:

        String in_clause = "'120','121'";

        String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
        + "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
        + "DEPTNO " + "FROM EMP "
        + "WHERE ENAME not in (?) "
        + "ORDER BY 2";

        and this one doesn't:

        String in_clause = "120,121";

        String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
        + "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
        + "DEPTNO " + "FROM EMP "
        + "WHERE ENAME not in (?) "
        + "ORDER BY 2";

        Henry

        Comment


        • #5
          Henry,

          You might find certain combinations that provide a workaround with a specific jdbc driver. However, it is still not the right way to do it

          Comment


          • #6
            Henry,

            I've moved this topic to the Data Access forum, here.

            Best regards,
            Tom.
            Last edited by Rod Johnson; Jan 18th, 2006, 10:41 AM.

            Comment

            Working...
            X