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

[Andy Dustman]

On Mon, 26 Jul 1999, [iso-8859-1] 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...)
> 
> 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.

It seems DateTime objects SHOULD be fine for when, if you are using
mxODBC. I assume WHEN is a DATETIME column? If so, then a string with the
ISO format, i.e. '1999-07-27 13:24:53.003', should also be fine.

But a syntax error, you say. Is it possible that "when" is a reserved word
in Sybase? If so, you may be able to circumvent this with a literal column
name, i.e.:

> cursor.execute("""INSERT INTO analysis
> (id, "when", what, howmuch)
> VALUES (?,?,?,?)""", (id,when,what,howmuch))

I.e. normal SQL column names are not case-sensitive, but if you quote them
using the double-quotes, they are interpreted literally. I forget the
technical SQL term for this, but I'm pretty sure that's how it works.

-- 
andy dustman  | programmer/analyst |  comstar communications corporation
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d