Hello,

I've just migrated to hibernate 3 and so far it's working out great. Our application runs on either SQL Server or DB2.

I moved to hibernate3 and tested using SQL Server which worked out really well. Then I tried DB2. I'm getting the error "executeQuery method cannot be used for update." The query is a simple select. So I'm a bit confused.

My initial thought is that it's trying to make the resultSet updatable or scrollable or something that's implying an update might occur.

I've tried setting:
hibernate.jdbc.use_scrollable_resultset false
but it's no use.

What else might I look into?

Thanks
Carl

Hibernate version:
3.0

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.adminserver.dcl">
<class name="SecurityDcl" table="AsSecurity" lazy="false">
<cache usage="transactional"/>
<id
column="ClientGUID"
name="clientGuid"
type="string"
>
<generator class="assigned" />
</id>
<property
column="SecurityRoleGUID"
length="36"
name="securityRoleGuid"
not-null="true"
type="string"
/>
<property
column="ClientNumber"
length="50"
name="clientNumber"
not-null="true"
type="string"
/>
<property
column="PersonalID"
length="10"
name="personalId"
not-null="true"
type="string"
/>
<many-to-one
name="securityRole"
class="SecurityRoleDcl"
cascade="none"
outer-join="auto"
column="SecurityRoleGUID"
insert="false"
update="false"
/>
<one-to-one
name="client"
class="ClientDcl"
cascade="none"
outer-join="auto"
constrained="false"
/>
</class>
<query name="findSecurityForLogin">
from SecurityDcl as security
where security.clientNumber = :clientNumber
and security.personalId = :personalId
</query>
<query name="findSecurityByClientGuid">
from SecurityDcl as security
where security.clientGuid = :clientGuid
</query>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

SecurityDcl data = null;
try {
Session session = HibernateSessionUtl.currentSession();
Query query = session.getNamedQuery( "findSecurityForLogin" );
query.setString( "clientNumber", clientNumber );
query.setString( "personalId", personalId );
data = ( SecurityDcl )query.uniqueResult();
}
catch( HibernateException exception ) {
throw new ASExceptionUtl( "Call to locate SecurityDcl failed", exception );
}
finally {
HibernateSessionUtl.closeSession();
}

Full stack trace of any exception that occurs:

2005-04-14 10:23:43,640 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] compile() : The query is already compiled, skipping...
2005-04-14 10:23:43,641 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2005-04-14 10:23:43,641 DEBUG [org.hibernate.jdbc.AbstractBatcher] opening JDBC connection
2005-04-14 10:23:43,641 DEBUG [org.hibernate.jdbc.JDBCContext] successfully registered Synchronization
2005-04-14 10:23:43,641 DEBUG [org.hibernate.SQL] /*named query findSecurityForLogin*/ select securitydc0_.ClientGUID as ClientGUID, securitydc0_.SecurityRoleGUID as Security2_1_, securitydc0_.ClientNumber as ClientNu3_1_, securitydc0_.PersonalID as PersonalID1_ from AsSecurity securitydc0_ where (securitydc0_.ClientNumber=? and securitydc0_.PersonalID=?)
2005-04-14 10:23:43,641 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
2005-04-14 10:23:43,642 DEBUG [org.hibernate.loader.hql.QueryLoader] bindNamedParameters() carll -> clientNumber [1]
2005-04-14 10:23:43,643 DEBUG [org.hibernate.type.StringType] binding 'carll' to parameter: 1
2005-04-14 10:23:43,643 DEBUG [org.hibernate.loader.hql.QueryLoader] bindNamedParameters() carll -> personalId [2]
2005-04-14 10:23:43,643 DEBUG [org.hibernate.type.StringType] binding 'carll' to parameter: 2
2005-04-14 10:23:43,643 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2005-04-14 10:23:43,643 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement
2005-04-14 10:23:43,643 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select securitydc0_.ClientGUID as ClientGUID, securitydc0_.SecurityRoleGUID as Security2_1_, securitydc0_.ClientNumber as ClientNu3_1_, securitydc0_.PersonalID as PersonalID1_ from AsSecurity securitydc0_ where (securitydc0_.ClientNumber=? and securitydc0_.PersonalID=?)]
com.ibm.db2.jcc.b.SqlException: executeQuery method cannot be used for update.
at com.ibm.db2.jcc.b.zc.a(zc.java:2387)
at com.ibm.db2.jcc.b.ad.d(ad.java:1929)
at com.ibm.db2.jcc.b.ad.U(ad.java:489)
at com.ibm.db2.jcc.b.ad.executeQuery(ad.java:472)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1197)
at org.hibernate.loader.Loader.doQuery(Loader.java:366)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:206)
at org.hibernate.loader.Loader.doList(Loader.java:1515)
at org.hibernate.loader.Loader.list(Loader.java:1498)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:266)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:788)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:588)
at com.adminserver.dal.SecurityFacadeEJB.findForLogin(SecurityFacadeEJB.java:48)

Name and version of the database you are using:
DB2 8.1.7

I found the problem. I've got the generated SQL comments turned on. These comments are inserted in the SQL before the SELECT. The db2 driver must just check that the first word is select or die. Pretty lame but generated SQL comments do not work on DB2.