-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 posts ] 
Author Message
 Post subject: Issues with oracle stored procedures
PostPosted: Wed Nov 15, 2006 1:21 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
I'm facing some issues whenever i invoke oracle stored procedures.

Please let me know whether it supports oracle stored procedures.

Without stored procedures I could carry out CRUD Functionality. But I want to use stored procedures.

By default, command type is Text and the parameter names are p0, p1, p2 and so on. If I change the values of command type and parameter name at run time I could invoke oracle stored procedures.

Thanks in advance. Waiting for your feedback at the earliest.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 15, 2006 2:04 am 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
I don't have Oracle installed so I can only provide a few guesses based on Internet searches. Try this:
Code:
<sql-insert>execute mySqlInsert(?, ?, ?);</sql-insert>

The usual restrictions apply - the number of parameters must match the number of columns, the parameters must be in order NHibernate expects them, etc.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 15, 2006 5:20 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
The statement and the order of the parameters are fine. If there are any issues in those areas I could not invoke the stored procedure after I have changed the commandtype to storedprocedure and the parameter names at run time (Using QuickWatch).

By default the commandtype is taking as CommandType.Text and the parameter names are taking as p0, p1 and so on. The problem lies in this area only. To support oracle stored procedure the commandtype should be storedprocedure and the parameter names should be as per the mapping file.

Is there any option in the mapping or config file to say that it should change the commandtype and the parameter name instead of default values?

Thanks in advance.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 15, 2006 5:34 am 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
No, there isn't any such option, because it was too complex to implement at the time.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 17, 2006 9:44 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
The problem i was facing while invoking the stored procedure has been fixed. For oracle, call keyword should be used to invoke the procedure and the parameter names should be given in bracket and oracle.dataaccess should be used instead of microsoft oracle driver. Thanks a lot for your response.

Please help me out from the following issue.

Find below the stored procedure, mapping file and the class file. In the GETBIll stored procedure, i'm returning refcursor as output and billid is my input parameter.
I have given the output parameter as the first parameter as per rules. I want to know whether i'm calling the procedure in a right way in the mapping file as well as i would like to know how to invoke this procedure in front end.

Stored Procedure
CREATE OR REPLACE PROCEDURE GETBILL(
RETVAL OUT SYS_REFCURSOR,
BillID IN BILL_POC.BILL_ID%type
)
IS
BEGIN
OPEN RETVAL FOR 'SELECT BILL_DESC,STARTDATE FROM BILL_POC WHERE BILL_ID = :0' using BillID;

END GETBILL;
/

Mapping file

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns:xsd='http://www.w3.org/2001/XMLSchema'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xmlns='urn:nhibernate-mapping-2.0'
namespace="NHibernatePOB" assembly="NHibernatePOB" default-lazy="false">
<class name="NHibernatePOB.Bill" table="BILL_POC">
<id name="BillID" type="Int32" unsaved-value="null">
<column name="BILL_ID" sql-type="Integer" not-null="true" unique="true" />
<generator class="assigned" />
</id>
<property name="BillDesc" type="String">
<column name="BILL_DESC" length="20" sql-type="VARCHAR2" not-null="true" />
</property>
<property name="BillDate" type="DateTime">
<column name="STARTDATE" sql-type="DATE" not-null="true"/>
</property>
<loader query-ref="GETBILL"/>

<sql-insert >
Call INSERTBILL (:BillDesc,:BillDate,:BillID)
</sql-insert>

<sql-update>
Call UPDATEBILL (:BillDesc,:BillDate,:BillID)
</sql-update>

<sql-delete>
Call DELETEBILL (:BillID)
</sql-delete>
</class>

<sql-query name="GETBILL">
<return class="NHibernatePOB.Bill">
<return-property name="BillDesc" column="BILL_DESC"></return-property>
<return-property name="BillDate" column="STARTDATE"></return-property>
</return>
Call GETBILL (:Bill,:BillID)
</sql-query>
</hibernate-mapping>

Class file
using System;
using System.Collections.Generic;
using System.Text;

