How to access Stored Procedure

[jacob selvaraj]

hi
I am very new to Hibernate. I am writing DAOs using Hibernate to access a complex SP which is having 17 IN Params 4 Out Params and and the Result Set contains 6 param. ( SP IN/Out param is mentioned below)
As of my knowledge we will be having Hibernate Mapping file which will contain mapping to input/output parameters to its corresponding column name in table.
Consider The Store Procedure name is XXX. Please let me know how to call this SP. The return type wil be Array of values and the input is DocSearchParams

Store Procedure Structure:
----------------------------------------------------------------------------
Sl NoIn/Out SizeView Name
----------------------------------------------------------------------------
1INCODE CHAR(2)
2INLOGCL CHAR(10)
3INSCHD_CDE CHAR(1)
4INENV CHAR(1)
5INUSER CHAR(8)
6INTYPE_1CHAR(2)NVIA0080801
7INVAL_1_LVARCHAR (255)NVIA0080801
8INTYPE_2CHAR(2)YVIA0080801
9INVAL_2_LVARCHAR (255)YVIA0080801
10INI__TPE_LVARCHAR (255)YVIA0080801
11INI__DATEDATEYVIA0080501
12INB_DATEDATEYVIA0080501
13INI_FPECHAR(2)YVIA0080501
14OUTO_TEC_INTEGERNn/a
15OUTRETURNED-RES_SETSSMALLINT
16OUTRESULTS_INFO_BLKCHAR(200)
17OUTRETURN_CODESMALLINT
18OUTRETURN_MSG_NUMSMALLINT
19OUTERROR_INTERFACE

1.1.1Result Set

Field Name SQL TypePosition AllowsCI Table View Data Field in DB
DID DEC(15)1N0501 DT_ID
DATE DATE2N0501 DDATE
DTYPE CHAR(2)3N0801 D_TYP
M_TYPE CHAR(2)4N0501 MPE
DI_TYPE CHAR(2)5N0501 DPE
USATE Time Stamp6N0801 CRMESTAMP

Can i write a mapping file like this
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<sql-query name="SP0039_SP" callable="true">
<return alias="documentsVO"
class="com.program.fina.DocumentsVO">
</return>
{ call XXX(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }
</sql-query>
</hibernate-mapping>
1) hibernate Gurus Please help me to solve this issue.
2) Please let me know how to access the result set from DAO.
Regards
S.Jacob
[ July 25, 2006: Message edited by: jacob selvaraj ]

[Scott Johnson]

You can't call this procedure as a Hibernate named query.

Read the Hibernate Reference section 16.3.2 for more details.

If you rewrote the procedure to return just a result set, it would work fine.

Or you can get a reference to Hibernate's Connection object and execute the procedure that way.

[Mark Spritzler]

Also the rules for Hibernate calling stored procedures.

1. Only one out parameter allowed
2. the out parameter must be the first parameter
3. It also must be a reference cursor.

Mark

[jacob selvaraj]

Thanks Scott and Mark for your replies.
The problem is I am Just Writing an interface which is Suppose to use that Stored Procedure. The Stored Procedure is written by third party.
1) Please let me know the way to access the above Stored Procedure with out changing the Stored Procedure. ( With Syntax Possible)
2) Please let me know what the configuration parameter in Hibernate Mapping file.

Regards
S.Jacob

[jacob selvaraj]

Hi
Can i Call Stored Procedure like below as you said.
--------------------------------------------
session = HibernateUtil.getInstance(CONFIG_FILE_LOCATION).getCurrentSession();
getIndexTrans = session.beginTransaction();
CallableStatement callableStatement = session.connection().prepareCall("{call SP0039(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
callableStatement.setString("FUNCTION_CODE", emptyString(2));
callableStatement.setString("LOGCL_RUN_DTE", emptyString(NUM_TEN));
callableStatement.setString("SCHD_CMPNT_CDE", emptyString(1));
callableStatement.setString("ENV_TYPE_CDE", emptyString(1));
callableStatement.setString("USER_ID", emptyString(NUM_EIGHT));
callableStatement.commit();
Regards
S.Jacob

[Scott Johnson]

CallableStatement doesn't have a commit() method. You should call executeUpdate() to run the sql.

[jacob selvaraj]

Hi all,

Here is the DAO to call SP.
1) After doing a preparecall i am setting input parameter.
callableStatement.setString(FUNCTION_CODE, " ");
But i am getting the below Exception in the above setString Statement.
----------
Exception:
----------
com.ibm.db2.jcc.b.SqlException: JDBC 3 method called - not yet supported
at com.ibm.db2.jcc.b.bd.setString(bd.java:961)
at org.apache.commons.dbcp.DelegatingCallableStatement.setString
(DelegatingCallableStatement.java:221)
Please help me in this regard.


