This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes Other Application Frameworks and the fly likes Calling stored procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Frameworks » Other Application Frameworks
Bookmark "Calling stored procedures" Watch "Calling stored procedures" New topic

Calling stored procedures

Saravanan Subramani

Joined: Feb 27, 2008
Posts: 12
Hi ,

I am new in hibernate. What i want to do is, I have to call a procedure.
without specifying the column names in mapping file.

My procedure accept 2 input parameters, it generate a unique number and return it as out parameter. In this scenario,i dont know How to map and how to write the code.

this is my procedure,


select swith_id into OUTBATCHID from ps_async
where h1sd_xb_exec_oprid = ID and trunc(send_date) = trunc(sysdate);

when no_data_found then
select h1sd_xb_batch_seq.nextval into OUTID from dual;
insert into ps_async values(ID,sysdate,OUTID);
insert into xb_batch values (OUTID,INLABEL,'NEW',ID,' ',0,trunc(sysdate),ID,trunc(sysdate),ID);

My procdure is referring 2 table. I saw so many samples like

<sql-query name="selectAllEmployees_SP" callable="true">
<return alias="emp" class="employee">
<return-property name="empid" column="EMP_ID"/>

<return-property name="name" column="EMP_NAME"/>
<return-property name="address" column="EMP_ADDRESS"/>
{ ? = call selectAllEmployees(?,?) }

I think these are not needed in my requirement.what i want to do is just send 2 string parameters and get out parameter. i dont know how to set return-property for my requirement.

Please suggest me how to do that,

Thanks in advance
[ July 17, 2008: Message edited by: ssaravanan saravanan ]

Joe Matthew
Ranch Hand

Joined: Jun 10, 2008
Posts: 66
Though not recommended, use a cursor to get the result set you require:

CallableStatement callableStatement = null;

callableStatement = session.connection().prepareCall("Your SP name");

callableStatement.setString(1, "aaaa");



ResultSet rs = (ResultSet)callableStatement.getObject(2)

[ June 19, 2008: Message edited by: Joe Mathew ]

Regards,<br />Joe<br /> <br />"Always program as if the person who will be maintaining your program is a violent psychopath that knows where you live."<br />--Martin Golding
Saravanan Subramani

Joined: Feb 27, 2008
Posts: 12
Thanks for reply.
I used mapping with following way,
<sql-query name="FETCHBATCHID" callable="fale">
{ call PSFTCTE.common.TASKID(?) }

Query query = session.getNamedQuery("FETCHBATCHID").setParameter(0, userId);
int batId = query.executeUpdate();

i changed callable="false" in mapping file.

procedure is executing finely.but another problem is, when i call a procedure that return a out parameter, i am getting error.

mapping like this,

<sql-query name="FETCHBATCHID" callable="false">
{? = call PSFTCTE.xmlbatch_common.FETCHTASKID(?,?) }

Query query = session.getNamedQuery("FETCHBATCHID").setParameter(0, userId).setParameter(1, label);
int batId = query.executeUpdate();

I don't know how to write for get retrun out parameter.

Following Error is throwing ORA-01008: not all variables bound)

Hibernate: {? = call PSFTCTE.xmlbatch_common.FETCHTASKID(?,?) }
org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(
at org.hibernate.exception.SQLStateConverter.convert(
at org.hibernate.exception.JDBCExceptionHelper.convert(
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(
at org.hibernate.impl.SessionImpl.executeNativeUpdate(
at org.hibernate.impl.SQLQueryImpl.executeUpdate(
at com.orderengine.logic.TestDatabase.createBatchId(
at com.orderengine.logic.TestDatabase.main(
Caused by: java.sql.SQLException: ORA-01008: not all variables bound

at oracle.jdbc.dbaccess.DBError.throwSqlException(
at oracle.jdbc.ttc7.TTIoer.processError(
at oracle.jdbc.ttc7.Oall7.receive(
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(
at oracle.jdbc.driver.OracleStatement.executeNonQuery(
at oracle.jdbc.driver.OracleStatement.doExecuteOther(
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(
... 4 more

I can use what you suggesed.But i would like to know why my code is not working when there is a return out parameter & where i made mistake.

Thanks in advance..
Saravanan Subramani

Joined: Feb 27, 2008
Posts: 12
My procedure will be return a integer object. not like a row.

i have problems with the return parameters of the stored procedure.

Thanks, I appreciate any help.
Joe Matthew
Ranch Hand

Joined: Jun 10, 2008
Posts: 66
are you registering the out variable before the SP is executed?
Saravanan Subramani

Joined: Feb 27, 2008
Posts: 12
Thanks for reply,

I dont know how to register the out variables before the SP is executed

Joe Matthew
Ranch Hand

Joined: Jun 10, 2008
Posts: 66
LinkThis will help you out.

But the previous post solution will help does not have any constraints other than it uses a deprecated method.
[ June 25, 2008: Message edited by: Joe Mathew ]
Have you checked out Aspose?
subject: Calling stored procedures
It's not a secret anymore!