Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Weired error comes while using stored procedure with hibernate

 
hello kumar d
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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
 
anuj patel
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

You can try with the following code from your DAO.

hope this might help you,

Thanks,
Anuj
 
Adrian Johnston
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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();
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"hello kumar d", please check your private messages.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic