Mailing List Archive: Zope: DB

DCOracle2 with a Stored Procedure that Returns a ref_cur

Index | Next | Previous | View Threaded

mmhamze at pleiades

Mar 16, 2007, 8:44 AM

Post #1 of 6 (2284 views)
Permalink

DCOracle2 with a Stored Procedure that Returns a ref_cur

I am using DCOCralce2 with Python 2.41, and Oracle 9.
I have a stored procedure (sp1) that takes 4 IN parameters, with one OUT
parameter. The OUT parameter is a **ref_cursor** that holds a data set.
I am doing the following:
db = DCOracle2.connection(connectionString)
C1 = db.cursor()
C2 = db.cursor()
#I run the following holding the result into the cursor C2
#since the OUT param is a ref_cur
C2 = C1.sp1(INparam1, INparam2,INparam3,INparam4, ref_cur)

I expect to get a data set
I know there is data when sp1 is run
But I am getting an empty data set when I fetch data via C2 cursor.
Do you have any idea how to make this work when a stored procedure has a
ref_cur OUT parameter?
Thanks,
Maan

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

banaouas.medialog at wanadoo

Mar 16, 2007, 10:04 AM

Post #2 of 6 (2161 views)
Permalink

Re: DCOracle2 with a Stored Procedure that Returns a ref_cur [In reply to]

you can do it like this:

db = DCOracle2.connection(connectionString)
C1 = db.cursor()
C2 = db.cursor()
sql = "sp1(INparam1, :INparam2, :INparam3, :INparam4, :ref_cur)"
C1.execute(sql, (INparam1, INparam2,INparam3,INparam4, C2))
for row in C2:
...

Maan M. Hamze a écrit :
> I am using DCOCralce2 with Python 2.41, and Oracle 9.
> I have a stored procedure (sp1) that takes 4 IN parameters, with one OUT
> parameter. The OUT parameter is a **ref_cursor** that holds a data set.
> I am doing the following:
> db = DCOracle2.connection(connectionString)
> C1 = db.cursor()
> C2 = db.cursor()
> #I run the following holding the result into the cursor C2
> #since the OUT param is a ref_cur
> C2 = C1.sp1(INparam1, INparam2,INparam3,INparam4, ref_cur)
>
> I expect to get a data set
> I know there is data when sp1 is run
> But I am getting an empty data set when I fetch data via C2 cursor.
> Do you have any idea how to make this work when a stored procedure has a
> ref_cur OUT parameter?
> Thanks,
> Maan
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB [at] zope
> http://mail.zope.org/mailman/listinfo/zope-db
> ---------------------------------------------------------------------------------------
> Orange vous informe que cet e-mail a ete controle par l'anti-virus mail.
> Aucun virus connu a ce jour par nos services n'a ete detecte.
>
>
>
>
>
>

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

maciej.wisniowski at coig

Mar 16, 2007, 11:11 AM

Post #3 of 6 (2168 views)
Permalink

Re: DCOracle2 with a Stored Procedure that Returns a ref_cur [In reply to]

> I am using DCOCralce2 with Python 2.41, and Oracle 9.
> I have a stored procedure (sp1) that takes 4 IN parameters, with one OUT
> parameter. The OUT parameter is a **ref_cursor** that holds a data set.
> I am doing the following:
> db = DCOracle2.connection(connectionString)
> C1 = db.cursor()
> C2 = db.cursor()
> #I run the following holding the result into the cursor C2
> #since the OUT param is a ref_cur
> C2 = C1.sp1(INparam1, INparam2,INparam3,INparam4, ref_cur)
>
> I expect to get a data set
> I know there is data when sp1 is run
> But I am getting an empty data set when I fetch data via C2 cursor.
> Do you have any idea how to make this work when a stored procedure has a
> ref_cur OUT parameter?
I've used stored procedures in DCOracle2 but I don't remember if
I was dealing with ref_cursors. I think I did this with SQLRelay.

So do you really have to use DCOracle2?
I see that your code is plain python. You're not using
Zope database adapter here(?) so maybe it is better to use something
that is in active developement like cx_Oracle or SQLRelay? You'll avoid
some problems, eg. DCOracle2 is not 64 bit compatible.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

banaouas.medialog at wanadoo

Mar 17, 2007, 4:39 AM

Post #4 of 6 (2156 views)
Permalink

Re: DCOracle2 with a Stored Procedure that Returns a ref_cur [In reply to]

options = "(2714, " + "'" + "99999999" + "'," + "36, 1, c2)"

it's wrong!
you must give a sequence as second parameter of execute method.

So you do like this:
options = (2714, '99999999',36, 1, c2)
c1.execute(sql, options)

