Hi,

I am using hibernate with postgreSQL db.

We had a very long query in which more then 10 tables joining conditions were used.
so to make the code cleaner it was decided that to move that query into stored procedure and call that stored procedure from hibernate DAO classes.

Following is a snippet of our procedure.

CREATE OR REPLACE FUNCTION "public"."delete_special_records" (cId1 TEXT,cid TEXT) RETURNS void AS
'
DECLARE
BEGIN
DELETE FROM "table1" WHERE ("Cid" = cId1) AND
,............)
RETURN;
END;
'
LANGUAGE 'plpgsql';

Following is the code for calling the stored procedure in mapping file.
Table1.hbm.xml
<hibernate-mapping>
<class>
..... DAO Table classes's attributes....
</class>
<sql-query name="deleteSpecialRecord_SP" callable="false">
{? = call delete_special_records(?, ?) }
</sql-query>
</hibernate-mapping>


And following is the code from where above procedure is getting called.
Query query = getSession().getNamedQuery("deleteSpecialRecord_SP");
query.setParameter(1, cId1);
query.setParameter(2, cId2);
query.executeUpdate();



But when I run with above code , it gives me following error.
2008-09-20 04:00:26,405 WARN [org.hibernate.util.JDBCExceptionReporter]
SQL Error: 0, SQLState: 22023
2008-09-20 04:00:26,405 ERROR [org.hibernate.util.JDBCExceptionReporter]
No value specified for parameter 3.
2008-09-20 04:00:26,408 ERROR
[com.psp.fsv.command.AbstractCommand] Failed to execute command Caused by: org.postgresql.util.PSQLException: No value specified for parameter 3.
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:146)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:184)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:305)
at sun.reflect.GeneratedMethodAccessor75.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:477)
at $Proxy39.executeUpdate(Unknown Source)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:95)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:165)
... 50 more
2008-09-20 04:00:26,417 WARN
DeleteSpecialRecord: { command =



Can anybody please help me?
- Regards,
Kumar

Stored procedures no longer require OUT parameter
In the initial stored procedure support for sql-insert, sql-update, and sql-delete Hibernate required that the stored procedures define an out parameter which returned the affected row count to be able to tie into Hibernate's normal staleness checks. Starting with 3.2, this is no longer a requirement, although it is still supported for backwards compatibility. The expectation here is governed by a new attribute check attribute on these mapping elements. The possible attributes are none, count, and param.

none - specifies absolutely no checking should be performed. This is the recommended option for stored procedure support moving forward. The assumption here is that your stored procedure code performs the pertinent checks and propogates issues via SQLExceptions.

count - perform checking based on the results returned by the JDBC operations; this is how Hibernate operates when stored procedured are not invloved.

param - this is the legacy behaviour for stored procedure support. Essentially the same as check except that here the count value comes from the registered OUT parameter.

i guess you are using old version