commons-issues mailing list archives

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

    [ https://issues.apache.org/jira/browse/DBUTILS-128?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15273563#comment-15273563
] 

inthink commented on DBUTILS-128:
---------------------------------

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

Mime
View raw message