[Zope-DB] 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 [EMAIL PROTECTED] To: Maan M. Hamze [EMAIL PROTECTED] Cc: zope-db@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, et, 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:[EMAIL PROTECTED] Sent: Saturday, March 17, 2007 5:32 PM To: zope-db@zope.org Subject: Re: [Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR m.banaouas [EMAIL PROTECTED]: options = (2714, + ' + + ', + 36, 1, c2) it's wrong! you must give a sequence as second parameter of execute method. So you do like this: options = (2714, '',36, 1, c2) c1.execute(sql, options) -- I tried it both ways. With what you suggest: Import DCOracle2 db =
[Zope-DB] RE: DCOracle2 TIMESTAMP DataType Issue - Resolved :-)
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 -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.00 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:[EMAIL PROTECTED] Sent: Thursday, March 22, 2007 5:42 AM To: Maan M. Hamze Cc: zope-db@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 [EMAIL PROTECTED] To: Maan M. Hamze [EMAIL PROTECTED] Cc: zope-db@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
[Zope-DB] RE: DCOracle2 TIMESTAMP DataType Issue
This is the error I am getting with DCOracle2 on Win XP with a TIMESTAMP field fetched in a record set. r = c1.fetchone() Traceback (most recent call last): File stdin, line 1, in ? File C:\Python\lib\site-packages\DCOracle2\DCOracle2.py, line 1182, in fetch one v = col[self._rcount].value() TypeError: no type converter registered for type SQLT_TIMESTAMP The error message is generated from dco2.c I believe in: static PyObject *ResultSetItem_value(ResultSetItem *self, PyObject *args) Not this: static PyObject *BindingArrayObject_item(BindingArrayObject *self, int item) Maan -Original Message- From: Maan M. Hamze [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 21, 2007 4:04 AM To: zope-db@zope.org Cc: Matthew T. Kromer Subject: DCOracle2 TIMESTAMP DataType Issue 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 [EMAIL PROTECTED] To: Maan M. Hamze [EMAIL PROTECTED] Cc: zope-db@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, et, 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:[EMAIL PROTECTED] Sent: Saturday, March 17, 2007 5:32 PM To: zope-db@zope.org Subject: Re: [Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR m.banaouas [EMAIL PROTECTED]: options = (2714, + ' + + ', + 36, 1, c2) it's wrong! you must give a sequence as second parameter of execute method. So you do like this: options = (2714, '',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, '', 36, 1, c2) sql = storedProcedureName(:INparam1, :INparam2, :INparam3, :INparam4, :OUTparam) c1.execute(sql, options) I am getting now: Traceback (most recent call last): File
