RE: [Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR / ORA TIMESTAMP DataType Issue
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 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, '', 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, '', 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
RE: [Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR
You have a point Maciej - but I got used to DCOracle2 and so far it has performed quite well. I link it with Oracle lib32 libraries, but use it with ora lib 64-bit libraries in the path. So far, there has been no issues I am aware of. The only thing that came up recently is how to use it with a stored procedure returning a ref cursor. This has been resolved, with a new issue on how to handle Ora TimeStamp data type which is crashing DCOracle2 with a segmentation fault on Sun Solaris (DCOracle2 exits fine with an actual error message on Windows XP. By the way I am using now DCOracle2 with Oracle 10 using the archive you sent me. So far, I have not run into any problems. However, I need to make a summary of what needs to be done to keep the scripts in one set for various platforms (Windows and Sun etc...) using C macros (DEFs), instead of allowing them to diverge. Maan So do you really have to use DCOracle2? I see that your code is plain python. You're not using Zope database adapter here(?) so maybe it is better to use something that is in active developement like cx_Oracle or SQLRelay? You'll avoid some problems, eg. DCOracle2 is not 64 bit compatible. -- 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
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 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, '', 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, '', 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
Re: [Zope-DB] DCOracle2 with a Stored Procedure that Returns REFCURSOR / ORA TIMESTAMP DataType Issue
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
