Mario Vega created DBUTILS-102:
----------------------------------
Summary: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near the keyword 'WHERE'
Key: DBUTILS-102
URL: https://issues.apache.org/jira/browse/DBUTILS-102
Project: Commons DbUtils
Issue Type: Bug
Affects Versions: 1.5, 1.4
Environment: Windows 7; SQL Server 2012 Express Edition
Reporter: Mario Vega
Using prepared statement works good, but using the same sql and DBUtils throw
exception. junit test below.
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)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
