Hey, is there any doco about using ZSQL Methods from Python Products? Also, does anyone know of any work done to extend ZSQL Methods to allow stored-procedure calls? 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? Thanks, Monty
Monty Taylor wrote:
Hey, is there any doco about using ZSQL Methods from Python Products?
Not that I'm aware of. You simply call them pretty much like any other Python callable thing. The signature is: someMethod([mapping, **other_arguments]) where: mapping is a mapping object that provides SQL method arguments, and can include other information. (The actual name of the positional argument may be "REQUEST" ;). This argument is optional. You can also provide arguments as named keywords.
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. 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. Jim -- Jim Fulton mailto:jim@digicool.com Technical Director (888) 344-4332 Python Powered! Digital Creations http://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.
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 Type In/Out Default? ------------------------------ ----------------------- ------ -------- PP_NAME VARCHAR2 IN PP_RESULT REF CURSOR IN/OUT RECORD IN/OUT AMSM_ID NUMBER(12) IN/OUT NAME VARCHAR2(80) IN/OUT PROCEDURE SELECT_TECHNOLOGIES Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PP_CATEGORY VARCHAR2 IN 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
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 Type In/Out Default? ------------------------------ ----------------------- ------ -------- PP_NAME VARCHAR2 IN PP_RESULT REF CURSOR IN/OUT RECORD IN/OUT AMSM_ID NUMBER(12) IN/OUT NAME VARCHAR2(80) IN/OUT PROCEDURE SELECT_TECHNOLOGIES Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PP_CATEGORY VARCHAR2 IN 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:jim@digicool.com Technical Director (888) 344-4332 Python Powered! Digital Creations http://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.
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.
participants (3)
-
Jim Fulton -
Monty Taylor -
Phillip J. Eby