SQL Server does not allow top to be specified as a parameter, you need to inject in the query:
select top $max_users$ * from NP_USER HTH, Niels ________________________________ From: Alec Lebedev [mailto:[EMAIL PROTECTED] Sent: dinsdag 7 augustus 2007 20:10 To: [email protected] Subject: specifying offset/top parameter I know that IBatis API allows the user to specify offset and max parameters for a query, but is there a way to parameterize the number of rows returned by the query in the XML query definition? When I use the following query definition IBatis throws an exception thrown below. <select id="selectTopUsers" resultClass="java.util.List" parameterClass="java.util.Map"> select top #max_users# * from NP_USER </select> Exception in thread "main" com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in ibatis/queries/UserReport.xml. --- The error occurred while applying a parameter map. --- Check the selectTopUsers-InlineParameterMap. --- Check the statement (query failed). --- Cause: java.sql.SQLException: Incorrect syntax near '@P0'. at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery WithCallback(GeneralStatement.java:185) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery ForList(GeneralStatement.java:123) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap ExecutorDelegate.java:615) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap ExecutorDelegate.java:589) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessi onImpl.java:118) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClient Impl.java:95) at com.nextpage.server.domain.dao.IBatisReportDAO.selectTopUsers(IBatisRepo rtDAO.java:40) at com.nextpage.server.domain.dao.IBatisReportDAO.main(IBatisReportDAO.java :51) Caused by: java.sql.SQLException: Incorrect syntax near '@P0'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java :365) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.jav a:525) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:48 7) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStat ement.java:475) at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor. java:186) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQu ery(GeneralStatement.java:205) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery WithCallback(GeneralStatement.java:173) ... 7 more
