[Zope-dev] ZSQL Methods from Python?

[Monty Taylor]

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