[Zope-DB] RE: DCOracle2 TIMESTAMP DataType Issue - Resolved :-)

Maan M. Hamze mmhamze at pleiades.net
Thu Mar 22 12:58:01 EDT 2007


Matt -
IT WORKED :-D
I tried it with both, SQLT_DAT, and SQLT_STR:
I started by adding:
a.  CONVERTOUTF(SQLT_TIMESTAMP); //Add to list of CONVERTOUTF at the top
of file
Then:
I:  With SQLT_DAT
I replaced TYPEENTRY(SQLT_TIMESTAMP) in TypeTable[] with
a.  CONVERTOUTF(SQLT_TIMESTAMP, SQLT_DAT,7) // changing number 7 did not

                                            //make any difference with
the 
                                            //result
b.  I COPIED CONVERTOUTF(SQLT_DAT) into CONVERTOUF(SQLT_TIMESTAMP)
     //without changing the body of the function

I recompiled, and selected a timestamp field (without recasting it of
course).
Result:  OracleDate with yyyy-mm-dd hh:mm:ss
So, Seconds granuality is lost (tenths, hundredths, and thousandth)
Which is expected, but for all practical purposes, it does not matter.

Then,
II: with SQLT_STR
I replaced TYPEENTRY(SQLT_TIMESTAMP) in TypeTable[] with
a.  CONVERTOUTF(SQLT_TIMESTAMP, SQLT_STR,50) // changing length affected
                                             //length of resulting
string
b.  I COPIED CONVERTOUTF(SQLT_STR) into CONVERTOUF(SQLT_TIMESTAMP)
     //without changing the body of the function

Result:  Full timestamp value as a string,
Such as:  03-MAY-06 12.00.00.000000 AM

Along with other changes to dco2.c it may be time to coordinate a new
package release.
However, I need to add some WIN32 defs to the code in order to make one
platform independent package.  This mainly affects the _decl statement
for windows, and the dword issue.
I also need to add a define for ORACLE10G into dco2.c, and a way of
detecting oracle10g in oratest.  However, I still do not know what to
detect using nm.
Whatever the case is:  DCOracle2 is FINE into Oracle10 and with time
stamps.
As far as I am concerned, this is great news.

You know, I love your implementation with these converters.  This dco2.c
is a gem of a code piece of writing.
Thanks for your help,
Maan 

-----Original Message-----
From: Matthew T. Kromer [mailto:matt at bane.mi.org] 
Sent: Thursday, March 22, 2007 5:42 AM
To: Maan M. Hamze
Cc: zope-db at zope.org
Subject: Re: DCOracle2 TIMESTAMP DataType Issue

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



More information about the Zope-DB mailing list