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 2017/04/20 01:24:04 UTC
[jira] [Updated] (DBUTILS-128) Can't execute subquery when use
parameters
[ https://issues.apache.org/jira/browse/DBUTILS-128?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
inthink updated DBUTILS-128:
----------------------------
Priority: Blocker (was: Major)
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.
was:
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.
> 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
> Priority: Blocker
> 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)
> ```
> driver: ojdbc7.jar
> When execute on database or use pure JDBC, It's correct.
> Please fixed.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)