[DB-SIG] DB-API 1.1
Paul Boddie
Paul Boddie <Paul.Boddie@cern.ch>
Thu, 4 Jun 1998 12:34:11 +0200 (MET DST)
Sorry to go straight for this one, but it is something that I have
dealt with tentatively...
> Stored Procedures. As expected this causes troubles.
>
> AFAIK, it is possible to call stored procedures via the
> standard execute method. Input parameters get passed in
> via the parameter tuple, output goes into a result set.
I don't think it is with oracledb, or at least not universally. In
any case, I think that a specific method to make it more usable is
worthwhile, otherwise you have to do things like:
begin proc(...); end;
That is how you might do the work in, say, sqlplus for Oracle, but it
isn't very nice. I always mess the syntax up somehow. ;-)
> Since databases tend to use different calling syntaxes
> the callproc() could take care of mapping the procedure
> to the database specific syntax and then pass the parameters
> on to the execute method to have the procedure call itself
> executed. Results would then be available via the
> result set. IN OUT parameters are not possible using this
> scheme, but then: which Python type would you use for them
> anyway...
The way I implemented this [1] used a method on the cursor object, for
reasons that the Oracle Call Interface function requires a cursor with
which the work is done. The DB-API doesn't define what happens with
the parameters, though, and although I considered a dictionary, having
little experience of either OCI or Python internals I settled for
passing in a list of all IN or IN/OUT parameters in the order that
they are expected in the procedure call, and receiving a tuple of all
OUT or IN/OUT parameters in the order they are listed in the procedure
definition. That seems a bit strange, and obviously the programmer
has to check the parameters in use carefully, but it isn't completely
illogical.
[1] http://assuwww.cern.ch/~pboddie/Personal/Interests/Python/Patch_for_oracledb.html
Paul Boddie Paul.Boddie@cern.ch
| http://assuwww.cern.ch/~pboddie
| Any views expressed above are personal and not necessarily
| shared by my employer or my associates.