Re: [SQLObject] ADO DB-API support?

[Magnus <ma...@th...>]

At 05:07 2003-05-25 -0500, Ian Bicking wrote:
>On Sun, 2003-05-25 at 04:43, Magnus Lyck=E5 wrote:
> > 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 ;)

I agree on both points! :)

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

The problem is that then I somehow have to figure out what database
I have behind my ODBC source. I'm not even sure the problem exists
in all Access version. As far as I understand, more modern versions
can be coerced into being more SQL92-like in their behaviour.

I don't want to mess with this at all, I think the really correct way
to handle this is to use parameter passing the way it's intended and
supported by the interface, and let the lower layers pass in the
parameters. Why should we solve a problem that is already solved in
the ODBC drivers?

>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:

But Date is really just an example... The thing is that if I use
something like "INSERT INTO X (a,b,c) VALUES (?,?,?)" or
"SELECT * FROM X WHERE A < ?", and pass in the literals in a params
tuple, the problem goes away, regadless of what kind of data this
might be. (Of course, I still have to provide data in the right
format, but I don't have to solve quoting or escaping.)

After all, the point with the second parameter to cursor.execute()
is there to promote this kind of coding. As far as I understand, neither
MySQL, PostGreSQL or SQLite supports Dynamic SQL or the SQL CLI properly,
so there %s is used, and the parameters are passed into the SQL string in
the interface, but for interfaces to databases with proper Dynamic SQL
support, both the SQL string with question marks and the parameter tuple
are just passed to a lower layer, and handled by the database vendor's
driver. I think this is the right way to do it.

There are certinaly lots of other problems in various SQL dialects that
we can't solve with this fix. Contatenation operators and join syntax etc
makes it difficult to always get SQL to work cross platform, but for
handling of quoting and escaping of literals used in SQL queries, there
*is* a right way to do it, so trying to solve it again in the Python layer
seems...well, non-optimal.

As far as I understand, it's only the code for INSERT and UPDATE that
I *need* to fix. For INSERT it seems that the easiest way is to fix it
in _queryInsertID and not even call _insertSQL. Just call execute with
the values as a separate parameter, and pass in ",".join(['?']*len(values))
in the second parenthesis.

For updates I need to override _SO_update so that I just insert
"SET A =3D ?, B =3D ?" and pass the parameters to self.query. This in
turn means that I need to add an optional param parameter to query
and _query, but it seems like a reasonable thing to do.

Then I still don't have perfect support for selects, but I can do
without that for now I think.

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

So far, you ony seem to support backends that don't support parameter
passing though.

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

As you might have seen in the db-sig mailing list, I recently wrote
code that goes the other way: ? to %s. This has the advantage that
you can write your SQL queries just the way a proper SQL book describes
them, i.e. you can write "SELECT * FROM X WHERE A =3D ? AND B LIKE 'X%'"
without having to think about doubling % or using any other non-SQL
construct in the SQL code. It's also fairly simple to convert qmark
style SQL to named, numbered or even Sybase @-style quoting if that
is needed.

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

I think you are doing more work than you have to that way. As
I said, for MySQL, PostgreSQL and SQLite it's no big difference,
since they do that in a simple way in the interface code anyway,
but for most commercial it might well be. At least for interfaces
like ADO and ODBC where you use one interface but can have any of
many backends behind that interface.

This also means that you place a burden on the application developer
to solve this right for filtering in the select statement.

I do realize that changing this now is quite some work though...


--
Magnus Lycka (It's really Lyck&aring;), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program =20