Problem with inserting dates using mx.ODBC.Windows driver

Discussion in 'Python' started by Fons Dijkstra, Jul 31, 2003.

  1. 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
     
    Fons Dijkstra, Jul 31, 2003
    #1
    1. Advertising

  2. 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/

    ________________________________________________________________________
     
    M.-A. Lemburg, Aug 1, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Dates dates dates dates... SQL and ASP.NET

    David Lozzi, Sep 29, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    904
    Rob Schieber
    Sep 30, 2005
  2. rvino

    problem with odbc driver

    rvino, Aug 18, 2007, in forum: Perl
    Replies:
    0
    Views:
    2,350
    rvino
    Aug 18, 2007
  3. PW

    Dates! Dates! Dates!

    PW, Aug 7, 2004, in forum: ASP General
    Replies:
    4
    Views:
    231
    Mark Schupp
    Aug 9, 2004
  4. Replies:
    1
    Views:
    213
    Mark Schupp
    Oct 5, 2004
  5. George  Moschovitis

    Inserting a blob using the oracle driver

    George Moschovitis, Jan 21, 2005, in forum: Ruby
    Replies:
    1
    Views:
    113
    Michael Neumann
    Jan 21, 2005
Loading...

Share This Page