Re: [SQLObject] ADO DB-API support?

[Ian B. <ia...@co...>]

On Sun, 2003-05-25 at 04:43, Magnus Lyck=E5 wrote:
> I've been thinking of adding support for adodbapi to SQLObject.
> Has anyone done that? Any comments?
>=20
> The problem I see is that SQLObject doesn't use the param part
> of cursor.execute(sql, param).
>=20
> That will at least cause problems with dates for my current
> backend, MS Access 97, since the warped SQL dialect there uses
> dates like #1999-04-12# instead of '1999-04-12'. :(

That's really dumb, but I guess it's not your fault ;)

You could mess around with SQLBuilder.sqlRepr if you don't mind making
incompatible modifications.  That's where the current quoting of
mxDateTime values goes.  I've thought about hooks for database-specific
quoting, exactly like this situation, but I haven't done it.  There's
only a couple cases, since there's a reasonable lowest-common-
denominator for most databases and data types.

> But it seems I can get basic support by just overriding the
> insert or update methods. After all, dates are never key
> values, and I suppose I can live with the limitations in
> selects for the time being.

You probably won't be able to use your date object directly, but if an
instance defines a sqlRepr method, that will be used.  So it could be
something like:

class MyDate:
    def __init__(self, date):
        self.date =3D date
    def sqlRepr(self):
        return '#%s#' % self.date.strftime('%Y-%m-%d')

Again, obviously not portable among databases, and perhaps only
moderately better than modifying the source of SQLBuilder.

Someday DateTimeCol will be able to handle the necessary quoting, though
that won't address the select issues.

> But in the long run, It's like to be able to do
> Person.select('birthdate < ?', (Date(1999,4,12),)) though...
>=20
> What's the rationale behind not using params today? Would
> a change have any negative consequences (beyond some labour)?

Well, for select in particular, maybe params would be fine.  However,
SQLObject has maintained database independence fairly well, and the
annoying paramstyle issue would make code that uses params less
portable.  Though I'm certainly okay with:

  Person.select('birthdate < %s', params=3D(Date(1999, 4, 12),))

Where any non-param %'s have to be quoted (like %%), then the database
connection changes %s to ? or whatever the paramstyle is.  Should be
easy enough to implement.

I don't use params, and don't plan to, for other SQL construction.=20
Mostly it's because I like having complete control over the SQL, which I
do when I do the string substitution myself.

  Ian