Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement + MS SQL query

 
Swapna James
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am getting an error when I am trying to take the top 100 rows from a table using PreparedStatement on an MS SQL database.

The code is as follows.



I am getting this error



I googled and didn't get a proper solution. Can anybody help?

Thanks in advance,

Swapna.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just a thought, but have you tried executing the query in query analyzer to make sure the syntax is correct for sql server? Also, have you tried executing with a hard-coded 100 in the query instead of as a prepared statement replacement variable.
 
Swapna James
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Scott,

I have tried the same query using

String query="SELECT TOP 100 EMP_NO,EMP_NAME FROM EMP_TABLE";

and

String query="SELECT TOP "+rowFactor+" EMP_NO,EMP_NAME FROM EMP_TABLE";

Both works fine. Only when I use '?' this error comes.

Any idea why? Is it related to the driver that I am using?

Regards,

Swapna.
 
Brian Mozhdehi
Ranch Hand
Posts: 81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is definately because of the driver, the MSSQL driver does not support every statement that the query analyzer does. Matter of fact, most of the drivers do not support all of the statements that can be run using the native tools, for example a DESC <tablename> statement that runs fine in SQL PLus (Oracle) does not run using Oracle's JDBC driver. MSSQL in particular struggles with some stored procedure calls using CallableStatement (getting output parameters) and PreparedStatement.

You can try altering your SQL using ORDER BY or some other tactic to get the rows you want and use setMaxRows() method on your statement object to limit the rows returned. Or you can use a different JDBC driver, such as the open source one found here http://jtds.sourceforge.net/. Or you can use the JDBC-ODBC bridge, although thats a last resort, since under heavy load that will occasionally terminate your VM when it encounters native errors (which it will under heavy load) since it relies on native code and is generally prone to exceptions (which teh JVM wont catch, they are treated as throwables).

Hope that helps.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic