matt at bane
Mar 22, 2007, 3:41 AM
Post #4 of 4
(961 views)
Permalink
|
The type table just registers the known Oracle data types and whether
or not there are type converters registered,
TYPEENTRY has no converter,
TYPOENTRY has a registered output converter,
CTYPOENTRY has a coercion output converter, where oracle is asked to
return the result as a different type, or where size data has to be
provided.
Its entirely possible you could change the type table entry for a
timestamp to be
CTYPOENTRY(SQLT_TIMESTAMP,SQLT_DAT,7)
and then copy (don't rename) the whole CONVERTOUTF(SQLT_DAT) function
to be CONVERTOUTF(SQLT_TIMESTAMP).
I don't know if Oracle can convert a timestamp into a date though.
It probably can :) This may lose data doing the conversion. You
could also ask for it to always be converted to a string in the
converter, or anything else -- the second parameter to CTYPOENTRY
tells it what to return the type as, and the 3rd is the size. The
output conversion routine is named on the first parameter, but its
input arguments will be based on the 2nd and 3rd.
On Mar 21, 2007, at 5:04 AM, Maan M. Hamze wrote:
> I checked dco2.c yesterday and I did notice that there is no
> converter for SQLT_TIMESTAMP:
> COVERTOUTF(SQLT_TIMESTAMP)
>
> I wanted to study the code further, so that I do not sound like an
> idiot :-D
> I noticed the following which discouraged me from making more
> comments about it:
> In
>
> struct TypeTableStruct TypeTable[]
>
> There is CTYPOENTRY. TYPEENTRY, and TYPOENTRY
> We have for example:
>
> /* I */ TYPOENTRY(SQLT_DAT), /* Date */
> TYPEENTRY(SQLT_TIMESTAMP), /* Time stamp */
>
> Where *I* means internal Oracle type
> So the suggestion is that SQLT_TIMESTAMP is not an internal oracle
> type, and it is an argument of TYPEENTRY while SQLT_DAT is internal
> oracle type and is an argument of TYPOENTRY.
>
> I do not know if this makes a difference related to writing:
> COVERTOUTF(SQLT_TIMESTAMP)
>
> You wtire:
>>> 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.
>
> DCOracle2 on Windows XP comes back with the error message that an
> unknown datatype of SQLT_TIMESTAMP is encontered (not a segfault).
> I'll run it again today on Windows XP to see which line is
> producing the error. Maybe then we would know if CONVERTOUTF
> (SQLT_DAT) is producing error or simply the absence of CONVERTOUTF
> (SQLT_TIMESTAMP) is causing it.
>
> Maan
>
>
>
> ----- Original Message ----- From: "Matthew T. Kromer"
> <matt[at]bane.mi.org>
> To: "Maan M. Hamze" <mmhamze[at]pleiades.net>
> Cc: <zope-db[at]zope.org>
> Sent: Tuesday, March 20, 2007 8:32 PM
> Subject: Re: [Zope-DB] DCOracle2 with a Stored Procedure that
> Returns REFCURSOR / ORA TIMESTAMP DataType Issue
>
>
> 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
_______________________________________________
Zope-DB mailing list
Zope-DB[at]zope.org
http://mail.zope.org/mailman/listinfo/zope-db
|
