You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Gary Gregory (JIRA)" <ji...@apache.org> on 2016/05/06 00:53:12 UTC
[jira] [Commented] (DBUTILS-128) Can't execute subquery when use
parameters
[ https://issues.apache.org/jira/browse/DBUTILS-128?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15273413#comment-15273413 ]
Gary Gregory commented on DBUTILS-128:
--------------------------------------
Can you please provide the DML for the tabkes and a unit test for working and failure use vases?
> 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
> Labels: bug, oracle, subquery
>
> 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)