By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

Problem with inserting dates using mx.ODBC.Windows driver

P: n/a
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
Jul 18 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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/

__________________________________________________ ______________________
Jul 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.