commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "inthink (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DBUTILS-128) Can't execute subquery when use parameters
Date Wed, 27 Apr 2016 09:43:13 GMT
inthink created DBUTILS-128:
-------------------------------

             Summary: Can't execute subquery when use parameters
                 Key: DBUTILS-128
                 URL: https://issues.apache.org/jira/browse/DBUTILS-128
             Project: Commons DbUtils
          Issue Type: Bug
    Affects Versions: 1.5, 1.6
         Environment: 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=?

            Reporter: inthink



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)
```

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



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message