You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "inthink (JIRA)" <ji...@apache.org> on 2016/04/27 11:43:13 UTC
[jira] [Created] (DBUTILS-128) Can't execute subquery when use
parameters
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)