Version:
--------
Hibernate 3.0
DB2 Version 8.1.6.4

DAO CODE:
-----------
try {
session = HibernateUtil.getInstance(CONFIG_FILE_LOCATION).getCurrentSession();
getIndexTrans = session.beginTransaction();
try {
CallableStatement callableStatement = session.connection().prepareCall("{call DB2IAA1.SP0044(?,?,?,?,?)}");

callableStatement.setString(FUNCTION_CODE, " ");
callableStatement.setString(LOGCL_RUN_DTE, emptyString(NUMBER_TEN));
callableStatement.setString(SCHD_CMPNT_CDE, emptyString(1));
callableStatement.setString(ENV_TYPE_CDE, emptyString(1));
callableStatement.setString(USER_ID, emptyString(NUMBER_EIGHT));

callableStatement.setString(I_FUNCTION_CODE, FUNC_CODE_UPDATE);
callableStatement.setLong(I_DOCUMENT_TYPE, usageParams.getDocumentId());
callableStatement.setString(I_CASE_ID, usageParams.getCaseId());
callableStatement.setString(I_LOB_SYSTEM_TYPE, "VALUE FROM CVT");
callableStatement.setString(I_DOCUMENT_TYPE, usageParams.getDocumentUsageContextType());
callableStatement.setString(I_NEW_DOC_TYPE, emptyString(2));
callableStatement.setString(I_ACF_ID, emptyString(NUMBER_SEVEN));
callableStatement.setString(I_CASE_SUBJECT_TYP, assignStringValue(usageParams.getCaseSubjectType(), 2));
callableStatement.setString(I_CASE_SUBJECT_VAL, assignStringValue(usageParams.getCaseSubjectValue(), LENGTH_TWENTY_THREE));
callableStatement.setString(I_BSNS_PROC_TYPE, assignStringValue(usageParams.getBusinessProcessType(), NUMBER_FOUR));
callableStatement.setString(I_NEW_BSNS_PROC_TP, emptyString(NUMBER_FOUR));
callableStatement.registerOutParameter(RETURNED_RES_SETS, Types.SMALLINT);
callableStatement.registerOutParameter(RESULT_INFO_BLK, Types.CHAR);
callableStatement.registerOutParameter(RETURN_CODE, Types.SMALLINT);
callableStatement.registerOutParameter(RETURN_MSG_NUM, Types.SMALLINT);
callableStatement.registerOutParameter(ERROR_INTERFACE, Types.CHAR);
callableStatement.execute();
callableStatement.getInt(RETURNED_RES_SETS);
callableStatement.getString(RESULT_INFO_BLK);
returnCode = callableStatement.getInt(RETURN_CODE);
raiseReturnException(returnCode, methodName);
callableStatement.getInt(RETURNED_RES_SETS);
callableStatement.getString(ERROR_INTERFACE);

}
catch (SQLException e) {
Log.debug(e, "Started printStackTrace");
e.printStackTrace();
Log.debug(e.getLocalizedMessage(), "");
System.out.println(e.getErrorCode());
Log.debug(e, "End printStackTrace");
}

getIndexTrans.commit();
}
catch (HibernateException he) {
if (getIndexTrans != null && getIndexTrans.isActive()) {
if (session != null && session.isConnected()) {
getIndexTrans.rollback();
}
}
/*
* Only raise an alert for a - Check for Error Code = -1,041
*/
if (he.getCause() != null) {
alertDBConnectionFailure(he);
}
raiseException(methodName, he, OACR_HIBERNATE_EXCEPTION);
}
[ August 08, 2006: Message edited by: jacob selvaraj ]

[Scott Johnson]

Looks like your DB2 JDBC driver doesn't support JDBC 3.0.

Upgrade to the latest driver and try again.