Hi,

In my Hibernate application, native sql query is being used. If concurrent users are accessing it, some of the data is getting lost. If I rerun it, it's able to get all. and this is not happening all the time. Is it any thing related to cache?

regards,
deepika

are you missing entire rows or just some values of the returned objects ?

last thing is because of session scoped identity (look it up if you don't know what it is ;)

the first one is dependent on your db and connections transactional level.

Hi,

Thanks for your response.
Only some of the values of the returned objects are missing. I'm using session-per-request.

Also randomly i'm getting the following error

[9/11/06 9:54:16:507 CDT] 6da9583d SystemErr R org.hibernate.exception.GenericJDBCException: could not execute query

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)

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:1674)

at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)

at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)

at com.seec.insurance.life.countryDB.hibernate.util.SearchMethods.organizationNamesearch(SearchMethods.java:66)

at com.seec.insurance.life.partySearch.countryDB.SearchCountryDB.getGeneralSearchParties(SearchCountryDB.java:192)

at com.seec.insurance.life.partySearch.countryDB.SearchCountryDB.partySearch(SearchCountryDB.java:104)

at com.seec.insurance.life.partySearch.dbIntegration.SearchDBIntegration.partySearch(SearchDBIntegration.java:73)

at com.seec.insurance.life.partySearch.components.SearchComp.partySearch(SearchComp.java:37)

at com.seec.insurance.life.partySearch.webservice.PartySearchWS.partySearch(PartySearchWS.java:69)

at sun.reflect.GeneratedMethodAccessor175.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))

at java.lang.reflect.Method.invoke(Method.java(Compiled Code))

at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)

at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)

at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)

at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)

at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)

at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)

at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:453)

at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)

at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java(Compiled Code))

at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)

at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)

at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)

at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)

at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)

at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)

at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)

at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)

at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:1018)

at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:529)

at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:208)

at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:134)

at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:321)

at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)

at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:120)

at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:250)

at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)

at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)

at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java(Compiled Code))

at com.ibm.ws.http.HttpConnection.run(HttpConnection.java(Compiled Code))

at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))

Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.throw_SQLException(Unknown Source)

at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(Unknown Source)

at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(Unknown Source)

at COM.ibm.db2.jdbc.net.DB2ResultSet.next(Unknown Source)

at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java(Compiled Code))

at org.hibernate.loader.Loader.doQuery(Loader.java(Compiled Code))

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)

at org.hibernate.loader.Loader.doList(Loader.java:2145)

... 47 more

so i would look at your db and your queries...nothing hibernate specific in those messages.

We are using DB2.
the following query is being used for it. only T3 info is missing randomly.

SELECT T1.*,T3.*,T10.*, T6.* FROM CONTACT T1

LEFT OUTER JOIN ORG_GROUP T6 ON T1.PR_GRP_OU_ID = T6.ROW_ID
LEFT OUTER JOIN ADDR_PER T3 ON T6.PR_ADDR_PER_ID = T3.ROW_ID
LEFT OUTER JOIN CONTACT2_FNX T10 ON T1.ROW_ID = T10.PAR_ROW_ID

WHERE (UPPER(T1.CON_CD) = 'CUSTOMER') AND ( UPPER(T1.X_LAST_NAME_CAP) LIKE 'WILLIAMS')
AND UPPER(T3.CITY) LIKE 'BLOOMINGTON' fetch first 100 rows only

regards,
deepika

so investigate why that sql is causing Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010 at the *jdbc level*

Try to dissable statement cache in DB pool (or test aplication without JDBC connection pool), if this doe's not help then verify hibernate session management, it must not be shared by threads ("Function sequence error" is reported by driver, some JDBC methods are executed in the wrong order, probably connection is accessed after "close").