[Zope-DB] Re: DCOracle2 TIMESTAMP DataType Issue

Thu, 22 Mar 2007 02:41:41 -0800

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, 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:[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, " + "'" + "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@zope.org
http://mail.zope.org/mailman/listinfo/zope-db







------------------------------

_______________________________________________
Zope-DB mailing list
Zope-DB@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@zope.org
http://mail.zope.org/mailman/listinfo/zope-db


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






















					Reply via email to