Hi,

I am newly learning the hibernate concepts.
Can anyone help me by sending an example for calling a stored procedure using hibernate 3.0

Thanks in advance,
Kanthu

try to read this
http://www.hibernate.org/hib_docs/v3/re ... tml_single
16.2.2. Using stored procedures for querying

Thanks a lot for ur reply.

The example which is given in 16.2.2 i was able to execute it.

Can u please tell how to call a stored procedure with parameters.

For example: 2 input parameters and 1 output parameter,
(or) 1 input parameter with a list of output values, etc....

Please tell me whether it is possible or not.

Once again thanks a lot for ur reply.

Thanks & Regards,
Kanthu.

If you are using Oracle then try something like this:

{ call PROCEDURE_NAME(?, :param1, :param2) }

The first param (?) is output param (resultSet)

If non Oracle db then use

{ ? = call PROCEDURE_NAME(:param1, :param2) }

Actually, I haven't used this yet. I've only read this.

Thanks for the immediate reply.

I also have read it and i try to implement but wasn't able to implement it.

If you have any excecuted code then please let me know.

Thanks and Regards,
kanthu.

Does hibernate provide support to register out parameters

if i have some thing like this in my hbm.cfg file

<sql-query name="mySproc" callable="true>

...

{call GET_EMPLOYEE_DETAILS(:empID,?,?)}
</sql-query>

Here the last 2 parameters are out type. How do invoke this procedure from the java code?

Query q = sesseion.getNamedQuery("mySproc");
q.setParameter("empID","123");
//Not sure how to register out parameters...??

I tried everything in my limits to get this working with no luck.

Appreciate if any one can answer.

Thanks

me too faced the same problem.....

Can anyone help it out.............

Thanks and Regards,
kanthu.

Hibernate allows only 1 out param

see http://www.hibernate.org/hib_docs/v3/re ... l#sp_query

16.2.2.1. Rules/limitations for using stored procedures

Hi,
I am trying to execute a stored procedure using hibernate.
Below are the snippet from my mapping file

<sql-query name="checkIfContractExists" callable="true" >
<return-scalar type="long" column="rowupdated"/>
{ ? = call checkIfContractExists(?,?,?) }
</sql-query>

The Java code which I am using to invoke the stored procedure

try{
session = HibernateSessionFactory.currentSession();
Query query = session.getNamedQuery( "checkIfContractExists" );

query.setLong(0, 100344 );
query.setLong(1, 143405);
query.setLong(2, 992);

log.debug("before executing the script");

List list = query.list();
log.debug("after executing the script");

Object count = list.get(0);
}

Stored procedure code

create or replace
PROCEDURE checkIfContractExists
(
o_err_flag out SYS_REFCURSOR,
pi_con_mkt_type_combo_id IN number,
pi_union_agmt_id IN number,
pi_pay_method_code IN NUMBER

) IS

v_RecordCount NUMBER;

v_Error_Flag varchar2(1);
v_ContractCount NUMBER;
v_run_pay_method_code NUMBER;
v_onehr_pay_method_code NUMBER;
v_existing_pay_method_code NUMBER;
v_pay_method_runs_flag char(1) ;



BEGIN

--This procedure checks whether the cotract exists for a given market combination
--IF THE CONTRACT exists then check whether it is a runs or one hour method

SELECT count(*) into v_ContractCount FROM RESD_CON_KEY
WHERE con_mkt_type_combo_id = pi_con_mkt_type_combo_id
AND pi_union_agmt_id = pi_union_agmt_id;

select lookup_code_id into v_run_pay_method_code
from resd_lookup_code where lookup_type='Payment Method'
and lookup_code = 'Runs';

select lookup_code_id into v_onehr_pay_method_code
from resd_lookup_code where lookup_type='Payment Method'
and lookup_code = '1 Hour';

IF NVL(v_ContractCount, 0) = 0 THEN

--SET THE ERROR FLAG AS 1 AS THERE ARE NO CONTRACTS FOR THIS MARKET COMBO AND
--UNION AGREEMENT
o_err_flag := 1;

ELSIF NVL(v_ContractCount, 0) = 1 and (pi_pay_method_code = v_run_pay_method_code or pi_pay_method_code = v_onehr_pay_method_code) THEN
---FETCH THE PAY_METHOD_CODE OF THE ALREADY EXISTING CONTRACT TO SEE IF IT IS RUNS OR ONE HOUR

SELECT pay_method_code INTO v_existing_pay_method_code FROM RESD_CON
WHERE con_key_id IN
(
SELECT con_key_id
FROM resd_con_key
WHERE con_mkt_type_combo_id = pi_con_mkt_type_combo_id
AND pi_union_agmt_id = pi_union_agmt_id
);

IF(pi_pay_method_code = v_run_pay_method_code ) THEN

v_pay_method_runs_flag := 'Y';
ELSIF (pi_pay_method_code = v_onehr_pay_method_code) THEN

v_pay_method_runs_flag := 'N';

END IF ;

IF(v_pay_method_runs_flag ='Y' AND v_existing_pay_method_code = v_onehr_pay_method_code) THEN

o_err_flag := 1;
ELSIF(v_pay_method_runs_flag ='N' AND v_existing_pay_method_code = v_run_pay_method_code) THEN

o_err_flag := 1;
ELSE
o_err_flag := -1;
END IF ;


ELSIF NVL(v_ContractCount, 0) = 2 THEN

-- send the error flag as Y as we already have two contracts for this market and we cannot let the user add more.
o_err_flag := -1;

ELSE
o_err_flag := -1;

END IF;

OPEN o_ref_cursor FOR
SELECT o_err_flag AS rowupdated FROM dual;
RETURN o_ref_cursor;

END;

I am getting the results as null when the stored procedure is invoked from the hibernate but when I run the stored procedure from the database I get the correct results.