Hi all,

I have the setup in my box: Hibernate 3.2.1, Postgres 8.1, jboss4.05 GA

I overwrite the insert sql string by using the following declaration in the hibernate mapping file.

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

The following is the stored procedure writing in PL/pgsql

CREATE OR REPLACE FUNCTION "public"."insertprocessfromprocesstrans" (var_version integer, var_group_name varchar, var_customer_duns varchar, var_customer_name varchar, var_error_reason text, var_error_type varchar, var_partner_duns varchar, var_partner_name varchar, var_pip_name varchar, var_pip_version varchar, var_process_end_time timestamptz, var_process_id varchar, var_process_start_time timestamptz, var_process_status varchar, var_response_doc_no varchar, var_request_doc_no varchar, var_process_instance_uid bigint, var_is_process_success boolean, var_is_initiator boolean, var_user_tracking_id varchar, var_process_trans_uid varchar) RETURNS "pg_catalog"."void" AS
$body$
/* New function body */
DECLARE
process_trans_record RECORD;
BEGIN
SELECT INTO process_trans_record * FROM isat_process_transaction AS pt WHERE pt.process_instance_uid=var_process_instance_uid;
IF FOUND THEN
-- invoke updateFromProcessTrans
RAISE NOTICE 'Existed process trans found with UID %',var_process_instance_uid;
SELECT updateProcessFromProcessTrans(var_process_instance_uid, var_process_start_time, var_process_end_time, var_process_status, var_error_type, var_error_reason, var_process_id, var_is_process_success);
ELSE
-- INSERT
BEGIN
INSERT INTO isat_process_transaction(process_id, process_start_time, process_end_time, process_status, error_type, error_reason, process_instance_uid, is_process_success, uid)
VALUES (var_process_id, var_process_start_time, var_process_end_time, var_process_status, var_error_type, var_error_reason, var_process_instance_uid, var_is_process_success, var_process_trans_uid);
EXCEPTION WHEN UNIQUE_VIOLATION THEN
RAISE NOTICE 'Another thread inserted the process trans with UID %',var_process_trans_uid;
SELECT updateProcessFromProcessTrans(var_process_instance_uid, var_process_start_time, var_process_end_time, var_process_status, var_error_type, var_error_reason, var_process_id, var_process_success);
END;
END IF;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Basically, the above stored procedure try to insert the record into table. If a unique violation occured while inserting the record, an update will be performed instead.


While hibernate trying to commit, the following error occured

expected.
[26:04:07-15:39:17,750] [ WARN:org.hibernate.util.JDBCExceptionReporter] [JMS SessionPool Worker-125] SQL Error: 0, SQLState: 0100E
[26:04:07-15:39:17,750] [ERROR:org.hibernate.util.JDBCExceptionReporter] [JMS SessionPool Worker-125] A result was returned when none was expected.
[26:04:07-15:39:17,750] [ERROR:org.hibernate.event.def.AbstractFlushingEventListener] [JMS SessionPool Worker-125] Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.CacheSynchronization.beforeCompletion(CacheSynchronization.java:59)
at org.jboss.tm.TransactionImpl.doBeforeCompletion(TransactionImpl.java:1491)
at org.jboss.tm.TransactionImpl.beforePrepare(TransactionImpl.java:1110)
at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:324)
at org.jboss.ejb.plugins.TxInterceptorCMT.endTransaction(TxInterceptorCMT.java:501)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:411)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:181)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:168)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:136)
at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
at org.jboss.ejb.Container.invoke(Container.java:954)
at sun.reflect.GeneratedMethodAccessor763.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.invocation.local.LocalInvoker$MBeanServerAction.invoke(LocalInvoker.java:169)
at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:118)
at org.jboss.invocation.InvokerInterceptor.invokeLocalMarshalled(InvokerInterceptor.java:295)
at org.jboss.invocation.MarshallingInvokerInterceptor.invoke(MarshallingInvokerInterceptor.java:61)
at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:61)
at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:70)
at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSessionInterceptor.java:112)
at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:100)


As can be seen in the log, it indicate 'A result was returned when none was expected', but in the stored procedure, i didn't return anything.


Could i know why it will happen and how could i recover from the error?



I tried another approach to achieve the similiar task wihch is using the sql-query for stored procedure

here is the mapping file

<sql-query callable="true" name="insertProcessFromProcessTrans">
<return-scalar type="string" column="uid"/> <![CDATA[{call insertProcessFromProcessTrans(:processID, :processStartTime, :processEndTime, :processStatus, :errorType, :errorReason, :processInstanceUID, :isProcessSuccess,:uid)}]]>
</sql-query>

the following is the stored procedure. What it does is inserting record into table

DECLARE
process_trans_record RECORD;
update_process_trans_cmd TEXT;
BEGIN
SELECT INTO process_trans_record * FROM isat_process_transaction AS pt WHERE pt.process_instance_uid=var_process_instance_uid;
IF FOUND THEN
-- invoke updateFromProcessTrans
RAISE NOTICE 'Existed process trans found with UID %',processInstanceUID;
ELSE
-- INSERT
BEGIN
INSERT INTO isat_process_transaction(process_id, process_start_time, process_end_time, process_status, error_type, error_reason, process_instance_uid, is_process_success, uid)
VALUES (var_process_id, var_process_start_time, var_process_end_time, var_process_status, var_error_type, var_error_reason, var_process_instance_uid, var_is_process_success, var_process_trans_uid);
EXCEPTION WHEN UNIQUE_VIOLATION THEN
RAISE NOTICE 'Another thread inserted the process trans with UID %',processTransUID;
END;
END IF;

RETURN 0;
END;


I encountered the following error while hibernate trying to execute the stored procedure

Caused by: java.lang.UnsupportedOperationException: org.hibernate.dialect.PostgreSQLDialect does not support resultsets via stored procedures
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.dialect.Dialect.registerResultSetOutParameter(Dialect.java:939)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.loader.Loader.doQuery(Loader.java:661)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.loader.Loader.doList(Loader.java:2144)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.loader.Loader.list(Loader.java:2023)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
[25:04:07-20:27:00,156] [ERROR:STDERR] [Timer-2] at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
[25:04:07-20:27:00,171] [ERROR:STDERR] [Timer-2] at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
[25:04:07-20:27:00,171] [ERROR:STDERR] [Timer-2] at util.db.DAO.query(DAO.java:208)

is it true that Hibernate is not supporting the invocation on the pg stored procedure? how can I resolve it ?

Thanks in advance