Uploaded image for project: 'Commons DbUtils'
  1. Commons DbUtils
  2. DBUTILS-128

Can't execute subquery when use parameters

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Blocker
    • Resolution: Unresolved
    • 1.5, 1.6
    • None
    • Database: Oracle

      SQL: select e.empno,e.ename,e.job,e.hiredate,e.mgr,e.sal,e.comm,d.dname,(select e2.ename from emp e2 where e2.empno=e.mgr) as mgrname from emp e,dept d where e.deptno=d.deptno and e.empno=?

    • Important

    Description

      Oracle Database:
      ```
      SQL> desc dept
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      DEPTNO NOT NULL NUMBER(2)
      DNAME VARCHAR2(14)
      LOC VARCHAR2(13)

      SQL> desc emp;
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      EMPNO NOT NULL NUMBER(4)
      ENAME VARCHAR2(10)
      JOB VARCHAR2(9)
      MGR NUMBER(4)
      HIREDATE DATE
      SAL NUMBER(7,2)
      COMM NUMBER(7,2)
      DEPTNO NUMBER(2)
      ````

      SQL
      ```
      select e.empno,e.ename,e.job,e.hiredate,e.mgr,e.sal,e.comm,d.dname,(select e2.ename from emp e2 where e2.empno=e.mgr) as mgrname from emp e,dept d where e.deptno=d.deptno and e.empno=?
      ```

      Java
      ```
      String sql="select e.empno,e.ename,e.job,e.hiredate,e.mgr,e.sal,e.comm,d.dname,(select e2.ename from emp e2 where e2.empno=e.mgr) as mgrname from emp e,dept d where e.deptno=d.deptno and e.empno=?";

      Emp e=(Emp) qr.query(sql, new BeanHandler(Emp.class),7902);
      ```

      ERROR:
      ```
      java.sql.SQLException: ORA-00904: "EMP": invalid identifier
      Query: select e.empno,e.ename,e.job,e.hiredate,e.mgr,e.sal,e.comm,d.dname,(select e2.ename from emp e2 where e2.empno=e.mgr) as mgrname from emp e,dept d where e.deptno=d.deptno and e.empno=? Parameters: [7902]
      at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)
      at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:351)
      at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:289)
      ```

      driver: ojdbc7.jar

      When execute on database or use pure JDBC, It's correct.
      Please fixed.

      Attachments

        Activity

          People

            Unassigned Unassigned
            inthink inthink
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: