[Manish Namdeo]

2006-07-11 16:20:08,571 WARN [org.hibernate.util.JDBCExceptionReporter] – SQL Error: -99999, SQLState: null
2006-07-11 16:20:08,571 ERROR [org.hibernate.util.JDBCExceptionReporter] – executeQuery method cannot be used for update.
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select prospect0_.PROSPECT_ID as PROSPECT1_ from PROSPECT prospect0_]; SQL state [null]; error code [-99999]; executeQuery method cannot be used for update.; nested exception is com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
at com.ibm.db2.jcc.a.rf.a(rf.java:2534)
at com.ibm.db2.jcc.a.sf.d(sf.java:1962)
at com.ibm.db2.jcc.a.sf.X(sf.java:508)
at com.ibm.db2.jcc.a.sf.executeQuery(sf.java:491)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.springframework.orm.hibernate3.HibernateTemplate$31.doInHibernate(HibernateTemplate.java:853)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:844)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:836)
at com.er4d.econtact.data.dao.ProspectDAO.retrieveAll(ProspectDAO.java:25)
at com.er4d.econtact.business.ProspectManager.retrieveAll(ProspectManager.java:24)
at com.er4d.econtact.business.test.ProspectTest.main(ProspectTest.java:25)
Exception in thread “main”

Query is:
List l = getHibernateTemplate().find(“FROM Prospect”); // Query in stack trace above is modified for brevity.

My hibernate properties are set as:

hibernate.show_sql=true
hibernate.use_sql_comments=true
hibernate.cache.use_query_cache=true
hibernate.cglib.use_reflection_optimizer=false
hibernate.use_outer_join=true
hibernate.dialect=org.hibernate.dialect.DB2Dialect
hibernate.jdbc.use_scrollable_resultset=false

Data source is created as:

<bean id=”jdbcDataSource”
class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
<property name=”driverClassName”
value=”${jdbc.driverClassName}” />
<property name=”url” value=”${jdbc.url.type2}” />
<property name=”username” value=”${jdbc.username}” />
<property name=”password” value=”${jdbc.password}” />
</bean>

Why is Hibernate trying to use executeQuery (in place of execute)? Do I need to set additional properties fof Hibernate?

[Haris Peco]

mnamdeo ,

I think that you can’t change this with properteis – hibernate use executeQuery, because it’s query (select … from)
Can you send your hibernate mappings, pojo and schema script for table, please ? I can try reproduce your case.Have you created mappings and pojo with MyEclipse and can you browse database in MyEclipse’s db browser?
What is your database server, jdbc, eclipse and MyEclipse versions ?

a lot questions 🙂 (sorry)

Best regards

[Manish Namdeo]

Yes – mapping and POJO are created with MyEclipse. I can browse data in db explorer. DB2 Type 2 driver works; Type 4 barfs (in a Hibernate code test, Hibernate complains of No Suitable Driver). With Type 4, MyEclipse cannot reverse engineer.

OS Windows
DB2 – V8.2
JDBC- Bundled with 8.2 ( db2jcc.jar 1164KB, db2jcc_license_cu.jar 1KB)
Eclipse – 3.2
MyEclipse – 5.0M2

Offending method goes like this:

public List retrieveAll() throws DataAccessException{
log.info(“Retrieving Prospect.*”);
// List l = getHibernateTemplate().find(“FROM MyProspect WHERE contact_email_address like ?”, “%EMAIL.COM”);
List l = getHibernateTemplate().find(“FROM MyProspect”);
log.info(“Returning ” + l.size() + ” records.”);
return l;
}

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select prospect0_.PROSPECT_ID as PROSPECT1_, prospect0_.USER_ID as USER2_26_, prospect0_.PROSPECT_KEY as PROSPECT3_26_, prospect0_.CREATE_DATE as CREATE4_26_, prospect0_.SOURCE as SOURCE26_, prospect0_.SUPRESSION_CODE as SUPRESSION6_26_, prospect0_.CONTACT_FIRST_NAME as CONTACT7_26_, prospect0_.CONTACT_MIDDLE_INI as CONTACT8_26_, prospect0_.CONTACT_LAST_NAME as CONTACT9_26_, prospect0_.CONTACT_SUFFIX as CONTACT10_26_, prospect0_.CONTACT_TITLE as CONTACT11_26_, prospect0_.CONTACT_PHONE_1 as CONTACT12_26_, prospect0_.CONTACT_EXTENSION as CONTACT13_26_, prospect0_.CONTACT_PHONE_2 as CONTACT14_26_, prospect0_.CONTACT_JOB_FUNCTION_1 as CONTACT15_26_, prospect0_.CONTACT_JOB_FUNCTION_2 as CONTACT16_26_, prospect0_.CONTACT_ADDRESS_DROP as CONTACT17_26_, prospect0_.CONTACT_COMMUN as CONTACT18_26_, prospect0_.CONTACT_LANG_PREF as CONTACT19_26_, prospect0_.CONTACT_ADDRESS_1 as CONTACT20_26_, prospect0_.CONTACT_ADDRESS_2 as CONTACT21_26_, prospect0_.CONTACT_CITY as CONTACT22_26_, prospect0_.CONTACT_STATE_CODE as CONTACT23_26_, prospect0_.CONTACT_ZIP_CODE as CONTACT24_26_, prospect0_.CONTACT_COUNTY_CODE as CONTACT25_26_, prospect0_.CONTACT_COUNTRY_CODE as CONTACT26_26_, prospect0_.CONTACT_EMAIL_ADDRESS as CONTACT27_26_, prospect0_.CONTACT_EMAIL_PERMISSION as CONTACT28_26_, prospect0_.UPDATE_DATE as UPDATE29_26_, prospect0_.SALUTATION as SALUTATION26_, prospect0_.CONTACT_ADDRESS_3 as CONTACT31_26_, prospect0_.CONTACT_NAME as CONTACT32_26_, prospect0_.VALUE_CODE as VALUE33_26_, prospect0_.VALUE_DESCRIPTION as VALUE34_26_, prospect0_.JOB_FUNCTION_DESCRIPTION as JOB35_26_, prospect0_.SBCORDNO as SBCORDNO26_, prospect0_.EVENING_PHONE as EVENING37_26_, prospect0_.MSM_SOURCE_CODE as MSM38_26_, prospect0_.CONTACT_TIER as CONTACT39_26_, prospect0_.NEWSLETTER_VERSION as NEWSLETTER40_26_, prospect0_.EMAIL_FORMAT as EMAIL41_26_, prospect0_.CONTACT_COUNTRY as CONTACT42_26_, prospect0_.CONTACT_FULL_NAME as CONTACT43_26_, prospect0_.CONTACT_EMAIL_ADDRESS_2 as CONTACT44_26_, prospect0_.UPDATE_SOURCE as UPDATE45_26_, prospect0_.QA_IND as QA46_26_, prospect0_.CONTACT_MID_NAME as CONTACT47_26_, prospect0_.CONTACT_NICKNAME as CONTACT48_26_, prospect0_.CONTACT_KEY as CONTACT49_26_, prospect0_.ADDRESS_KEY as ADDRESS50_26_, prospect0_.ACCOUNT_KEY as ACCOUNT51_26_, prospect0_.ADDRESS_TYPE as ADDRESS52_26_ from PROSPECT prospect0_ where prospect_id=?]; SQL state [null]; error code [-99999]; executeQuery method cannot be used for update.; nested exception is com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
at com.ibm.db2.jcc.a.rf.a(rf.java:2534)
at com.ibm.db2.jcc.a.sf.d(sf.java:1962)
at com.ibm.db2.jcc.a.sf.X(sf.java:508)
at com.ibm.db2.jcc.a.sf.executeQuery(sf.java:491)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)

[Haris Peco]

mnamdeo,

I will check your code soon, but for now, please send me data about type 4 driver
Type 4 works better usually (except this you can work in network).
‘no suitable driver’ mean that your url is not good (or you driver class name or jar, but i supposet that you do this correct).Is it work in db dbrowser ?

Thanks

[Manish Namdeo]

Please ignore the difference between “MyProspect” and “Prospect” in query showed above. Code calls “Prospect”, not “MyProspect”.

[Manish Namdeo]

Not sure what you mean by details of driver but here is what I see: (I can connect with both Type 2 and Type 4 URL however Hibernate barfs on Type 4 URL).

db2jcc.jar:
Size: 1.13 MB (1,191,149 bytes)
Size on disk: 1.13 MB (1,191,936 bytes)
Manifest-Version: 1.0
Created-By: 1.3.1 (IBM Corporation)

2006-07-12 07:58:55,000 INFO [org.springframework.jdbc.datasource.DriverManagerDataSource] – Loaded JDBC driver: com.ibm.db2.jcc.DB2Driver
2006-07-12 07:58:55,062 INFO [org.hibernate.cfg.Environment] – Hibernate 3.0.5

[Haris Peco]

Please, what is url (connection profile) and driver classname (driver config)
for type 2 and type 4 drivers ?

Thanks

[Manish Namdeo]

Type 4 Connection Test:
DriverClass = com.ibm.db2.jcc.DB2Driver
Connected using url jdbc:db2://127.0.0.1:50000/DATABASEDEV
Major Version: 2
Minor Version: 3

Please note that a simple connection test works for both Type 2 and Type 4 however DB Browser does not work with Type 4 and Hibernate refuses to run with Type 4. With Type 2, it attempts to run but fails with executeQuery as described above.

[Manish Namdeo]

jdbc.driverClassName=com.ibm.db2.jcc.DB2Driver
jdbc.url.type2=jdbc:db2:DATABASEDEV
jdbc.url.type4=jdbc:db2//127.0.0.1:50000/DATABASEDEV
jdbc.username=DATABASEDEV
jdbc.password=DATABASEDEV

I have used both url’s with result as explained above.

[Manish Namdeo]

An update: I did find an error in my type 4 url. Correct url is:

jdbc.url.type4=jdbc:db2://127.0.0.1:50000/DATABASEDEV

It makes no difference however.

2006-07-12 10:03:30,946 INFO [org.hibernate.connection.ConnectionProviderFactory] – Initializing connection provider: org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider
2006-07-12 10:03:31,384 INFO [org.hibernate.cfg.SettingsFactory] – RDBMS: DB2/LINUX, version: SQL08023
2006-07-12 10:03:31,384 INFO [org.hibernate.cfg.SettingsFactory] – JDBC driver: IBM DB2 JDBC Universal Driver Architecture, version: 2.7.58

ERROR [org.hibernate.util.JDBCExceptionReporter] – executeQuery method cannot be used for update.
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select prospect0_.PROSPECT_ID as PROSPECT1 from PROSPECT prospect0_ where prospect_id=?]; SQL state [null]; error code [-99999]; executeQuery method cannot be used for update.; nested exception is com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
com.ibm.db2.jcc.a.SqlException: executeQuery method cannot be used for update.
at com.ibm.db2.jcc.a.hd.a(hd.java:2508)
at com.ibm.db2.jcc.a.id.d(id.java:1952)
at com.ibm.db2.jcc.a.id.X(id.java:505)
at com.ibm.db2.jcc.a.id.executeQuery(id.java:488)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:826)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:817)
at com.er4d.econtact.data.dao.ProspectDAO.retrieveByProspectId(ProspectDAO.java:56)
at com.er4d.econtact.business.ProspectManager.retrieveByProspectId(ProspectManager.java:34)
at com.er4d.econtact.business.test.ProspectTest.main(ProspectTest.java:21)
Exception in thread “main”

[Haris Peco]

mnamdeo,

It’s fine – now both drivers work.Can you send me schema script, please

Thanks

[Manish Namdeo]

OKAY – I figured it out. My speculation is that IBM DB2 driver does not do a good enough job of detecting if the current query is a “SELECT” i.e. Non Update type query vs. Update type query.

If I remove

hibernate.use_sql_comments=true

from hibernate.properties, everything works like a charm!

[Haris Peco]

It looks that it’s bug in hibernate.
Hibernate make comment (hql) like this
/* hql_query */ sql_query

Db2 don’t recognize comment /* */ (only single line –) and it’s problem

your workaround is great

Thank you !

[Manish Namdeo]

exactly. It took me a while to trace it through but it is finally working! thanks for your help.

[Author]

Posts