-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
hanslenaers
 Post subject: Generated statement cannot update pseudo column ORA_ROWSCN!
PostPosted: Tue Apr 22, 2008 5:19 am 
Newbie

Joined: Fri May 11, 2007 11:06 am
Posts: 3
Location: Belgium
We have succesfully mapped annotated (JPA) a class to our legacy database (Oracle 10G). In a JUnit4 test we were able to retrieve a record.
However we want to implement optimistic locking using the Oracle pseudo column ORA_ROWSCN because we can't change the database and we cannot interfere with legacy transactions.
But we were not able to get this working!
The pseudo column is be updated by the database and not by the generated update statement of Hibernate!

The generated update statement:

Code:
    update
        mytable
    set
        PROP4=?,
        PROP1=?,
        PROP2=?,
        PROP3=?,
        ORA_ROWSCN=?
    where
        ID=?
        and ORA_ROWSCN=?

but should be:

Code:
    update
        mytable
    set
        PROP4=?,
        PROP1=?,
        PROP2=?,
        PROP3=?,
    where
        ID=?
        and ORA_ROWSCN=?


Therefore we get an exception.

*************************************************************

Here is all the info (specific details were changed for security reasons)


Hibernate version:
hibernate 3.2.5 GA
hibernate annotations 3.3.0 GA
hibernate entitymanager 3.3.1 GA

Name and version of the database you are using:
Oracle 10g

Mapping documents:
Code:
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd" version="1.0">
   <persistence-unit name="***" transaction-type="RESOURCE_LOCAL">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
           <class>mypackage.MyClass</class>
      <properties>
         <property name="hibernate.archive.autodetection" value="none"/>
         <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" />
         <property name="hibernate.connection.url" value="***" />
         <property name="hibernate.connection.username" value="***" />
         <property name="hibernate.connection.password" value="***" />
                                  <property name="hibernate.connection.isolation" value="2" />
         <property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect" />
         <property name="hibernate.show_sql" value="true" />
         <property name="hibernate.format_sql" value="true" />
         <property name="org.hibernate.SQL" value="true" />
         <property name="org.hibernate.type" value="true" />
         <property name="org.hibernate.tool.hbm2ddl" value="true" />
         <property name="org.hibernate.pretty" value="true" />
         <property name="org.hibernate.cache" value="true" />
         <property name="org.hibernate.transaction" value="true" />
         <property name="org.hibernate.jdbc" value="true" />
         <property name="org.hibernate.hql.ast.AST" value="true" />
         <property name="org.hibernate.secure" value="true" />
         <property name="org.hibernate" value="true" />
      </properties>
   </persistence-unit>
</persistence>



Code:
@Entity
@Table(name = "mytable")
@NamedQuery(name = "MyClass.findAll", query = "select o from "MyClass o")
public class "MyClass extends EntityObject {
    @Version
    @Column(name = "ORA_ROWSCN")
    private Long version;

    @Id
    @Column(name = "ID", nullable = false)
    private Long id;

    @Column(name = "PROP1")
    private Long prop1;

    @Column(name = "PROP2")
    private Long prop2;

    @Column(name = "PROP3", nullable = false)
    private String prop3;

    @Column(name = "PROP4")
    private Date prop4;
}


public class MyTestClass {
    String persistenceUnit = "***";
    private EntityManagerFactory emf = null;
    private EntityManager em = null;

