This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell 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


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
Author

Weired error comes while using stored procedure with hibernate

hello kumar d
Greenhorn

Joined: Sep 20, 2008
Posts: 1
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

Joined: Jan 02, 2008
Posts: 10
Hi,

You can try with the following code from your DAO.

hope this might help you,

Thanks,
Anuj
Adrian Johnston
Greenhorn

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);
query.executeUpdate();
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Weired error comes while using stored procedure with hibernate