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