[hibernate-issues] [Hibernate-JIRA] Resolved: (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 resolved HHH-2388.
-----------------------------

    Resolution: Won't Fix

To make it working, use one of the turn around solutions:

- Use jconn3.jar with DYNAMIC_PREPARE=true option (in Hibenate
configuration file)
  Example:
  <property name="connection.url">jdbc:sybase:Tds:aurum:1503/masterDb?
  DYNAMIC_PREPARE=true</property>

OR

- Use jconn4.jar

Reason:
ASE does not throw exception when sql is Parameterized Sql, jconn3.jar uses
Parameterized Sql for insertion hence no exception is thrown
By using Dynamic Prepare as 'true' will cause ASE to throw exception.

Where as jconn4.jar we are using Dynamic Prepared Statement hence exception
is thrown.

Note:
While using jconn3.jar use
<property name="connection.driver_class">com.sybase.jdbc3.jdbc.SybDriver</
property>

And for jconn4.jar use
<property name="connection.driver_class">com.sybase.jdbc4.jdbc.SybDriver</
property>

> 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: HHH-2388.patch, 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