[DB-SIG] Preparing statement API

[Eric Brunson]

--EeQfGwPcQSOJBaQU
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

* Hrvoje Niksic (hniksic@iskon.hr) [000126 08:13]:
> Eric Brunson <brunson@Level3.net> writes:
>=20
> > From the DCOracle docs:
> >=20
> >         prepare(sql) --
> >              Returns a new Handle Object.  A handle is a pre-prepared
> >              notion of a cursor, where some handling of the SQL
> >              parsing has already been done.=20
> >=20
> > You use it like this:
> >=20
> > (Assume a DB connection called "dbc")
> >=20
> > inshndl =3D dbc.prepare( "insert into mytable ( attribute, value ) "
> > 		       "values ( :p1, :p2 )" )
> > for attr, val in list:
> >     inshndl.execute( (attr, val) )
>=20
> The difference is that the interface of my proposal seems (to me) more
> in line with the general cursor-based model of DB API.  It only adds
> one more function, and everything else happens naturally -- for
> instance, you still execute statements through cursor.execute,
> retrieve rows through cursor.fetch*, etc.
>=20
> Everyone please note that my proposal comes from a mere *user* of the
> DB modules.  If what I propose is hard to implement, don't hesitate to
> let me know.
>=20

I guess my point was the DCOracle package already has the
functionality you are describing, the Handle object inherits directly
from the Cursor object but they add the prepare() function to the
connection object and then overload execute() from the Cursor object
to take a list of parameters.

So, this is almost exactly what you're asking, except you apparently
want the prepare() function to be a method of the Cursor object
instead of the Connection.  The handle is still a Cursor and is
manipulated through the fetch*() functions. =20

I'm thinking that putting the prepare in the Connection object
probably avoids having to tote the baggage of runtime binding around
in a Cursor object if you aren't going to use it.  It does limit you
by having to instantiate a different Handle object for each different
query, but you'd have to do some careful benchmarking to determine if
that is outweighed by a performance gain in using normal cursors.  If
you look at the actual C code generated by the Pro*C precompiler of a
simple static select statement vs. one with runtime variable binding,
you'll see quite a difference.  That could very well be performance
impacting.

What I'm saying is the code is there, it exists.  I'm afraid I've only
ever used the DCOracle package and not the built in DB API stuff, but
from what I've read, the interfaces are pretty darn similar, almost
identical.  I believe the DCOracle implementation using :arbitrayname
to be a little more elegant since you can use named parameter lists
*or* positional parameters, rather than being restricted to positional
parameters by question marks as place holders.  This is also more in
line of the syntax of all the other Oracle implementations I've seen
in APIs such as Pro*C, SQL*Plus and PL/SQL.

So, if you want the functionality right away, use the DCOracle package
from Zope.  If it doesn't suit your tastes, then feel free to ignore
this.

e.

P.S.
I only have an in depth understanding of Oracle's implementation
specifics, so if what I've described above does not translate to
Sybase, Informix and the Open Source SQL DBs, please enlighten me.
Thanks.

--=20
Eric Brunson                * _ o  *       Faster and faster,            =
=20
brunson@brunson.com        * / //\           until the thrill of speed   =
=20
brunson@level3.net            \>>|   *         overcomes the fear of death
page-eric@level3.net           \\,     =20

--EeQfGwPcQSOJBaQU
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.0 (SunOS)
Comment: For info see http://www.gnupg.org

iD8DBQE4jywjQDyMrcmL1RcRAXuEAJ4sBu0oKoLXPlhW2TfcTUb/CjCuUACdFokl
oAPWptpI/VwyqPXi7LEANWo=
=w1uG
-----END PGP SIGNATURE-----

--EeQfGwPcQSOJBaQU--