Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate and DB2 stored procedure

 
S Setty
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic