aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate and DB2 stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate and DB2 stored procedure" Watch "Hibernate and DB2 stored procedure" New topic
Author

Hibernate and DB2 stored procedure

S Setty
Greenhorn

Joined: Sep 08, 2008
Posts: 16
Hi all

I am new to hibernate. we have a custom ORM tool similar to hibernate. I am currently evaluating hibernate to see if we can migrate.

I am trying to invoke a db2 stored procdure.

GET_TXNS( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

The first 7 params are in and the last 3 are out.

My hbm.xml is like this

<sql-query name="stmtTransactionsSP" callable="true">
<return alias="stmtTransactions" class="domain.StatementTransactionVO">
<return-property name="sequenceNumber" column="CID_SD1_SEQ_NO"/>
<return-property name="postedDate" column="STMTRETR_RETAIL_POST_DATE"/>
<return-property name="transactionDate" column="STMTRETR_RETAIL_TRANS_DATE"/>
<return-property name="amount" column="STMTRETR_RETAIL_AMT"/>
<return-property name="authorizedAmount" column="STMTRETR_PS2000_AUTHRIZ_AMT"/>
<return-property name="transactionType" column="STMTRETR_RETAIL_TYPE"/>
</return>
{CALL DB2.GET_TXNS( :userID, 1, 2, :accountNumber, :startDate, :endDate, :applID, ?, ?, ?) }
</sql-query>

Here is the code i am using to invoke this.

Session session = HibernateUtil.getSessionFactory().getCurrentSession();

session.beginTransaction();

Query stmtQuery = session.getNamedQuery("stmtTransactionsSP");

stmtQuery.setParameter("userID", "90EAS001");
stmtQuery.setParameter("prodAcctNo", 0);
stmtQuery.setParameter("creditAcctNo", 0);
stmtQuery.setParameter("accountNumber", serviceRequest.getAccountNumber());
stmtQuery.setParameter("startDate", serviceRequest.getFromDate().getSQLDate());
stmtQuery.setParameter("endDate", serviceRequest.getToDate().getSQLDate());
stmtQuery.setParameter("applID", "IV");

//Out parameters
//stmtQuery.setParameter(0, null);
//stmtQuery.setParameter(1, null);
//stmtQuery.setParameter(2, null);

statementTransactions = stmtQuery.list();

session.getTransaction().commit();

I get this exception

org.hibernate.QueryException: Expected positional parameter count: 2, actual parameters: [] [{CALL TCPUG.GET_STMT_TXN_HIST( :userID, 1, 2, :accountNumber, :startDate, :endDate, :applID, ?, ?, ?) }]

If i remove the comment to set out parameters

com.ibm.db2.jcc.c.SqlException: THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMBER CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE

Is this error because of the out parameters, how to handle this.

Can some one help?

Thanks
Santosh
 
 
subject: Hibernate and DB2 stored procedure