Maan M. Hamze a écrit :
> Hello -
> Thanks for your help. I am still getting errors -
> You wrote:
> sql = "sp1(INparam1, :INparam2, :INparam3, :INparam4, :ref_cur)"
>
> Did you mean:
> sql = "sp1(:INparam1, :INparam2, :INparam3, :INparam4, :ref_cur)"
> (notice :INparam1 instead of INparam1)
>
> Assume sp1 is hrpofficial, INparam1 = 2714, INparam2 = '99999999',
> INparam3 = 36, and INparam4 = 1
>
> db = DCOracle2.connection(connectionString)
> c1 = db.cursor()
> c2 = db.cursor()
> sql = "hrpofficial(:INparam1, :INparam2, :INparam3, :INparam4,
> :ref_cur)"
> options = "(2714, " + "'" + "99999999" + "'," + "36, 1, c2)"
> c1.execute(sql, options)
>
> I am getting an error:
> DatabaseError: (900, 'ORA-00900: invalid SQL statement')
>
> Any hints?
> Thanks again,
> Maan
>
> for row in C2:
> ...
>
> Maan M. Hamze a écrit :
>> I am using DCOCralce2 with Python 2.41, and Oracle 9.
>> I have a stored procedure (sp1) that takes 4 IN parameters, with one
> OUT
>> parameter. The OUT parameter is a **ref_cursor** that holds a data
> set.
>> I am doing the following:
>> db = DCOracle2.connection(connectionString)
>> C1 = db.cursor()
>> C2 = db.cursor()
>> #I run the following holding the result into the cursor C2
>> #since the OUT param is a ref_cur
>> C2 = C1.sp1(INparam1, INparam2,INparam3,INparam4, ref_cur)
>>
>> I expect to get a data set
>> I know there is data when sp1 is run
>> But I am getting an empty data set when I fetch data via C2 cursor.
>> Do you have any idea how to make this work when a stored procedure has
> a
>> ref_cur OUT parameter?
>> Thanks,
>> Maan
>>
>>
>> _______________________________________________
>> Zope-DB mailing list
>> Zope-DB [at] zope
>> http://mail.zope.org/mailman/listinfo/zope-db
>>

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

matt at bane

Mar 20, 2007, 6:17 PM

Post #5 of 6 (2147 views)
Permalink

Re: DCOracle2 with a Stored Procedure that Returns a ref_cur [In reply to]

If I recall, DCOracle2 returns an array of the OUT parameters from
invoking a stored procedure, so your invocation would be more like

c2 = c1.sp1(in1, in2, in3, in4) and you wouldn't pass in ref_cur

but its been a while since I reviewed the code. I know ref cursors
used to work...

On Mar 16, 2007, at 11:44 AM, Maan M. Hamze wrote:

> I am using DCOCralce2 with Python 2.41, and Oracle 9.
> I have a stored procedure (sp1) that takes 4 IN parameters, with
> one OUT
> parameter. The OUT parameter is a **ref_cursor** that holds a data
> set.
> I am doing the following:
> db = DCOracle2.connection(connectionString)
> C1 = db.cursor()
> C2 = db.cursor()
> #I run the following holding the result into the cursor C2
> #since the OUT param is a ref_cur
> C2 = C1.sp1(INparam1, INparam2,INparam3,INparam4, ref_cur)
>
> I expect to get a data set
> I know there is data when sp1 is run
> But I am getting an empty data set when I fetch data via C2 cursor.
> Do you have any idea how to make this work when a stored procedure
> has a
> ref_cur OUT parameter?
> Thanks,
> Maan
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB [at] zope
> http://mail.zope.org/mailman/listinfo/zope-db

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

mmhamze at pleiades

Mar 21, 2007, 1:31 AM

Post #6 of 6 (2159 views)
Permalink

Re: DCOracle2 with a Stored Procedure that Returns a ref_cur [In reply to]

The original issue with stored procedures that return a ref cursor has been
resolved and it is working fine.
As you mention, it was all along:
c2 = c1.procedures.sp1(in1, in2, in3, in4)
I was running into a problem with it, but the problem ended up being not
with the stored procedure, but with the TIMESTAMP data type, as I mention in
another post. DCOracle2 was crashing with a segmentation fault when one
tries to fetch a record from a non empty dataset returned by c2.
Trying the same thing with DCOracle2 on Windows XP, an actual error message
was received that an unknown data type of type SQLT_TIMESTAMP was
encountered. This is how I knew that issue was not with a stored procedure
returning a ref cursor but with the TIMESTAMP datatype.
We are using a new database design in Oracle 10g. The designers chose to
use timestamp instead of date, so the issue arose recently.
Maan

----- Original Message -----
From: "Matthew T. Kromer" <matt [at] bane>
To: "Maan M. Hamze" <mmhamze [at] pleiades>
Cc: <zope-db [at] zope>
Sent: Tuesday, March 20, 2007 8:17 PM
Subject: Re: [Zope-DB] DCOracle2 with a Stored Procedure that Returns a
ref_cur

> If I recall, DCOracle2 returns an array of the OUT parameters from
> invoking a stored procedure, so your invocation would be more like
>
> c2 = c1.sp1(in1, in2, in3, in4) and you wouldn't pass in ref_cur
>
> but its been a while since I reviewed the code. I know ref cursors used
> to work...
>
> On Mar 16, 2007, at 11:44 AM, Maan M. Hamze wrote:
>
>> I am using DCOCralce2 with Python 2.41, and Oracle 9.
>> I have a stored procedure (sp1) that takes 4 IN parameters, with one OUT
>> parameter. The OUT parameter is a **ref_cursor** that holds a data set.
>> I am doing the following:
>> db = DCOracle2.connection(connectionString)
>> C1 = db.cursor()
>> C2 = db.cursor()
>> #I run the following holding the result into the cursor C2
>> #since the OUT param is a ref_cur
>> C2 = C1.sp1(INparam1, INparam2,INparam3,INparam4, ref_cur)
>>
>> I expect to get a data set
>> I know there is data when sp1 is run
>> But I am getting an empty data set when I fetch data via C2 cursor.
>> Do you have any idea how to make this work when a stored procedure has a
>> ref_cur OUT parameter?
>> Thanks,
>> Maan
>>
>>
>> _______________________________________________
>> Zope-DB mailing list
>> Zope-DB [at] zope
>> http://mail.zope.org/mailman/listinfo/zope-db
>
>

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads