Problem with inserting dates using mx.ODBC.Windows driver

[Fons Dijkstra]

Hello,

I'm using the mx.ODBC.Windows package in order to read/write a MSAccess
database. Everything works fine apart from the DATE format handling. I'm
using the default "datetimeformat" (i.e. DATETIME_DATETIMEFORMAT) as
suggested. The date columns in the MSAccess database have "Short Date"
format.

When I read a DATE item everything works fine, like:

conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
data = cursor.fetchall()
print data [(..., <DateTime object for '2003-07-31 00:00:00.00 at address>, ...)]

But when I try to update the table I get a ProgrammingError, like:
date = mx.ODBC.Windows.Date(2003, 07, 31)
conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("INSERT INTO table (..., DATE, ...) VALUES (..., ?,

....)", (..., date, ...))
ProgrammingError: Syntax Error in INSERT INTO statement.

Is it possible to use the mx.DateTime type for MSAccess databases? If so,
how should it be done? If not, how can I insert dates into a MSAccess
database?

Thank you in advance, Fons

[M.-A. Lemburg]

Fons Dijkstra wrote:

Hello,

I'm using the mx.ODBC.Windows package in order to read/write a MSAccess
database. Everything works fine apart from the DATE format handling. I'm
using the default "datetimeformat" (i.e. DATETIME_DATETIMEFORMAT) as
suggested. The date columns in the MSAccess database have "Short Date"
format.

When I read a DATE item everything works fine, like:

conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
data = cursor.fetchall()
print data
[(..., <DateTime object for '2003-07-31 00:00:00.00 at address>, ...)]

But when I try to update the table I get a ProgrammingError, like:

date = mx.ODBC.Windows.Date(2003, 07, 31)
conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("INSERT INTO table (..., DATE, ...) VALUES (..., ?,
...)", (..., date, ...))
ProgrammingError: Syntax Error in INSERT INTO statement.

Is it possible to use the mx.DateTime type for MSAccess databases? If so,
how should it be done? If not, how can I insert dates into a MSAccess
database?
That's strange: if you pass in an mxDateTime value as bound
parameter to .execute() then underlying ODBC driver will take
care of converting it to the format needed by the database.

If that fails for MS Access, then something in their driver
must be broken or the driver is requesting the date value
as string which it really shouldn't.

As work-around you could convert the mxDateTime value
to a string in the needed format and pass the string
instead of the original value.
Thank you in advance, Fons

--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Aug 01 2003)

Python/Zope Products & Consulting ... http://www.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________