Re: [Zope-dev] ZSQL Methods from Python?
Jim Fulton wrote:
Monty Taylor wrote:
Also, does anyone know of any work done to extend ZSQL Methods to allow
stored-procedure calls?
No, but I'd love to see someone tackle it. The semantics
of stored procedures varies so widely accross databases, that
I doubt that it would be easiliy generalizable. I think, at
least for starters, a form of stored procedure support for
Oracle would make alot of sense.
I know I can do an Oracle Procedure call from
DCOracle that returns a cursor, but I'd love to combine that with the
caching/pluggable brains of ZSQL Methods. Is there anything out there on
this or am I going to have to roll my own?
I think you'd have to roll your own. If you want to
do something reusable that other people could use, I'd be
happy to provide whatever advice and support I can.
I think I'd like to talk to you about this. We're about to work on
transitioning a web-based product to Zope, and we use Store Procedures
exclusively. I'd love to have the extra ZSQL goo instead of having to wrap
the code at such an individual level. Maybe we can stick someone on the task.
But I'm sure we'll need a guiding hand or two. All of this assumes I can get
past the basic problem that follows...
Note that one of the things I like about Oracle's stored procedures
is that they allow me to avoid screwing with cursors in the common case
that I'm getting one row of data. I can just get the data I need through
a straight function call. The DCOracleStorage uses stored procedures
almost exclusively.
I've been looking through that code (we've started using DCOracleStorage on
the backend.) For some reason, though, I can't get the stored procedure stuff
to work like you do. Check out the following:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL desc pgm140_api;
PROCEDURE SELECT_TARGETS
Argument Name TypeIn/Out Default?
-- --- --
PP_NAMEVARCHAR2IN
PP_RESULT REF CURSOR IN/OUT
RECORD IN/OUT
AMSM_IDNUMBER(12) IN/OUT
NAME VARCHAR2(80)IN/OUT
PROCEDURE SELECT_TECHNOLOGIES
Argument Name TypeIn/Out Default?
-- --- --
PP_CATEGORYVARCHAR2IN
PP_RESULT REF CURSOR IN/OUT
RECORD IN/OUT
ET_ID NUMBER(12) IN/OUT
NAME VARCHAR2(80)IN/OUT
*
Then from python, with the same connection string, I do:
Python 1.5.2 (#1, Feb 14 2000, 18:27:27) [GCC 2.95.1 19990816 (release)] on
sunos5
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
import DCOracle.oci_
conn=DCOracle.Connect('')
pgm140_api=getattr(conn.procedures,'pgm140_api')
sql_select_targets=getattr(pgm140_api, 'select_targets')
Traceback (innermost last):
File "stdin", line 1, in ?
File "/apps/zope/lib/python/Products/ZOracleDA/DCOracle/ociProc.py", line
324, in __getattr__
oci.error: no usable procedure named pgm140_api.select_targets
I've tried using all caps on one or both as well to no avail. Any thoughts?
Monty
___
Zope-Dev maillist - [EMAIL PROTECTED]
http://lists.zope.org/mailman/listinfo/zope-dev
** No cross posts or HTML encoding! **
(Related lists -
http://lists.zope.org/mailman/listinfo/zope-announce
http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL Methods from Python?
Monty Taylor wrote:
Jim Fulton wrote:
(snip)
Note that one of the things I like about Oracle's stored procedures
is that they allow me to avoid screwing with cursors in the common case
that I'm getting one row of data. I can just get the data I need through
a straight function call. The DCOracleStorage uses stored procedures
almost exclusively.
I've been looking through that code (we've started using DCOracleStorage on
the backend.) For some reason, though, I can't get the stored procedure stuff
to work like you do. Check out the following:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL desc pgm140_api;
PROCEDURE SELECT_TARGETS
Argument Name TypeIn/Out Default?
-- --- --
PP_NAMEVARCHAR2IN
PP_RESULT REF CURSOR IN/OUT
RECORD IN/OUT
AMSM_IDNUMBER(12) IN/OUT
NAME VARCHAR2(80)IN/OUT
PROCEDURE SELECT_TECHNOLOGIES
Argument Name TypeIn/Out Default?
-- --- --
PP_CATEGORYVARCHAR2IN
PP_RESULT REF CURSOR IN/OUT
RECORD IN/OUT
ET_ID NUMBER(12) IN/OUT
NAME VARCHAR2(80)IN/OUT
*
Then from python, with the same connection string, I do:
Python 1.5.2 (#1, Feb 14 2000, 18:27:27) [GCC 2.95.1 19990816 (release)] on
sunos5
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
import DCOracle.oci_
conn=DCOracle.Connect('')
pgm140_api=getattr(conn.procedures,'pgm140_api')
sql_select_targets=getattr(pgm140_api, 'select_targets')
Traceback (innermost last):
File "stdin", line 1, in ?
File "/apps/zope/lib/python/Products/ZOracleDA/DCOracle/ociProc.py", line
324, in __getattr__
oci.error: no usable procedure named pgm140_api.select_targets
I've tried using all caps on one or both as well to no avail. Any thoughts?
I suspect that the problem is the RECORD argument type. I don't
remember off-hand what this is, but I'm pretty sure, the DCOracle
procedure code doesn's handle this type. We (you;) would need to
either work around this or fix it. Unless the fix is easy though,
we might want to wait for an OCI8-based Oracle interface that
someone here is rumored to be working on. :) (Actually, I *know*
that someone's working on an OCI 8 interface, but I'm not sure
what the status or priority is.)
Jim
--
Jim Fulton mailto:[EMAIL PROTECTED]
Technical Director (888) 344-4332 Python Powered!
Digital Creationshttp://www.digicool.com http://www.python.org
Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission. Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.
___
Zope-Dev maillist - [EMAIL PROTECTED]
http://lists.zope.org/mailman/listinfo/zope-dev
** No cross posts or HTML encoding! **
(Related lists -
http://lists.zope.org/mailman/listinfo/zope-announce
http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL Methods from Python?
At 08:43 AM 9/22/00 -0400, Jim Fulton wrote: Also, does anyone know of any work done to extend ZSQL Methods to allow stored-procedure calls? No, but I'd love to see someone tackle it. The semantics of stored procedures varies so widely accross databases, that I doubt that it would be easiliy generalizable. I think, at least for starters, a form of stored procedure support for Oracle would make alot of sense. Ty and I have put together a Stored Procedure method for Sybase; it requires a minor patch to ZSybaseDA, however, to allow for the status code return. I'm not sure how useful it would be to anyone else, though, since all we ever do with stored procedures is process the return value, and ignore any actual SELECT results. ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL Methods from Python?
"Phillip J. Eby" wrote: At 08:43 AM 9/22/00 -0400, Jim Fulton wrote: Also, does anyone know of any work done to extend ZSQL Methods to allow stored-procedure calls? No, but I'd love to see someone tackle it. The semantics of stored procedures varies so widely accross databases, that I doubt that it would be easiliy generalizable. I think, at least for starters, a form of stored procedure support for Oracle would make alot of sense. Ty and I have put together a Stored Procedure method for Sybase; it requires a minor patch to ZSybaseDA, however, to allow for the status code return. I'm not sure how useful it would be to anyone else, though, since all we ever do with stored procedures is process the return value, and ignore any actual SELECT results. I think it would be useful. Can you get multiple return values? Jim -- Jim Fulton mailto:[EMAIL PROTECTED] Python Powered! Technical Director (888) 344-4332http://www.python.org Digital Creationshttp://www.digicool.com http://www.zope.org Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email address may not be added to any commercial mail list with out my permission. Violation of my privacy with advertising or SPAM will result in a suit for a MINIMUM of $500 damages/incident, $1500 for repeats. ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL Methods from Python?
At 12:49 PM 9/22/00 -0400, Jim Fulton wrote: "Phillip J. Eby" wrote: Ty and I have put together a Stored Procedure method for Sybase; it requires a minor patch to ZSybaseDA, however, to allow for the status code return. I'm not sure how useful it would be to anyone else, though, since all we ever do with stored procedures is process the return value, and ignore any actual SELECT results. I think it would be useful. Can you get multiple return values? By "return value", I mean the "return status" code which is an integer returned by the Sybase "RETURN" statement. Sybase also can generate its own, negative-numbered return statuses, which indicate a Sybase error. These error codes can include ones that mean the current transaction has already been aborted. Apart from the "return value", a Sybase stored procedure can execute SELECT's which result in rows being returned. We have never used this capability, so I'm not sure what would need to be done for it to be practical. We ordinarily use stored procedures only to perform inserts, updates, deletes, and other things that only need a integer result if anything. (Eg: get next counter value to assign an object identifier.) ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL Methods from Python?
"Phillip J. Eby" wrote: At 12:49 PM 9/22/00 -0400, Jim Fulton wrote: "Phillip J. Eby" wrote: Ty and I have put together a Stored Procedure method for Sybase; it requires a minor patch to ZSybaseDA, however, to allow for the status code return. I'm not sure how useful it would be to anyone else, though, since all we ever do with stored procedures is process the return value, and ignore any actual SELECT results. I think it would be useful. Can you get multiple return values? By "return value", I mean the "return status" code which is an integer returned by the Sybase "RETURN" statement. Sybase also can generate its own, negative-numbered return statuses, which indicate a Sybase error. These error codes can include ones that mean the current transaction has already been aborted. Apart from the "return value", a Sybase stored procedure can execute SELECT's which result in rows being returned. We have never used this capability, so I'm not sure what would need to be done for it to be practical. We ordinarily use stored procedures only to perform inserts, updates, deletes, and other things that only need a integer result if anything. (Eg: get next counter value to assign an object identifier.) Ah, too bad. Can Sybase *only* return values through selected rows? Oracle's stored procedures are much more like traditional procedures and therefore extremely useful even when no results are returned. Jim -- Jim Fulton mailto:[EMAIL PROTECTED] Python Powered! Technical Director (888) 344-4332http://www.python.org Digital Creationshttp://www.digicool.com http://www.zope.org Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email address may not be added to any commercial mail list with out my permission. Violation of my privacy with advertising or SPAM will result in a suit for a MINIMUM of $500 damages/incident, $1500 for repeats. ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
