Am 15.04.2010, 17:14 Uhr, schrieb Alexandre Fayolle
<alexandre.fayolle at logilab.fr>:
> Thanks for your detailed answer. I was not aware of the SELECT list
> limitation
> on parameters. I shall investigate if we can twist the ORM around this
> to use
> the more efficient method (as what I'm after is speed).
One of the places where we hit the problem was in the SQLa 0.6 mxODBC
dialect for MS SQL
>> An ODBC trace log would help find out what the ODBC driver
>> is asking for.
> Here's some additional information on this specific bug (the %(name)s
> quoting
> in the SQL is processed to use the ? style quoting):
> sql: 'UPDATE cw_CWUser SET cw_modification_date =
> %(cw_modification_date)s,
> cw_last_login_time = %(cw_last_login_time)s WHERE cw_eid = %(cw_eid)s'
> args: {'cw_eid': 5, 'cw_modification_date': datetime.datetime(2010, 4,
> 15, 16,
> 51, 25, 468000), 'cw_last_login_time': datetime.datetime(2010, 4, 15,
> 16, 51,
> 25, 453000)}
> Basically, the datetime values are obtained using
> datetime.datetime.now(). The
> column in the database use MS SQL datetime type.
> As for the ODBC trace log, I could not generate it. I have installed
> mxODBC and mxBase using MSI installers: do these include the debug
> support mentioned in the documentation, or do I have to use the ZIP
> prebuilt packages?
This looks like an incorrect application of the parameters. You *use* ?
for parameters in this case. You must use string formatting to generate
your SQL statement for "SELECT ?, ? FROM table" from your first example
because you are passing variables to a statement and not parameters to the
database which has already prepared the statement. Anytime you need "now"
in a statement, get the database to generate it or set the variable to
timestamp to have it done automatically on change.
There is a bug in the MS SQL driver with the use of parameters in JOINs
and sub-selects but that is not the case here.
The ODBC trace log is produced by the ODBC driver not by mxODBC.
Charlie
--
Charlie Clark
eGenix.com
Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/________________________________________________________________________
::: Try our new mxODBC.Connect Python Database Interface for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/