I'm trying to use the setMaxResults() with a named query and it keeps generating an error. I'm using Hibernate 3.1 with MSSQL Server. Does anyone have any suggestions. Thank you for the assist.

//begin error
8425 [RMI TCP Connection(4)-10.11.12.199] INFO : could not bind value '1' to parameter: 3; Invalid parameter index 3.
(IntegerType:91)
8425 [RMI TCP Connection(4)-10.11.12.199] WARN : SQL Error: 0, SQLState: 07009 (JDBCExceptionReporter:71)
8425 [RMI TCP Connection(4)-10.11.12.199] ERROR: Invalid parameter index 3. (JDBCExceptionReporter:72)
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1655)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.cid.zeugmaruntime.dao.hibernate.ResultDaoImpl.getMoreResult(Unknown Source)
at com.cid.zeugmaruntime.result.ResultImpl.getMoreResult(Unknown Source)
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 sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Invalid parameter index 3.
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.getParameter(JtdsPreparedStatement.java:255)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(JtdsPreparedStatement.java:326)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setInt(JtdsPreparedStatement.java:495)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.setInt(NewProxyCallableStatement.java:2732)
at org.hibernate.type.IntegerType.set(IntegerType.java:41)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:83)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:65)
at org.hibernate.loader.Loader.bindNamedParameters(Loader.java:1748)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 19 more
8440 [RMI TCP Connection(4)-10.11.12.199] INFO : could not execute query (ResultDaoImpl:?)



//end error

//start code

try {
Session session = getSession();
logger.info("got a session");
Transaction tx = session.beginTransaction();
Query queryObject = session.getNamedQuery("uiSearchMoreResults");
queryObject.setInteger("NodeId", crit.getNodeId());
queryObject.setString("Filter", crit.getFilter());
queryObject.setInteger("ClassifyId", crit.getClassifyId());
queryObject.setFirstResult(0);
queryObject.setMaxResults(10);
List list = queryObject.list();
tx.commit();
HibernateUtil.closeSession();
return list;
} catch (HibernateException e) {
e.printStackTrace();
logger.info(e.getMessage());
List list = (List)new ArrayList();
return list;
}
//end code

Do you have the dialect set properly?

Could we see the query? Is is SQL or HQL?

Did an SQL statement get generated?

Curtis ...

I'm asssuming that my dialect is set correct since everything seems to work with the exception of the setMaxResults. I've attached my hibernate config. I'm using SQL since it's calling a stored procedure (named query).

//begin hibernate config

<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:jtds:sqlserver://server2/sbirdkb</property>
<property name="hibernate.connection.username">sa</property>
<property name="hibernate.connection.password">sa</property>
<property name="hibernate.show_sql">true</property>

<property name="use_outer_join">true</property>
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>

</session-factory>
</hibernate-configuration>

//end hibernate config

What happens if you don't use setFirstResult nor setMaxResults?

If I remove the setMaxResults and setFirstResult it works just fine. If I leave in just setFirstResult() it works fine as well. The error only occurs if I use the setMaxResults.

getNamedQuery is not for stored procedures.

It is for get a query stored in a mapping file (with <query> or with <sql-query> tags).

Look for it and post the code.

Also, what happens if you use SetMaxResults only?

here is the mapping file

//begin map file

<hibernate-mapping package="com.cid.runtime.result">

<class name="ResultMoreVO">
<id name="awardId" column="AwardId" type="int">
<generator class="increment"/>
</id>
<property name="title" column="Title" />
<property name="source" column="Agency"/>
<property name="topicNumber" column="TopicNumber" />
<property name="score" column="Amount" type="float" />
<property name="nodeText" column="Class" />
<property name="keywords" column="Keywords" />
<property name="companyName" column="CompanyName" />
<property name="investigatorName" column="InvestigatorName" />
<property name="abstracts" column="abstract" />
<property name="dateComplete" column="CompleteDate" />
</class>

<sql-query name="uiSearchMoreResults" callable="true">
<return class="ResultMoreVO">
<return-property name="awardId" column="AwardId" />
<return-property name="title" column="Title" />
<return-property name="source" column="Agency"/>
<return-property name="topicNumber" column="TopicNumber" />
<return-property name="score" column="Score" />
<return-property name="nodeText" column="Class" />
<return-property name="keywords" column="Keywords" />
<return-property name="companyName" column="CompanyName" />
<return-property name="investigatorName" column="InvestigatorName" />
<return-property name="abstracts" column="abstract" />
<return-property name="dateComplete" column="CompleteDate" />
</return>
{ call uiSearchMoreResults(:NodeId, :Filter, :ClassifyId) }
</sql-query>

</hibernate-mapping>

//end map file

if i use setMaxResults in any way it generates the error I posted at the beginning of the thread.

setMaxResults(int) adds a "limiting" clause to the generated SQL (i.e. for DB2 - FETCH FIRST 10 ROWS ONLY). While I've never tried, I can't see how it could be applied to a stored procedure call -- the SQL of the stored procedure is resident in the DB server and can't be modified by the caller. The only solution I can see to modify the stored procedure to allow the limiting value to be passed in and the apply then limiting within the stored procedure.

BTW: getNamedQuery should work fine for the stored procedure call.

Hope this helps
Curtis ...

I faced the same problem long ago, just update your MySQL JDBC Connector;

MySQL does not support parameters for Limit clause, but some versions of JDBC Connectors simulate it and some don't. The latest versions do the simulation and you won't have a problem with them.

---------------------------
Don't forget the credit.