[DB-SIG] mxODBC and Stored Procedures

M.-A. Lemburg mal@lemburg.com
Thu, 13 Sep 2001 20:42:33 +0200


paul@boddie.net wrote:
>=20
> Marc-Andr=E9 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.
>=20
> But surely the usage of stored procedures where the Python developer ha=
s no
> access to the database-resident code necessitates the provision of in/o=
ut and
> out parameters. It's frequently not (just) a question of what is better=
 or more
> flexible.

Ok, ok. You've got me convinced. I'll look into providing support
for in/out and output parameters too.
=20
> > 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.
>=20
> I can see that the in-place modification of sequence elements is "un-Py=
thonic"
> 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 t=
o use
> lists with such calls, if only to avoid conceptual surprises.
>=20
> When I extended oracledb (over three years ago now) to handle stored
> procedures, I seem to remember the method I implemented requiring a seq=
uence
> containing a combination of the in and in/out parameters in the order g=
iven in
> the stored procedure's signature, with the result of the operation bein=
g a
> tuple containing the subsequent in/out and out values (again in the ord=
er given
> in the signature). For example:
>=20
>   # SOMEPROC(in1, inout1, out1, inout2, in2, in3)
>   (new_in_out1, out1, new_in_out2) =3D cursor.callproc("SOMEPROC",
>       (in1, in_out1, in_out2, in2, in3))
>=20
> As a result, in/out and out parameters (and the requirement that they b=
e
> variables) didn't really exist at the Python level at all. Therefore, s=
equences
> of values could be passed into the method and variables could be used t=
o
> collect the result values, thus providing the illusion of variable modi=
fication
> 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 m=
ore
> Pythonic and indeed more flexible, if a little hard to follow sometimes=
 - I may
> have considered adding fillers to the sequences too:
>=20
>   (tmp, in_out1, tmp, out1, in_out2, tmp) =3D cursor.callproc("SOMEPROC=
",
>       (in1, in_out1, None, in_out2, in2, in3))

This is what DB API 2.0 specifies if I'm not mistaken.=20
=20
> Although it can be tempting to use None with the result values, with th=
e usual
> nasty results...

Aside: I think that we (pydev) decided to use _ as replacement for unneed=
ed=20
parameters... at least that's what Martin von Loewis uses a lot
and it seems to work quite nicely.
=20
> Paul
>=20
> P.S. This issue and some issues around transactions (discussed on the W=
ebware
> 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 r=
eal
> issues with version 2. Is the time right to put proposals forward?

The 3.0 proposal was really more about a proposal for a DBI wrapper
around DB API compliant modules. In that sense it did not really
target the DB API itself.

Which are the issues with DB API 2.0 ?

The only one which comes to my mind is that it would be nice
to expose the exception objects used by the module on the=20
connection objects as read-only attributes: it makes writing
generic DB code a lot easier. But this is really only a minor issue,
something for say DB API 2.1.

Thanks,
--=20
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
______________________________________________________________________
Consulting & Company:                           http://www.egenix.com/
Python Software:                        http://www.lemburg.com/python/