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

[Michael Joyce (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32624#action_32624 ] 

Michael Joyce commented on HHH-2388:
------------------------------------

Is it possible to detect the warning that Sybase issues: http://www.sybase.com/detail?id=1037380#BABHCIBG and cope with that, by throwing an exception from Hibernate?

Running an insert with the cast(Blah as numeric(32,<1 more than colum allows>) gives me:

SQLWarning: ErrorCode:   3624 SQLState: 01ZZZ --- Truncation error occurred.
SQLWarning: ErrorCode:   3621 SQLState: 01ZZZ --- Command has been aborted.
0 Row(s) Inserted

To my SQL Client it looks like this succeeded.



> 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: Gail Badner
>
> 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