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

2007-03-20 Thread Maciej Wisniowski

> 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?
We had problems with segmentation faults on 64 bit systems.
We didn't realized what caused this (except that it was DCOracle2).
Problem appeared only under high load of our servers so it was hard
to debug. On 32 bit systems everything was ok. Is your problem with
TimeStamp related to 64 bit platform or it happens on 32 bit platforms too?

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


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

2007-03-20 Thread 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, 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 "", 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, '', 36, 1, )
Would this be causing the invalid data type bound error above?

Also, should not the following work:
c2 = c1.storedProcedureName(2714, '', 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 = '',
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, " + "'" + "" + "'," + "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:

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

2007-03-20 Thread Maan M. Hamze
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 "", 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, '', 36, 1, )
Would this be causing the invalid data type bound error above?

Also, should not the following work:
c2 = c1.storedProcedureName(2714, '', 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 = '',
 > 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, " + "'" + "" + "'," + "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