468,278 Members | 1,566 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,278 developers. It's quick & easy.

Problem with inserting dates using mx.ODBC.Windows driver

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
1 3058
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.

Similar topics

5 posts views Thread by SerGioGio | last post: by
1 post views Thread by php newbie | last post: by
reply views Thread by Jindrich Prchal | last post: by
3 posts views Thread by Wescotte | last post: by
2 posts views Thread by Frank Millman | last post: by
reply views Thread by zattat | last post: by
1 post views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.