M.-A. Lemburg mal@lemburg.com
Thu, 04 Jun 1998 14:31:08 +0200

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)

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

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.

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