commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gary Gregory (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DBUTILS-128) Can't execute subquery when use parameters
Date Fri, 06 May 2016 00:53:12 GMT

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

Mime
View raw message