[DB-SIG] Syntax for dates/times in prepared statements

M.-A. Lemburg mal@lemburg.com
Mon, 26 Jul 1999 17:36:07 +0200


Andy Robinson wrote:
> 
> Still at work on the database chapter for PPW32 - I'm
> doing some benchmarking on prepared vs. literal
> statements.  I'd like to include dates and times in
> the parameter list and cannot find a syntax to let me
> do it.  Any help gratefully received as failure to
> solve this kind of weakens my hymns of praise to
> mxDateTime!
> 
> Currently using Sybase SQL Anywhere with their
> provided ODBC driver; mxODBC.Windows; and mxDateTime.
> 
> cursor.execute("""INSERT INTO analysis
> (id, when, what, howmuch)
> VALUES (?,?,?,?)""", (id,when,what,howmuch))
> 
> fails however I represent the date-time in 'when'.
> Sybase complains about the SQL syntax.  I've tried
> strings in many formats, numbers, and raw mxDateTime
> objects (I hoped the latter was the intention...)

That's how it's supposed to work... but date/time values
are always "something special" because databases tend
to be somewhat picky about what the format should look
like.

To help, I need more information though: the row
description and the debugging output when executing the
above statement. To get the debug output written to a file,
please run Python in debug mode, i.e. 'python -d'. mxODBC
should then write a file mxODBC.log which contains
plenty of information about the dialog with the ODBC driver.

Also, the output of the script ODBC/Misc/test.py would
probably give some more insights.

FYI, the latest pre-release is available at:

   http://starship.skyport.net/~lemburg/mxODBC-pre1.2.0.zip

> Without the "when" column, it all works beautifully
> and runs 60% faster with prepared statements.  I also
> tried other column names.  Also embedding dates in
> literal SQL statements is no problem.
> 
> If this is a database-specific problem, can someone
> give me an example of a database it does work with,
> and what syntax they use?

BTW, could be that the database doesn't like the order
of the columns... some drivers want all things they
consider special at the end of the list.

-- 
Marc-Andre Lemburg
______________________________________________________________________
Y2000:                                                   158 days left
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/