[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-2388) Insert w/ identity column fails on Sybase but no exception occurs

[strong liu (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2388?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

strong liu updated HHH-2388:
----------------------------

    Attachment: MyEntity.hbm.xml
                MyEntityTest.java
                MyEntity.java

Sybase throws an Exception when hibernate try to get generated key before hibernate run into org.hibernate.id.IdentityGenerator$InsertSelectDelegate
public Serializable executeAndExtract(PreparedStatement insert) 

I have attached the test case.
--------------------------------------------------------
23:58:00,354  INFO SchemaExport:196 - schema export complete
Hibernate: 
    /* insert MyEntity
        */ insert 
        into
            MyEntity
            (cost) 
        values
            (?)
java.sql.SQLException: JZ0NK: Generated keys are not available because either the Statement.NO_GENERATED_KEYS was used or no keys were autom
atically generated.
	at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
	at com.sybase.jdbc3.jdbc.SybStatement.getGeneratedKeys(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.hibernate.util.GetGeneratedKeysHelper.getGeneratedKey(GetGeneratedKeysHelper.java:69)
	at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:76)
	at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:33)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2162)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2642)
	at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)


> Insert w/ identity column fails on Sybase but no exception occurs
> -----------------------------------------------------------------
>
>                 Key: HHH-2388
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2388
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.1
>         Environment: Hibernate 3.2.1.GA with annotations
> Sybase jConnect 6.05 JDBC driver
> Sybase ASE 15 database
>            Reporter: Tim Morrow
>            Assignee: strong liu
>         Attachments: MyEntity.hbm.xml, MyEntity.java, MyEntityTest.java
>
>
> I have a scenario where storing a new entity fails (i.e. the row is not inserted) but Hibernate does not realize this.  No exceptions are thrown.  Later, this leads to AssertionFailures (if two such entities fail in the same session - they both have the same PK).
> The problem specifically occurs with a table that has a numeric column with precision (e.g. numeric(10,4)) and an identity column when using Sybase ASE.
> ==========
> To reproduce:
> 1. Use Sybase jConnect 6.05 JDBC driver with Sybase ASE 15 database.
> 2. Define an Entity with a long ID and BigDecimal numeric column.
> 3. Create a corresponding table with an identity column and numeric(10, 4) column.
> For example:
> hibernate.MyEntity.java:
> -----------------------------------------
> package hibernate;
> import java.math.BigDecimal;
> import javax.persistence.Column;
> import javax.persistence.Entity;
> import javax.persistence.GeneratedValue;
> import javax.persistence.GenerationType;
> import javax.persistence.Id;
> import javax.persistence.Table;
> import org.hibernate.validator.NotNull;
> @Entity
> @Table(name = "z_tim_test")
> public class MyEntity {
>     private BigDecimal cost;
>     private long id;
>     public MyEntity() {}
>     @Column(columnDefinition = "numeric(10,4)")
>     @NotNull public final BigDecimal getCost() {
>         return cost;
>     }
>     @GeneratedValue(strategy = GenerationType.AUTO)
>     @Id public final long getId() {
>         return id;
>     }
>     public final void setCost(BigDecimal cost) {
>         this.cost = cost;
>     }
>     public final void setId(long id) {
>         this.id = id;
>     }
> }
> Table:
> -----------------------------------------
> CREATE TABLE z_tim_test
> (
>    id numeric(19) PRIMARY KEY not null,
>    cost numeric(10,4) not null
> );
> 4. Write some code that stores a new entity and tries to load it:
>         MyEntity myEntity = new MyEntity();
>         myEntity.setCost(new BigDecimal("123.12345"));
>         session.save(myEntity);
>         session.flush();
>         session.clear();
>         List<MyEntity> results = session.createCriteria(MyEntity.class).list();
>         if (results.size() != 1) {
>             throw new IllegalStateException("Expected 1 result");
>         }
> This test will throw the IllegalStateException because the row was not persisted and no errors occurred.
> Reason:
> * Sybase does not thrown any SQLException when you try and persist a numeric value whose scale exceeds that defined on the column.  Instead, it returns an updateCount of zero and an identity column value of zero.
> * Hibernate does not check the updateCount after executing the statement when using an Identity column.  The offending code is in:
>     org.hibernate.id.IdentityGenerator$InsertSelectDelegate
> 		public Serializable executeAndExtract(PreparedStatement insert) throws SQLException {
> 			if ( !insert.execute() ) {
> 				while ( !insert.getMoreResults() && insert.getUpdateCount() != -1 ) {
> 					// do nothing until we hit the rsult set containing the generated id
> 				}
> 			}
> 			ResultSet rs = insert.getResultSet();
> 			try {
> 				return IdentifierGeneratorFactory.getGeneratedIdentity( rs, persister.getIdentifierType() );
> 			}
> 			finally {
> 				rs.close();
> 			}
> 		}
> It ignores the updateCount.
> The net result is that the object is assigned a PK value of zero.  Hibernate continues.  My applicaiton is unaware that the row failed to insert.
> Solution:
> It would seem to me replacing the above code with:
>         if (!insert.execute()) {
>             if (insert.getUpdateCount() < 1) {
>                 throw new HibernateException("No update occurred");
>             }
>             while (!insert.getMoreResults()) {
>                 // do nothing until we hit the rsult set containing the generated id
>             }
>         }
> Would take care of the problem?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira