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.
String query="SELECT TOP 100 EMP_NO,EMP_NAME FROM EMP_TABLE";
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?
Joined: Aug 17, 2006
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).