Hi
I would to know if it is possible to use a stored procedure to insert new values having the database generating the id? My following example doesn't work.

Hibernate version:
3.0.1
Mapping documents:


public class Product {
private long id;
private String name;

public long getId() {return id; }
public String getName() {return name;}
public void setId(long string) {id = string; }
public void setName(String string) {name = string;}
}


<hibernate-mapping>

<class name="fr.digimind.jdo.hibernate.Product" table="Product">

<id name="id" type="long">
<generator class="native"/>
</id>

<property name="name" not-null="true"/>

<sql-insert callable="true">{call add_product (?)}</sql-insert>


</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Transaction t = sess.beginTransaction();
Product p = new Product();
p.setName("test2");
sess.save(p);
t.commit();

Full stack trace of any exception that occurs:

org.hibernate.HibernateException: The database returned no natively generated identity value
at org.hibernate.id.IdentifierGeneratorFactory.getGeneratedIdentity(IdentifierGeneratorFactory.java:33)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1770)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2187)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:445)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:440)
at fr.digimind.jdo.hibernate.FindProductByName.main(FindProductByName.java:91)
Exception in thread "main"

Name and version of the database you are using:
MS SQLServer 2000

The generated SQL (show_sql=true):

19 12:31:14 DEBUG [org.hibernate.transaction.JDBCTransaction] begin
19 12:31:14 DEBUG [org.hibernate.transaction.JDBCTransaction] current autocommit status: true
19 12:31:14 DEBUG [org.hibernate.transaction.JDBCTransaction] disabling autocommit
19 12:31:14 DEBUG [org.hibernate.event.def.DefaultSaveOrUpdateEventListener] saving transient instance
19 12:31:14 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] generated identifier: , using strategy: org.hibernate.id.IdentityGenerator
19 12:31:14 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] saving [fr.digimind.jdo.hibernate.Product#<null>]
19 12:31:14 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] executing insertions
19 12:31:14 DEBUG [org.hibernate.persister.entity.BasicEntityPersister] Inserting entity: fr.digimind.jdo.hibernate.Product (native id)
19 12:31:14 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
19 12:31:14 DEBUG [org.hibernate.SQL] {call add_product (?)}
19 12:31:14 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
19 12:31:14 DEBUG [org.hibernate.persister.entity.BasicEntityPersister] Dehydrating entity: [fr.digimind.jdo.hibernate.Product#<null>]
19 12:31:14 DEBUG [org.hibernate.type.StringType] binding 'test2' to parameter: 1
19 12:31:14 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
19 12:31:14 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement


the Stored Procedure:
CREATE PROCEDURE add_product
@name varchar(255)
AS
insert into Product (name) values (@name)
select @@identity
GO

It doesn't work with or without the 'select @@identity'

Thanks
ju

Can I see you stored procedure?

I've already given it... at the end of the post

Opps sorry.

How do you have your auto generated value set up in DB2. I ran into a problem where it won't allow me to import values. I set it to increment as defualt.

To insert a row I had to do
insert into TABLE values (DEFAULT, name)


Don't quote me but after you insert one value then you can i
nsert into table (name)

Just throwing it out there..not sure if it is right or not, I'm new at DB2.

B

you need to try and use IdentityGenerator or SelectGenerator.

Ain't got more success...
none of the identity, select, native or increment generator works.
However, it works fine when I put the insert sql function directly in the mapping file.[/code]

how about you remove the select @@identity from the SP as that is what hibernate wille execute if you are using native.

same results...
I wonder if a '?' has to be used in the mapping file to represent the object id:
<sql-insert callable="true">{ ? = call add_product (?)}</sql-insert>
Anyway it doesn't work either.

then use the other identitygenerator that allows you to specify a select hibernate should do on the table to get the id

I tried

the insert function works but the stored procedure still doesn't.
May it come from the driver? I'n using the jTDS driver but with the MS SQLSERVER default driver, another error occur:
java.lang.AbstractMethodError: com.microsoft.jdbc.sqlserver.SQLServerConnection.prepareStatement(Ljava/lang/String;I)Ljava/sql/PreparedStatement;
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.hibernate.util.GetGeneratedKeysHelper.prepareStatement(GetGeneratedKeysHelper.java:39)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:373)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:74)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1748)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2187)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:445)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:440)
at fr.digimind.jdo.hibernate.FindProductByName.main(FindProductByName.java:90)

It happens with the sp and with the insert function...

which jdk and which version of jtds ?

jdk 1.4.2 and jTDS 1.0.3

from the stacktrace you seem to suddenly have eneabled JDBC 3 only getGeneratedKeys support...why ?

I don't know at all.
it seems to enable by default in the hibernate.properties file.

ok Thanks very much!!
now it works with the SelectGenerator
I 've still got some problems with the IdentityGenerator (see the log) but if you don't have time to help me, I will handle my programs with the Selectgenerator.

Thanks again
ju

(same error with the jTDS or the MS SQL driver)
20 10:41:22 DEBUG [org.hibernate.transaction.JDBCTransaction] begin
20 10:41:22 DEBUG [org.hibernate.transaction.JDBCTransaction] current autocommit status: true
20 10:41:22 DEBUG [org.hibernate.transaction.JDBCTransaction] disabling autocommit
20 10:41:22 DEBUG [org.hibernate.event.def.DefaultSaveOrUpdateEventListener] saving transient instance
20 10:41:22 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] generated identifier: , using strategy: org.hibernate.id.IdentityGenerator
20 10:41:22 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] saving [fr.digimind.jdo.hibernate.Product#<null>]
20 10:41:22 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] executing insertions
20 10:41:22 DEBUG [org.hibernate.persister.entity.BasicEntityPersister] Inserting entity: fr.digimind.jdo.hibernate.Product (native id)
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
20 10:41:22 DEBUG [org.hibernate.SQL] {call add_product (?)}
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
20 10:41:22 DEBUG [org.hibernate.persister.entity.BasicEntityPersister] Dehydrating entity: [fr.digimind.jdo.hibernate.Product#<null>]
20 10:41:22 DEBUG [org.hibernate.type.StringType] binding 'test3' to parameter: 1
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
20 10:41:22 DEBUG [org.hibernate.SQL] call identity()
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
20 10:41:22 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement
20 10:41:22 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not insert: [fr.digimind.jdo.hibernate.Product] [call identity()]
java.sql.SQLException: Ligne 1 : syntaxe incorrecte vers 'call'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214)
at net.sourceforge.jtds.jdbc.TdsCore.clearResponseQueue(TdsCore.java:697)
at net.sourceforge.jtds.jdbc.TdsCore.submitSQL(TdsCore.java:864)
at net.sourceforge.jtds.jdbc.TdsCore.microsoftPrepare(TdsCore.java:1093)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:537)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:664)
at org.hibernate.id.AbstractPostInsertGenerator.getGenerated(AbstractPostInsertGenerator.java:42)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1794)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2187)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:445)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:440)
at fr.digimind.jdo.hibernate.FindProductByName.main(FindProductByName.java:90)
20 10:41:22 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 170, SQLState: 37000
20 10:41:22 ERROR [org.hibernate.util.JDBCExceptionReporter] Ligne 1 : syntaxe incorrecte vers 'call'.

that error message is jtds/mssql stored procedure stuff that complains.....

as always SP's and error messages is so precise in their reporting ;)

i can't remember the mssql jdbc syntax for sp's, but look in their docs for it and dont be surprised if a single whitespace is misplaced somewhere.

/max