Hi!
I need some help because I'm having some trouble using stored procedures on a Mysql DB. I want to change the hibernate generated SQL insert function to a custom SP (stored procedure). I also want the DB to generate the data id with a simple auto-incemented counter.
The problem is that Hibernate does not find the generated id after the SP is called. Here is the code:
Hibernate version:
3.1.3 with MySQL driver 3.1.13
Mapping documents:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping
>
<class
name="fr.digimind.factory.databean.NewsIndexBean"
table="indexnews"
lazy="false"
>
<id
name="idNI"
column="Id_NI"
type="long"
unsaved-value="any"
>
<generator class="native">
</generator>
</id>
<property
name="id_Site"
type="long"
update="true"
insert="true"
column="Id_Site"
/>
<property
name="urlNI"
type="string"
update="true"
insert="true"
column="Url_NI"
/>
<sql-insert callable="true"> call insert_IndexNews(?,?) </sql-insert>
</class>
</hibernate-mapping>
Full stack trace of any exception that occurs:
DEBUG [org.hibernate.transaction.JDBCTransaction] begin
DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
DEBUG [org.hibernate.connection.DriverManagerConnectionProvider] total checked-out connections: 0
DEBUG [org.hibernate.connection.DriverManagerConnectionProvider] using pooled JDBC connection, pool size: 0
DEBUG [org.hibernate.transaction.JDBCTransaction] current autocommit status: false
DEBUG [org.hibernate.jdbc.JDBCContext] after transaction begin
DEBUG [org.hibernate.event.def.DefaultSaveOrUpdateEventListener] saving transient instance
DEBUG [org.hibernate.event.def.AbstractSaveEventListener] saving [fr.digimind.factory.databean.NewsIndexBean#<null>]
DEBUG [org.hibernate.event.def.AbstractSaveEventListener] executing insertions
DEBUG [org.hibernate.persister.entity.AbstractEntityPersister] Inserting entity: fr.digimind.factory.databean.NewsIndexBean (native id)
DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG [org.hibernate.SQL] call insert_IndexNews(?,?)
DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
DEBUG [org.hibernate.persister.entity.AbstractEntityPersister] Dehydrating entity: [fr.digimind.factory.databean.NewsIndexBean#<null>]
DEBUG [org.hibernate.type.LongType] binding '3' to parameter: 1
DEBUG [org.hibernate.type.StringType] binding 'http://www.google.fr' to parameter: 2
DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement
DEBUG [org.hibernate.transaction.JDBCTransaction] rollback
DEBUG [org.hibernate.transaction.JDBCTransaction] rolled back JDBC Connection
DEBUG [org.hibernate.jdbc.JDBCContext] after transaction completion
DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
DEBUG [org.hibernate.connection.DriverManagerConnectionProvider] returning connection to pool, pool size: 1
DEBUG [org.hibernate.impl.SessionImpl] after transaction completion
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.AbstractEntityPersister.insert(AbstractEntityPersister.java:1970)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2405)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:37)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:269)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:167)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:101)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:530)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:518)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:514)
at fr.digimind.evolution.dataaccess.DBMappingImpl.save(DBMappingImpl.java:132)
at fr.digimind.dataaccess.test.DatabaseConnectionTest.main(DatabaseConnectionTest.java:44)
DEBUG [org.hibernate.impl.SessionImpl] disconnecting session
DEBUG [org.hibernate.jdbc.ConnectionManager] connection already null in cleanup : no action
Name and version of the database you are using:
MySQL DB 5.0 using InnoDB engine
[b]the Stored Procedure[b]
CREATE PROCEDURE `insert_IndexNews`( in idSite int, in url varchar(300))
BEGIN
declare _count int;
select count(*) into _count from newsindex i
where i.id_Site = idSite and i.Url_NI = url;
IF (_count = 0)
THEN insert into newsindex (Id_Site, Url_NI) values (idSite, url);
select last_insert_id();
ELSE select 0;
END IF;
END
When I execute this SP on the query browser, the resultset returned contains the last inserted id.
Am I missing somethig?
Thx.
Login
FAQ
Search
