commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "inthink (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DBUTILS-128) Can't execute subquery when use parameters
Date Thu, 20 Apr 2017 01:24:04 GMT

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

Mime
View raw message