    @Before
    public void startTest() {
        try {
            emf = Persistence.createEntityManagerFactory(persistenceUnit);
            em = emf.createEntityManager();
            em.getTransaction().begin();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    @After
    public void endTest() {
        try {
            if(em.getTransaction().isActive()) {
                em.getTransaction().commit();
            }
            em.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    @Test
    public void updateKlant() {
        final long objId = 100277837L;
        try {
            MyClass object = getEntityManager().find(MyClass.class, objId);
            assertNotNull(object);
            System.out.println(object);
            object.setProp3("blabla");
            getEntityManager().persist(object);
//            getEntityManager().flush();
        } catch (Exception e) {
            fail(e.toString());
        }
    }
}


Full stack trace of any exception that occurs:
Code:
22-apr-2008 10:25:27 org.hibernate.cfg.annotations.Version <clinit>
INFO: Hibernate Annotations 3.3.0.GA
22-apr-2008 10:25:27 org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.2.5
22-apr-2008 10:25:27 org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
22-apr-2008 10:25:27 org.hibernate.cfg.Environment buildBytecodeProvider
INFO: Bytecode provider name : cglib
22-apr-2008 10:25:27 org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
22-apr-2008 10:25:27 org.hibernate.ejb.Version <clinit>
INFO: Hibernate EntityManager 3.3.1.GA
22-apr-2008 10:25:29 org.hibernate.cfg.AnnotationBinder bindClass
INFO: Binding entity from annotated class: mypackage.MyClass
22-apr-2008 10:25:29 org.hibernate.cfg.annotations.QueryBinder bindQuery
INFO: Binding Named query: MyClass.findAll => select o from MyClass o
22-apr-2008 10:25:29 org.hibernate.cfg.annotations.EntityBinder bindTable
INFO: Bind entity mypackage.MyClass on table mytable
22-apr-2008 10:25:29 org.hibernate.validator.Version <clinit>
INFO: Hibernate Validator 3.0.0.GA
22-apr-2008 10:25:29 org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
22-apr-2008 10:25:29 org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 20
22-apr-2008 10:25:29 org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: true
22-apr-2008 10:25:29 org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: JDBC isolation level: READ_COMMITTED
22-apr-2008 10:25:29 org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: oracle.jdbc.driver.OracleDriver at URL: ***
22-apr-2008 10:25:29 org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=***, password=****, autocommit=true, release_mode=auto}
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: Oracle, version: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: Oracle JDBC driver, version: 10.2.0.3.0
22-apr-2008 10:25:30 org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.OracleDialect
22-apr-2008 10:25:30 org.hibernate.dialect.Oracle9Dialect <init>
WARNING: The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead
22-apr-2008 10:25:30 org.hibernate.dialect.OracleDialect <init>
WARNING: The OracleDialect dialect has been deprecated; use Oracle8iDialect instead
22-apr-2008 10:25:30 org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
22-apr-2008 10:25:30 org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 15
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: auto
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL inserts for batching: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
22-apr-2008 10:25:30 org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: JPA-QL strict compliance: enabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory createCacheProvider
INFO: Cache provider: org.hibernate.cache.NoCacheProvider
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: pojo
22-apr-2008 10:25:30 org.hibernate.cfg.SettingsFactory buildSettings
INFO: Named query checking : enabled
22-apr-2008 10:25:30 org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
22-apr-2008 10:25:30 org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Hibernate:
    select
        mytable0_.ID as KLT1_0_0_,
        mytable0_.PROP4 as KLT2_0_0_,
        mytable0_.PROP1 as KLT3_0_0_,
        mytable0_.PROP2 as KLT4_0_0_,
        mytable0_.PROP3 as KLT5_0_0_,
        mytable0_.ORA_ROWSCN as ORA6_0_0_
    from
        mytable mytable0_
    where
        mytable_.ID=?
Hibernate:
    update
        mytable
    set
        PROP4=?,
        PROP1=?,
        PROP2=?,
        PROP3=?,
        ORA_ROWSCN=?
    where
        ID=?
        and ORA_ROWSCN=?
22-apr-2008 10:25:31 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1747, SQLState: 42000
22-apr-2008 10:25:31 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-01747: invalid user.table.column, table.column, or column specification

22-apr-2008 10:25:31 org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: could not update: [mypackage.MyClass#100277837]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2425)
   at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2307)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2607)
   at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:92)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:234)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
   at mypackage.test.MyTestClass.endTest(MyTestClass.java:103)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:615)
   at org.junit.internal.runners.BeforeAndAfterRunner.invokeMethod(BeforeAndAfterRunner.java:74)
   at org.junit.internal.runners.BeforeAndAfterRunner.runAfters(BeforeAndAfterRunner.java:65)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:37)
   at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
   at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
   at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
   at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
   at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
   at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
   at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
   at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
   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:745)
   at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
   at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
   at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2407)
   ... 33 more
Top
 Profile  
 
sanne.grinovero
 Post subject:
PostPosted: Tue Apr 22, 2008 10:36 am 
Hibernate Team
Hibernate Team

Joined: Fri Oct 05, 2007 4:47 pm
Posts: 2536
Location: Third rock from the Sun
Hi,
you may try this:

Code:
@Version
@Column(name = "ORA_ROWSCN", updatable=false, insertable=false)
private Long version;

So you get hibernate not to include this column in insert and update statements.

_________________
Sanne
http://in.relation.to/

Top
 Profile  
 
hanslenaers
 Post subject:
PostPosted: Tue Apr 22, 2008 11:04 am 
Newbie

Joined: Fri May 11, 2007 11:06 am
Posts: 3
Location: Belgium
Thanks, but changing annotation into:

Code:
@Version
@Column(name = "ORA_ROWSCN", updatable=false, insertable=false)
private Long version;


results in following query omitting the ORA-ROWSCN from the where clause:

Code:
    update
        mytable
    set
        PROP4=?,
        PROP1=?,
        PROP2=?,
        PROP3=?
    where
        ID=?


which is not what we want.
Top
 Profile  
 
CowinR
 Post subject:
PostPosted: Mon Jun 16, 2008 7:03 am 
Newbie

Joined: Wed Nov 01, 2006 8:00 am
Posts: 2
Did you find a solution to this? I also need to do the same...
Top
 Profile  
 
hanslenaers
 Post subject:
PostPosted: Mon Jun 16, 2008 10:23 am 
Newbie

Joined: Fri May 11, 2007 11:06 am
Posts: 3
Location: Belgium
Hi,

The solution as suggested by s.grinovero did the job.
We were confused by the SQL that was generated. We expected to find the ORA_ROWSCN in the where clause of the outputted sql statement.
Is it possible that the generated sql statement differs from the actual statement?
A delete for examples includes the ORA_ROWSCN.
Top
 Profile  
 
sanne.grinovero
 Post subject:
PostPosted: Mon Jun 16, 2008 10:51 am 
Hibernate Team
Hibernate Team

Joined: Fri Oct 05, 2007 4:47 pm
Posts: 2536
Location: Third rock from the Sun
Hi,
yes I also was expecting ORA_ROWSCN to be included, not sure about this.

_________________
Sanne
http://in.relation.to/

Top
 Profile  
 
jimothy
 Post subject: Try @org.hibernate.Generated
PostPosted: Tue Jul 29, 2008 10:47 am 
Newbie

Joined: Thu Apr 29, 2004 1:44 pm
Posts: 1
Here's what I'm using:

@Version
@Column(name = "ORA_ROWSCN", updatable = false, insertable = false)
@org.hibernate.annotations.Generated(GenerationTime.ALWAYS)

Though I haven't fully tested this, it does result in ORA_ROWSCN appearing in the WHERE clause.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.