[DB-SIG] mxODBC and Stored Procedures

[paul@boddie.net]

Marc-André Lemburg wrote:
>
> The 2.1 version will implement .callproc() as well 
> as .nextset(). But the .callproc() method won't support output
> parameters either. I've found that these can easily be emulated
> using result sets which give you much more flexibility in terms
> of handling different data types, so I decided not to implement
> output and input/output parameters.

But surely the usage of stored procedures where the Python developer has no 
access to the database-resident code necessitates the provision of in/out and 
out parameters. It's frequently not (just) a question of what is better or more 
flexible.

> That said, I believe that we shouldn't have allowed .callproc()
> to handle anything other than input parameters in the first
> place. It just complicates the implementation and doesn't gain
> you any flexibility.

I can see that the in-place modification of sequence elements is "un-Pythonic" 
for a procedure abstraction, and although there may be interesting ways of 
modifying tuples at the C API level, it really means that people have to use 
lists with such calls, if only to avoid conceptual surprises.

When I extended oracledb (over three years ago now) to handle stored 
procedures, I seem to remember the method I implemented requiring a sequence 
containing a combination of the in and in/out parameters in the order given in 
the stored procedure's signature, with the result of the operation being a 
tuple containing the subsequent in/out and out values (again in the order given 
in the signature). For example:

  # SOMEPROC(in1, inout1, out1, inout2, in2, in3)
  (new_in_out1, out1, new_in_out2) = cursor.callproc("SOMEPROC",
      (in1, in_out1, in_out2, in2, in3))

As a result, in/out and out parameters (and the requirement that they be 
variables) didn't really exist at the Python level at all. Therefore, sequences 
of values could be passed into the method and variables could be used to 
collect the result values, thus providing the illusion of variable modification 
if really desired (use in_out1 and in_out2 instead of new_in_out1 and 
new_in_out2 respectively in the above example). This is arguably much more 
Pythonic and indeed more flexible, if a little hard to follow sometimes - I may 
have considered adding fillers to the sequences too:

  (tmp, in_out1, tmp, out1, in_out2, tmp) = cursor.callproc("SOMEPROC",
      (in1, in_out1, None, in_out2, in2, in3))

Although it can be tempting to use None with the result values, with the usual 
nasty results...

Paul

P.S. This issue and some issues around transactions (discussed on the Webware 
list) could open up a new debate on the DB-API. I know that someone was 
interested in making DB-API version 3, but at the time no-one had any real 
issues with version 2. Is the time right to put proposals forward?

-- 
Get your firstname@lastname email for FREE at http://Nameplanet.com/?su