File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes PreparedStatement + MS SQL query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement + MS SQL query" Watch "PreparedStatement + MS SQL query" New topic
Author

PreparedStatement + MS SQL query

Swapna James
Ranch Hand

Joined: Jul 26, 2005
Posts: 56
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.


SCJP 1.4
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Swapna James
Ranch Hand

Joined: Jul 26, 2005
Posts: 56
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

Joined: Aug 17, 2006
Posts: 81
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement + MS SQL query