• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Weired error comes while using stored procedure with hibernate

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

You can try with the following code from your DAO.

hope this might help you,

Thanks,
Anuj
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The relative paramters are zero based so try:

Query query = getSession().getNamedQuery("deleteSpecialRecord_SP");
query.setParameter(0, cId1);
query.setParameter(1, cId2);
query.executeUpdate();
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"hello kumar d", please check your private messages.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic