[DB-SIG] DB-API 1.1

[Paul Boddie]

> 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.

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?  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.

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.

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.