Details
-
Bug
-
Status: Resolved
-
Critical
-
Resolution: Not A Problem
-
1.4, 1.5
-
None
-
None
-
Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
Description
I have some sql with one parameter (typ). Using prepared statement works good, but using the same sql and DBUtils throw exception. When i remove parameter, DBUtils works good too. JUnit test below.
TestDB.java
package com.test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.log4j.Logger; import org.junit.Test; public class TestDB { Logger log = Logger.getLogger(TestDB.class); @Test public void dbutils() throws SQLException { QueryRunner q = new QueryRunner(MyDataSource.getInstance()); List<Import2> l = q.query("select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) a where row > 10", new ResultSetHandler<List<Import2>>(){ @Override public List<Import2> handle(ResultSet rs) throws SQLException { List<Import2> l = new ArrayList<Import2>(); while(rs.next()) { Import2 i = new Import2(); i.setTyp(rs.getString("typ")); i.setId(rs.getLong("id")); l.add(i); } return l; } }, new Object[]{"TYPE1"}); log.info(l); } @Test public void jdbc() throws SQLException { Connection c = MyDataSource.getInstance().getConnection(); PreparedStatement pst = c.prepareStatement("select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) a where row > 10"); pst.setString(1, "TYPE1"); ResultSet rs = pst.executeQuery(); List<Import2> l = new ArrayList<Import2>(); while(rs.next()) { Import2 i = new Import2(); i.setTyp(rs.getString("typ")); i.setId(rs.getLong("id")); l.add(i); } log.info(l); } class Import2 { String typ; Long id; public String getTyp() { return typ; } public void setTyp(String typ) { this.typ = typ; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } } }
Exception:
java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'. Query: select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) a where row > 10 Parameters: [TYPE1]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:363)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:350)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:288)
at com.test.TestDB.dbutils(TestDB.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Test Model
CREATE TABLE import2 ( id integer IDENTITY NOT NULL, typ nvarchar(20), );