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/05/06 03:03:12 UTC
[jira] [Comment Edited] (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=15273563#comment-15273563 ]
inthink edited comment on DBUTILS-128 at 5/6/16 3:02 AM:
---------------------------------------------------------
*After my test, an error occurs when using ojdbc7.jar*
h2. Environment:
- ORACLE12C
- ojdbc7.jar
h2. SQL Script:
{code:title=SQL.sql|borderStyle=solid}
CREATE TABLE DEPT2
(
DEPTNO NUMBER(2) NOT NULL PRIMARY KEY,
DNAME VARCHAR2(14 BYTE) NULL ,
LOC VARCHAR2(13 BYTE) NULL
);
INSERT INTO DEPT2 VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT2 VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT2 VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT2 VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP2
(
EMPNO NUMBER(4,0) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
);
INSERT INTO EMP2 VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('19801218000000', 'YYYYMMDDHH24MISS'), 1200, NULL, 20);
INSERT INTO EMP2 VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('19810220000000', 'YYYYMMDDHH24MISS'), 1900, 300, 30);
INSERT INTO EMP2 VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('19810222000000', 'YYYYMMDDHH24MISS'), 1550, 500, 30);
INSERT INTO EMP2 VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('19810402000000', 'YYYYMMDDHH24MISS'), 3275, NULL, 20);
INSERT INTO EMP2 VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('19810928000000', 'YYYYMMDDHH24MISS'), 1550, 1400, 30);
INSERT INTO EMP2 VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('19810501000000', 'YYYYMMDDHH24MISS'), 3150, NULL, 30);
INSERT INTO EMP2 VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('19810609000000', 'YYYYMMDDHH24MISS'), 3150, NULL, 10);
INSERT INTO EMP2 VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('19870419000000', 'YYYYMMDDHH24MISS'), 3300, NULL, 20);
INSERT INTO EMP2 VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('19811117000000', 'YYYYMMDDHH24MISS'), 5700, NULL, 10);
INSERT INTO EMP2 VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('19810908000000', 'YYYYMMDDHH24MISS'), 1800, 0, 30);
INSERT INTO EMP2 VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('19870523000000', 'YYYYMMDDHH24MISS'), 1400, NULL, 20);
INSERT INTO EMP2 VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('19811203000000', 'YYYYMMDDHH24MISS'), 1250, NULL, 30);
INSERT INTO EMP2 VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('19811203000000', 'YYYYMMDDHH24MISS'), 3300, NULL, 20);
INSERT INTO EMP2 VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('19820123000000', 'YYYYMMDDHH24MISS'), 2000, NULL, 10);
commit;
{code}
h2. Java JUnit Test Case
{code:title=Bar.java|borderStyle=solid}
import static org.junit.Assert.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;
import com.cpy.ems.entity.Emp;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBUtilsSubqueryTest {
// C3P0
ComboPooledDataSource ds = new ComboPooledDataSource();
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=?";
int empno = 7902;
@Test
public void testJDBC() throws SQLException {
// Use JDBC, It's correct!
Connection con=ds.getConnection();
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setObject(1, empno);
pstmt.executeQuery();
con.close();
}
@Test // ERRORS
public void testDBUtils() throws SQLException {
// Use DBUtils, when use params it's incorrect
// Exception: java.sql.SQLException: ORA-00904: "EMP": invalid identifier
QueryRunner qr=new QueryRunner(ds);
//qr.query(sql2, rsh, params)
qr.query(sql, new BeanHandler<Emp>(Emp.class),empno);
}
@Test
public void testDBUtils2() throws SQLException {
// Use JDBC, do not use params, It's correct!
String sql2="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=7902";
QueryRunner qr=new QueryRunner(ds);
//qr.query(sql2, rsh)
qr.query(sql2, new BeanHandler<Emp>(Emp.class));
}
}
{code}
was (Author: inthink):
** After my test, an error occurs when using ojdbc7.jar **
## Environment:
- ORACLE12C
- ojdbc7.jar
## SQL Script:
````SQL
CREATE TABLE DEPT2
(
DEPTNO NUMBER(2) NOT NULL PRIMARY KEY,
DNAME VARCHAR2(14 BYTE) NULL ,
LOC VARCHAR2(13 BYTE) NULL
);
INSERT INTO DEPT2 VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT2 VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT2 VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT2 VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP2
(
EMPNO NUMBER(4,0) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
);
INSERT INTO EMP2 VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('19801218000000', 'YYYYMMDDHH24MISS'), 1200, NULL, 20);
INSERT INTO EMP2 VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('19810220000000', 'YYYYMMDDHH24MISS'), 1900, 300, 30);
INSERT INTO EMP2 VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('19810222000000', 'YYYYMMDDHH24MISS'), 1550, 500, 30);
INSERT INTO EMP2 VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('19810402000000', 'YYYYMMDDHH24MISS'), 3275, NULL, 20);
INSERT INTO EMP2 VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('19810928000000', 'YYYYMMDDHH24MISS'), 1550, 1400, 30);
INSERT INTO EMP2 VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('19810501000000', 'YYYYMMDDHH24MISS'), 3150, NULL, 30);
INSERT INTO EMP2 VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('19810609000000', 'YYYYMMDDHH24MISS'), 3150, NULL, 10);
INSERT INTO EMP2 VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('19870419000000', 'YYYYMMDDHH24MISS'), 3300, NULL, 20);
INSERT INTO EMP2 VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('19811117000000', 'YYYYMMDDHH24MISS'), 5700, NULL, 10);
INSERT INTO EMP2 VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('19810908000000', 'YYYYMMDDHH24MISS'), 1800, 0, 30);
INSERT INTO EMP2 VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('19870523000000', 'YYYYMMDDHH24MISS'), 1400, NULL, 20);
INSERT INTO EMP2 VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('19811203000000', 'YYYYMMDDHH24MISS'), 1250, NULL, 30);
INSERT INTO EMP2 VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('19811203000000', 'YYYYMMDDHH24MISS'), 3300, NULL, 20);
INSERT INTO EMP2 VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('19820123000000', 'YYYYMMDDHH24MISS'), 2000, NULL, 10);
commit;
```
## Java JUnit Test Case
```
import static org.junit.Assert.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;
import com.cpy.ems.entity.Emp;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBUtilsSubqueryTest {
// C3P0
ComboPooledDataSource ds = new ComboPooledDataSource();
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=?";
int empno = 7902;
@Test
public void testJDBC() throws SQLException {
// Use JDBC, It's correct!
Connection con=ds.getConnection();
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setObject(1, empno);
pstmt.executeQuery();
con.close();
}
@Test // ERRORS
public void testDBUtils() throws SQLException {
// Use DBUtils, when use params it's incorrect
// Exception: java.sql.SQLException: ORA-00904: "EMP": invalid identifier
QueryRunner qr=new QueryRunner(ds);
//qr.query(sql2, rsh, params)
qr.query(sql, new BeanHandler<Emp>(Emp.class),empno);
}
@Test
public void testDBUtils2() throws SQLException {
// Use JDBC, do not use params, It's correct!
String sql2="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=7902";
QueryRunner qr=new QueryRunner(ds);
//qr.query(sql2, rsh)
qr.query(sql2, new BeanHandler<Emp>(Emp.class));
}
}
```
> 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)