[DB-SIG] DB-API 1.1

[M.-A. Lemburg]

Paul Boddie wrote:
> 
> > Paul Boddie wrote:
> > >
> > > > 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.  ;-)
> >
> > That's why propose to have the callproc() method do this for you.
> > In Python, e.g.:
> >
> > def callproc(self,procname,parameters=[]):
> >       # DB specific syntax here (Solid syntax in this case):
> >       placeholders = ('?,' * (len(parameters)-1)) [:-1]
> >       s = '{?=call %s(%s)}' % (procname,placeholders)
> >       # Now back to the normal execute method; this will
> >       # possibly manipulate the parameter list in place.
> >       return self.execute(s,parameters)
> 
> One problem I should have mentioned:  for oracledb at least I think
> that there would need to be a bit of behind-the-scenes activity to
> ensure that the execute method "knows" what sort of object it is
> referring to, and that the fetch method "remembers" this information
> and uses the appropriate calls.  I think that in OCI, the means of
> retrieval may be different.

The above code is just an example of how it could work,
the intended behaviour being:
	* call the procedure/function with the parameters list
	* let the procedure manipulate the parameters, i.e.
	  replacing OUT and IN/OUT entries with output values
	* make any result set or multiple result set available
	  through subsequent .fetchxxx()/.nextset() calls.

Is this manageable ? It certainly is for ODBC and Solid. Don't have
any experience with Oracle though.

> Actually, I did get a contribution which enabled execute methods to
> work with some kinds of stored procedures, and the person in question
> pointed out that the mechanisms dealing with procedures are a bit
> different.  As I noted, I avoided combining the two and just found out
> how to call procedures from scratch, reusing as much of the binding
> code as possible.
> 
> > As opposed to my earlier comment I'd like to suggest a different
> > approach, though:
> >
> > Have the execute-method manipulate the parameter list in place.
> > Input columns will be used as such, output columns may contain
> > e.g. None as placeholder and in/output columns get their value
> > replaced by the procedures output.
> > Some DBs seem to also make a result available sometimes... this
> > could then be accessed via .fetchxxx().
> 
> It's possible, but wouldn't that mean that one would have to set the
> list up first, so that one could access the list's members after the
> call?

Right. You pass in the parameter list, let the procedure do
whatever it needs to do with it and then have a look at the
changed list to extract the IN/OUT and OUT variables. It's a
very simple but effective way of doing it, IMHO.

>  Obviously the only price to pay here is notational convenience,
> and if wrapped up in a callproc method, then it wouldn't affect anyone
> at all.
> 
> > This goes along the lines of what Paul's patch does, but without
> > the hazzles of always having to figure out the right order for
> > input and output parameters.
> 
> Yes, that's not all that nice, although the description of the
> procedure is enough to know the ordering and one just needs to make
> sure one hasn't missed an IN/OUT parameter or something when one feeds
> the data in as parameters, for example.  A dictionary mapping names to
> values might be nice, but then we would need to obtain the names of
> parameters in order to do the matching, and I am not sure that this is
> possible using OCI.  It would certainly add some complexity, anyway.

IMHO, dictionaries wouldn't make life easier, but that may be
a personal opinion: I am very much addicted to tuple and list unpacking
-- one of the coolest things Python has to offer (among the hundreds
of other nice features).

> I have given a point of view from the oracledb side (and my very basic
> OCI experience).  Perhaps we could try and assess what the differences
> are between database systems.

ODBC 2.0:
Calling procedures is done through the normal execute method.
You bind parameters prior to calling the execute API either as
IN, IN/OUT or OUT and the API overwrites IN/OUT and OUT parameters
with the procedures output. Additionally, you can retrieve information
about procedure columns (types, names, data flow direction, etc.).
Result sets are extracted with the normal APIs that you also use
for all other queries, e.g. SELECT.

Solid uses ODBC 2.0 as API, so the same should work here.

There's one quirk though: at least for Solid procedures can
have return values. This is notated as '{?=call MyProc(?,?,?)}'
rather than '{call MyProc(?,?,?)}'. Maybe we should have
a second cursor method called 'callfunc()' to clearify the
difference, because otherwise the first value in the parameter
list would always be an OUT parameter to be used for the
procedures return value (yet, in my normal understanding,
procedures are not supposed to return anything -- only functions
do).

Question: should we allow multiple procedure calls to be done
with one .callproc() invocation ? Passing a list of lists could
invoke this way of handling it (just like passing a list of lists
to cursor.execute()).

-- 
Marc-Andre Lemburg
----------------------------------------------------------------------
             | Python Pages:  http://starship.skyport.net/~lemburg/  |
              -------------------------------------------------------