This week's giveaway is in the Spring forum.
We're giving away four copies of REST with Spring (video course) and have Eugen Paraschiv on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Weired error comes while using stored procedure with hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Weired error comes while using stored procedure with hibernate" Watch "Weired error comes while using stored procedure with hibernate" New topic

Weired error comes while using stored procedure with hibernate

hello kumar d

Joined: Sep 20, 2008
Posts: 1

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
DELETE FROM "table1" WHERE ("Cid" = cId1) AND
LANGUAGE 'plpgsql';

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

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

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(
at org.postgresql.core.v3.QueryExecutorImpl.execute(
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(
at sun.reflect.GeneratedMethodAccessor75.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(
at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(
at $Proxy39.executeUpdate(Unknown Source)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(
... 50 more
2008-09-20 04:00:26,417 WARN
DeleteSpecialRecord: { command =

Can anybody please help me?
- Regards,
anuj patel

Joined: Jan 02, 2008
Posts: 10

You can try with the following code from your DAO.

hope this might help you,

Adrian Johnston

Joined: Oct 09, 2008
Posts: 1
The relative paramters are zero based so try:

Query query = getSession().getNamedQuery("deleteSpecialRecord_SP");
query.setParameter(0, cId1);
query.setParameter(1, cId2);
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

"hello kumar d", please check your private messages.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
subject: Weired error comes while using stored procedure with hibernate
It's not a secret anymore!