You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Mario Vega (JIRA)" <ji...@apache.org> on 2012/10/06 23:47:02 UTC
[jira] [Created] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
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.
{code:java|title=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;
}
}
}
{code}
Exception:
{code:java}
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)
{code}
Test Model
{code}
CREATE TABLE import2 (
id integer IDENTITY NOT NULL,
typ nvarchar(20),
);
{code}
was:
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.
{code:java|title=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;
}
}
}
{code}
Exception:
{code:java}
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)
{code}
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Comment Edited] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13471146#comment-13471146 ]
Mario Vega edited comment on DBUTILS-102 at 10/7/12 5:26 AM:
-------------------------------------------------------------
I think, Exception applies to the first "where", but ok... let query be:
{code}select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) as x{code}
I get the same exception using DbUtils.
was (Author: mario444):
Ok, so let query be:
"select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) as x"
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Resolved] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega resolved DBUTILS-102.
--------------------------------
Resolution: Not A Problem
Yes! That's it. Thank You very much William.
Using parameter pmdKnownBroken = true do the trick.
{code}QueryRunner q = new QueryRunner(MyDataSource.getInstance(), true);{code}
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Commented] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13471146#comment-13471146 ]
Mario Vega commented on DBUTILS-102:
------------------------------------
Ok, so let query be:
"select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) as x"
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
Comment: was deleted
(was: I think, Exception applies to the first "where", but ok... let query be:
{code}select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) as x{code}
I get the same exception using DbUtils.)
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Commented] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "William R. Speirs (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13471233#comment-13471233 ]
William R. Speirs commented on DBUTILS-102:
-------------------------------------------
I think this is simply a meta-data issue. Have you tried using the following constructor: http://commons.apache.org/dbutils/apidocs/org/apache/commons/dbutils/QueryRunner.html#QueryRunner(javax.sql.DataSource, boolean)
Basically, the SQL server driver has a bug/issue with parameter metadata in some queries. If you tell DBUtils to stop using parameter metadata, then it should work without issue.
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
Description:
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)
was:
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)
> 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.4, 1.5
> 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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
Description:
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)
was:
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)
> 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.4, 1.5
> 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
[jira] [Comment Edited] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13471167#comment-13471167 ]
Mario Vega edited comment on DBUTILS-102 at 10/7/12 7:18 AM:
-------------------------------------------------------------
I've tested Spring JdbcTemplate with the same query and works good. Sample code below.
{code}
@Test
public void spring() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(DecyzjeDataSource.getInstance());
List<Import2> l = jdbcTemplate.query("select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) a where row > 10", new Object[]{"TYPE1"}, new RowMapper<Import2>(){
@Override
public Import2 mapRow(ResultSet arg0, int arg1) throws SQLException {
Import2 i = new Import2();
i.setId(arg0.getLong("id"));
i.setTyp(arg0.getString("typ"));
return i;
}
});
log.info(l);
}
{code}
was (Author: mario444):
I've testes Spring JdbcTemplate with the same query and works good. Sample code below.
{code}
@Test
public void spring() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(DecyzjeDataSource.getInstance());
List<Import2> l = jdbcTemplate.query("select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) a where row > 10", new Object[]{"TYPE1"}, new RowMapper<Import2>(){
@Override
public Import2 mapRow(ResultSet arg0, int arg1) throws SQLException {
Import2 i = new Import2();
i.setId(arg0.getLong("id"));
i.setTyp(arg0.getString("typ"));
return i;
}
});
log.info(l);
}
{code}
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
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.
{code:java|title=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;
}
}
}
{code}
Exception:
{code:java}
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)
{code}
was:
Using prepared statement works good, but using the same sql and DBUtils throw exception. junit test below.
{code:java|title=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;
}
}
}
{code}
Exception:
{code:java}
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)
{code}
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition
> Reporter: Mario Vega
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
--
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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
Description:
Using prepared statement works good, but using the same sql and DBUtils throw exception. junit test below.
{code:java|title=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;
}
}
}
{code}
Exception:
{code:java}
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)
{code}
was:
Using prepared statement works good, but using the same sql and DBUtils throw exception. junit test below.
{code: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;
}
}
}
{code}
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)
> 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.4, 1.5
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
--
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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server (was: Windows 7; SQL Server 2012 Express Edition)
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
--
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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
Description:
Using prepared statement works good, but using the same sql and DBUtils throw exception. junit test below.
{code: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;
}
}
}
{code}
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)
was:
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)
> 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.4, 1.5
> 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.
> {code: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;
> }
>
> }
> }
> {code}
> 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
[jira] [Updated] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega updated DBUTILS-102:
-------------------------------
Priority: Critical (was: Major)
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
--
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
[jira] [Commented] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13471167#comment-13471167 ]
Mario Vega commented on DBUTILS-102:
------------------------------------
I've testes Spring JdbcTemplate with the same query and works good. Sample code below.
{code}
@Test
public void spring() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(DecyzjeDataSource.getInstance());
List<Import2> l = jdbcTemplate.query("select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) a where row > 10", new Object[]{"TYPE1"}, new RowMapper<Import2>(){
@Override
public Import2 mapRow(ResultSet arg0, int arg1) throws SQLException {
Import2 i = new Import2();
i.setId(arg0.getLong("id"));
i.setTyp(arg0.getString("typ"));
return i;
}
});
log.info(l);
}
{code}
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Reopened] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Mario Vega (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mario Vega reopened DBUTILS-102:
--------------------------------
I think, Exception applies to the first "where", but ok... let query be:
{code}select * from (select *, row_number() over( order by id desc ) as row from import2 where typ = ? ) as x{code}
I get the same exception using DbUtils.
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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
[jira] [Resolved] (DBUTILS-102)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
keyword 'WHERE'
Posted by "Sebb (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DBUTILS-102?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sebb resolved DBUTILS-102.
--------------------------
Resolution: Invalid
Syntax error in test case:
") a where"
Note the spurious "a" character.
> 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.4, 1.5
> Environment: Windows 7; SQL Server 2012 Express Edition; Microsoft JDBC Driver 4.0 for SQL Server
> Reporter: Mario Vega
> Priority: Critical
>
> 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.
> {code:java|title=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;
> }
>
> }
> }
> {code}
> Exception:
> {code:java}
> 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)
> {code}
> Test Model
> {code}
> CREATE TABLE import2 (
> id integer IDENTITY NOT NULL,
> typ nvarchar(20),
> );
> {code}
--
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