Hi all,
I am using the pessimistic lock 'Upgrade' in my named query. However I encounter some sql grammar error while executing in Oracle DB. I have tested with Postgres DB and without error. The named query is listed below
Code:
<query name="getProcessTransByProUID"><![CDATA[FROM ProcessTransaction AS pt WHERE pt.processInstanceUID = :processInstanceUID]]></query>
below is subset of the code that prepare the named query and applying the LockMode.Upgrade
Code:
//From class ProcessTransactionDAO
public ProcessTransaction selectForUpdateProcessTrans(Long processUID)
{
String queryName = getPersistenceClass().getName()+".getProcessTransByProUID";
String[] paramName = new String[]{"processInstanceUID"};
Long[] paramValue = new Long[]{processUID};
ProcessTransaction proTran = (ProcessTransaction)queryOneWithLock(queryName, paramName, paramValue, "pt", LockMode.UPGRADE);
return proTran;
}
//From class DAO
public Object queryOneWithLock(String queryName, String[] paramNames, Object[] paramVals, String alias, LockMode lockMode)
{
Query query = createQuery(queryName, paramNames, paramVals);
if (query == null)
{
return null;
}
query.setMaxResults(1);
query.setLockMode(alias, lockMode);
return query.uniqueResult();
}
//From class DAO
protected Query createQuery(String queryName, String[] paramNames, Object[] paramVals)
{
Query query = getCurrentSession().getNamedQuery(queryName);
if (query == null)
{
System.err.println("Unable to find named query: "+queryName);
return null;
}
if (paramNames != null && paramVals != null && paramNames.length<=paramVals.length)
{
for (int i=0; i<paramNames.length; i++)
{
query.setParameter(paramNames[i], paramVals[i]);
}
}
return query;
}
The error I encoutered was as followed
[30:04:07-22:02:55,242] [ INFO:STDOUT] [JMS SessionPool Worker-54] Hibernate: select * from ( select processtra0_."uid" as uid1_86_, processtra0_."version" as version2_86_, processtra0_."group_name" as group3_86_, processtra0_."customer_duns" as customer4_86_, processtra0_."customer_name" as customer5_86_, processtra0_."error_reason" as error6_86_, processtra0_."error_type" as error7_86_, processtra0_."partner_duns" as partner8_86_, processtra0_."partner_name" as partner9_86_, processtra0_."pip_name" as pip10_86_, processtra0_."pip_version" as pip11_86_, processtra0_."process_end_time" as process12_86_, processtra0_."process_id" as process13_86_, processtra0_."process_start_time" as process14_86_, processtra0_."process_status" as process15_86_, processtra0_."request_doc_no" as request16_86_, processtra0_."response_doc_no" as response17_86_, processtra0_."process_instance_uid" as process18_86_, processtra0_."is_process_success" as is19_86_, processtra0_."is_initiator" as is20_86_, processtra0_."user_tracking_id" as user21_86_ from GTVAN."isat_process_transaction" processtra0_ where processtra0_."process_instance_uid"=? ) where rownum <= ?
for update of processtra0_."uid"
[30:04:07-22:02:55,242] [DEBUG:org.hibernate.loader.hql.QueryLoader] [JMS SessionPool Worker-54] bindNamedParameters() 14491 -> processInstanceUID [1]
[30:04:07-22:02:55,273] [DEBUG:org.hibernate.jdbc.AbstractBatcher] [JMS SessionPool Worker-54] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[30:04:07-22:02:55,273] [DEBUG:org.hibernate.jdbc.ConnectionManager] [JMS SessionPool Worker-54] aggressively releasing JDBC connection
[30:04:07-22:02:55,273] [DEBUG:org.hibernate.jdbc.ConnectionManager] [JMS SessionPool Worker-54] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
[30:04:07-22:02:55,289] [DEBUG:org.hibernate.util.JDBCExceptionReporter] [JMS SessionPool Worker-54] could not execute query [select processtra0_."uid" as uid1_86_, processtra0_."version" as version2_86_, processtra0_."group_name" as group3_86_, processtra0_."customer_duns" as customer4_86_, processtra0_."customer_name" as customer5_86_, processtra0_."error_reason" as error6_86_, processtra0_."error_type" as error7_86_, processtra0_."partner_duns" as partner8_86_, processtra0_."partner_name" as partner9_86_, processtra0_."pip_name" as pip10_86_, processtra0_."pip_version" as pip11_86_, processtra0_."process_end_time" as process12_86_, processtra0_."process_id" as process13_86_, processtra0_."process_start_time" as process14_86_, processtra0_."process_status" as process15_86_, processtra0_."request_doc_no" as request16_86_, processtra0_."response_doc_no" as response17_86_, processtra0_."process_instance_uid" as process18_86_, processtra0_."is_process_success" as is19_86_, processtra0_."is_initiator" as is20_86_, processtra0_."user_tracking_id" as user21_86_ from GTVAN."isat_process_transaction" processtra0_ where processtra0_."process_instance_uid"=?]
java.sql.SQLException: ORA-00904: "PROCESSTRA0_"."uid": invalid identifier
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:911)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1121)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1243)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3459)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:780)
at util.db.DAO.queryOneWithLock(DAO.java:272)
at audit.dao.ProcessTransactionDAO.selectForUpdateProcessTrans(ProcessTransactionDAO.java:60)
As can be seen from the above stack trace, the select statement seem like something wrong so the exception java.sql.SQLException: ORA-00904: "PROCESSTRA0_"."uid": invalid identifier has been throwned. As can be seen below,
select * from
( select processtra0_."uid" as uid1_86_, processtra0_."version" as version2_86_, processtra0_."group_name" as group3_86_, processtra0_."customer_duns" as customer4_86_, processtra0_."customer_name" as customer5_86_, processtra0_."error_reason" as error6_86_, processtra0_."error_type" as error7_86_, processtra0_."partner_duns" as partner8_86_, processtra0_."partner_name" as partner9_86_, processtra0_."pip_name" as pip10_86_, processtra0_."pip_version" as pip11_86_, processtra0_."process_end_time" as process12_86_, processtra0_."process_id" as process13_86_, processtra0_."process_start_time" as process14_86_, processtra0_."process_status" as process15_86_, processtra0_."request_doc_no" as request16_86_, processtra0_."response_doc_no" as response17_86_, processtra0_."process_instance_uid" as process18_86_, processtra0_."is_process_success" as is19_86_, processtra0_."is_initiator" as is20_86_, processtra0_."user_tracking_id" as user21_86_ from GTVAN."isat_process_transaction" processtra0_ where processtra0_."process_instance_uid"=?
) where rownum <= ?
for update of processtra0_."uid"
Is the way I use the pessismistic lock correct? If so , is it the problem on the Oracle version i am using now ?
The setup in my box is as followed
Hibernate 3.2 GA
Oracle 10g
Jboss 4.0.5 GA
Thanks in advance
Login
FAQ
Search