We have not been able to get Hibernate (version 3.3.2 GA) to successfully execute a MySQL stored procedure associated with a class mapping for sql-insert. This class also has generator="Identity". We have tried all three values for the 'check' attribute on sql-insert. We have been able to get the sql-delete to work after setting the check="none" attribute.


<class name="Person" table="Person">
<id name="id" type="integer">
<column name="ID" />
<generator class="identity" />
</id>
<property name="name" type="string">
<column name="NAME" length="24" />
</property>
<sql-insert callable="true">{call createPerson (?)}</sql-insert>
<sql-delete callable="true" check="none">{call deletePerson (?)}</sql-delete>
</class>

The sql-insert stored procedure expects the first (and only parameter) to be NAME), does various tests, and then if successful, it does the insert and the returns a result set using:
Select LAST_INSERT_ID().
We also tried check="param" and created two parameters:
createPerson (?, ?) where the first parameter is defined as "OUT pid INT" and this also does not work (no value for parameter 2 provided).

------------------------------------
The exception we are getting with one parameter: createPerson (?)
is below:

Message: The database returned no natively generated identity value
org.hibernate.id.IdentifierGeneratorFactory.getGeneratedIdentity(IdentifierGeneratorFactory.java:90)
org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:98)
org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57)
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2176)
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2656)
org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321)
org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204)
org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130)
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListene
r.java:210)
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:117)

org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:535)
org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:527)
org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:523)

I just found this post and am having exactly the same problem with Hibernate 3.2.7, as well as 3.5.5 when I upgraded just to see if it was fixed.

It appears to be an issue with how Sybase returns the value from the stored procedure, and the ResultSet can't find it for whatever reason.

Was there any resolution to this? If so, would you post the fix/workaround please?

Thank you!
Dustin

In case anyone else runs into this, I got it to work by writing my own IdentityGenerator that forces the use of the InsertSelectDelegate that is present in the IdentityGenerator class since I didn't want to turn off GetGeneratedKeys for the entire Factory, just this one class that needed to use the sql-insert with a stored procedure:

public class StoredProcedureIdentityGenerator
extends AbstractPostInsertGenerator
{
public InsertGeneratedIdentifierDelegate getInsertGeneratedIdentifierDelegate(
final PostInsertIdentityPersister persister, final Dialect dialect,
final boolean isGetGeneratedKeysEnabled) throws HibernateException
{
return new InsertSelectDelegate(persister, dialect);
}


/**
* Delegate for dealing with IDENTITY columns where the dialect supports returning
* the generated IDENTITY value directly from the insert statement.
*
* Written by Christoph Sturm
* $Id: IdentityGenerator.java 14098 2007-10-18 06:17:56Z gbadner $
*/
public static class InsertSelectDelegate
extends AbstractReturningDelegate
implements InsertGeneratedIdentifierDelegate {
private final PostInsertIdentityPersister persister;
private final Dialect dialect;

public InsertSelectDelegate(
final PostInsertIdentityPersister persister,
final Dialect dialect)
{
super(persister);
this.persister = persister;
this.dialect = dialect;
}

public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
InsertSelectIdentityInsert insert = new InsertSelectIdentityInsert( dialect );
insert.addIdentityColumn( persister.getRootTableKeyColumnNames()[0] );
return insert;
}

protected PreparedStatement prepare(String insertSQL, SessionImplementor session) throws SQLException
{
return session.getBatcher().prepareStatement( insertSQL, false );
}

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();
}
}

public Serializable determineGeneratedIdentifier(
final SessionImplementor session, final Object entity)
{
throw new AssertionFailure( "insert statement returns generated value" );
}
}
}