[Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR / ORA TIMESTAMP DataType Issue

[Matthew T. Kromer]

Chances are good that the C code that is trying to construct the  
timestamp doesn't know how to convert it...

A quick peek into the source code hints the code doesn't have a type  
converter for SQLT_TIMESTAMP, although there is a converter for  
SQLT_DAT (date).

Putting a converter into the C code shouldn't be all that tough, if  
you need to do it you can probably figure it out :)

Take a look at the function CONVERTOUTF(SQLT_DAT) -- although that  
might also be where the segfault comes from.  That function hops  
around on one leg a bit to try to get the C library mktime and gmtime  
system time conversion routines to do the heavy lifting.

On Mar 20, 2007, at 8:53 AM, Maan M. Hamze wrote:

> To give an update:
> To run a stored procedure which returns a ref cursor, I tried:
> c1 = db.cursor()
> c2 = db.cursor()
> sql = "storedProcedureName(:inparam1, :inparam2, etccc, :outparam)"
> options = (inparam1, inparam2, etcccc, c2)
> c1.execute(sql, options)
> As recommended below.
> This did not work.
>
> However, this worked:
> c1 = db.cursor()
> c2 = db.cursor()
> c2 = c1.procedures.storedProcedureName(INparam1, INparam2, etc..)
> Only the INparams are given as arguments.  c2 is returned as a cursor
> OUTparam.
> Then,
> r = c2.fetchall(), or
> r = c2.fetchone()
> Etc.....
>
> This was causing a segmentation fault on a Sun Solaris box.
> On Windows, I got an actual error message.  While fetching, the cursor
> has a field of ora datatype TimeStamp.  This was crashing  
> DCOracle2.  a
> to_char solved the issue.
> I am using DCOracle2 and Oracle 10 on Solaris (and Windows XP).
> Did anyone run into issues of handling TimeStamp oracle data type with
> DCOracle2?
> Maan
>
>
> -----Original Message-----
> From: Maan M. Hamze [mailto:mmhamze at pleiades.net]
> Sent: Saturday, March 17, 2007 5:32 PM
> To: zope-db at zope.org
> Subject: Re: [Zope-DB] DCOracle2 with a Stored Procedure that Returns
> REFCURSOR
>
> "m.banaouas" banaouas.medialog at wanadoo.fr:
> 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)
> ------------------
> I tried it both ways.
> With what you suggest:
> Import DCOracle2
> db = DCOracle2.connect(connectionString)
> c1 = db.cursor()
> c2 = db.cursor()
> options = (2714, '99999999', 36, 1, c2)
> sql = "storedProcedureName(:INparam1, :INparam2, :INparam3, :INparam4,
> :OUTparam)
> c1.execute(sql, options)
>
> I am getting now:
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/opt/python/lib/python2.4/site-packages/DCOracle2/ 
> DCOracle2.py",
> line 98
> 7, in execute
>     self._cursor.bindbypos(i, p)
> ValueError: invalid data type bound
>
> Printing options yield:
>>> print options
> (5920, '99999999', 36, 1, <DCOracle2.DCOracle2.cursor instance at
> 0x19eee0>)
> Would this be causing the invalid data type bound error above?
>
> Also, should not the following work:
> c2 = c1.storedProcedureName(2714, '99999999', 36, 1)  ??
> Maan
>
>
> 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.org
>>> http://mail.zope.org/mailman/listinfo/zope-db
>>>
>
>
>
>
>
> ------------------------------
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>
>
> End of Zope-DB Digest, Vol 44, Issue 9
> **************************************
>
>
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db