namespace NHibernatePOB
{
public class Bill
{
private int _BillID;

public int BillID
{
get { return _BillID; }
set { _BillID = value; }
}
private string _BillDesc;

public string BillDesc
{
get { return _BillDesc; }
set { _BillDesc = value; }
}
private DateTime _BillDate;

public DateTime BillDate
{
get { return _BillDate; }
set { _BillDate = value; }
}
}
}


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 17, 2006 10:18 am 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
malar wrote:
I want to know whether i'm calling the procedure in a right way in the mapping file as well as i would like to know how to invoke this procedure in front end.


Sorry, I don't have much experience with Oracle so I can't tell whether it's right or not. In general, your procedure should behave just like a SELECT statement from the perspective of ADO.NET. That is, creating an IDbCommand, setting its CommandText to "Call GETBILL (:Bill,:BillID)" and calling ExecuteReader() should return an IDataReader with the necessary results.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 20, 2006 7:40 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
I have changed the sql-query syntax as follows
<sql-query name="GETBILL">
<return class="NHibernatePOB.Bill" >
<return-property name="BillID" column="BILL_ID" ></return-property>
<return-property name="BillDesc" column="BILL_DESC"></return-property>
<return-property name="BillDate" column="STARTDATE"></return-property>
</return>
:RETVAL = Call GETBILL (:BillID)
</sql-query>

and try to invoke the procedure as follows.
Bill obj1 = new Bill();
obj1.BillID = 2;
Bill obj = new Bill();
using (ISession session = PersistenceManager.NHSession)
{
IQuery qr = (IQuery) session.GetNamedQuery "GETBILL");
qr.SetParameter("BillID",obj1.BillID);
qr.SetParameter("RETVAL", obj);
qr.List<Bill>();
}

When i look into the command object at run time, command text has been changed like {:p0} = Call GETBILL ({:p1}) and both parameter directions are input. In my procedure RETVAL is the output parameter and BillID is the input parameter. Can i use IQUERY to invoke the stored procedure . Is there any option to specify the parameter direction and pararameter type.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 20, 2006 10:10 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
Is it possible to call a stored procedure having both input and output parameter?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 20, 2006 10:34 am 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
No, it's not possible to use OUT parameters. Again, the procedure must behave exactly as NHibernate-generated SQL would.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 21, 2006 3:17 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
Is there any other way i could return the result set without using OUT param. Please help me out.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 22, 2006 9:40 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
I'm using IQuery and limited the pagesize to fetch the data.
ISession session = PersistenceManager.NHSession;
IQuery qr1 = session.CreateQuery("From Bill");
qr1.SetMaxResults(2);
qr1.SetFirstResult(1);
IList<Bill> lst = qr1.List<Bill>();

But i'm getting the following error. Please help me out. I'm using nHibernate version 1.2 , .net 2.0 and oracle database. There is no stored procedure. Directly trying to fetch the database.

{"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index"}


Please correct my following understanding.

SetMaxResult is used to set the page size. Suppose I'm giving 10 it will display only records per page. SetFirstResult is used to specify the row no. from which we need to fetch the record. Suppose i'm giving 1, it will fetch 1-10 records and if i change the value of setfirstresult to 11, it will fetch from 11-20. Pls. correct me if I’m wrong.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 22, 2006 10:06 am 
Newbie

Joined: Mon Mar 27, 2006 10:24 am
Posts: 8
Location: Malmoe, Sweden
Check this out http://forum.hibernate.org/viewtopic.php?t=967268&highlight=maxresults dont know if its related

/Floda


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 23, 2006 1:53 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
In my project I have to call the stored procedures via nhibernate and all the procedures are having OUT parameters. Can you suggest me what I can do and is it possible to fix in 1.2 realease. When is version 1.2 getting released? Waiting for your feedback at the earliest.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 27, 2006 6:25 pm 
Newbie

Joined: Wed Aug 16, 2006 8:02 pm
Posts: 11
I second that


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 28, 2006 8:54 am 
Newbie

Joined: Wed Nov 01, 2006 10:54 am
Posts: 14
In Beta2 when Isession gets activated , transaction will be enabled but not in Beta1. Please correct me If i'm wrong.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.