aspose file tools*
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


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Frameworks » Other Application Frameworks
Bookmark "Calling stored procedures" Watch "Calling stored procedures" New topic
Author

Calling stored procedures

Saravanan Subramani
Greenhorn

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,

FUNCTION FETCHID(ID VARCHAR,LABEL VARCHAR) RETURN NUMBER IS
OUTID NUMBER;
BEGIN

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

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

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(?,?) }
</return>
</sql-query>

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
Saravanan.
[ July 17, 2008: Message edited by: ssaravanan saravanan ]

Saravanan
Joe Matthew
Ranch Hand

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

Code:
CallableStatement callableStatement = null;

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

callableStatement.setString(1, "aaaa");

callableStatement.registerOutParameter(2,OracleTypes.CURSOR);

callableStatement.execute();

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

Thanks,
Joe
[ 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
Greenhorn

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

//code
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(?,?) }
</sql-query>

//code
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(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:174)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1163)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:334)
at com.orderengine.logic.TestDatabase.createBatchId(TestDatabase.java:100)
at com.orderengine.logic.TestDatabase.main(TestDatabase.java:36)
Caused by: java.sql.SQLException: ORA-01008: not all variables bound

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:165)
... 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
Greenhorn

Joined: Feb 27, 2008
Posts: 12
Hi
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
Greenhorn

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

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

Thanks.
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling stored procedures