commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mario Vega (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DBUTILS-102) com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'
Date Sat, 06 Oct 2012 21:49:02 GMT

     [ 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

Mime
View raw message