I'm trying to enter a date value into a date field of our AS400/DB2 via JayDeBe.

When I try with a literal SQL value everything works fine

curs.execute('''
    UPDATE mytable
    SET city = ?,
        mydate = date(to_date('1999-05-03', 'YYYY-MM-DD'))
    WHERE id = ?''', ('Somewhere', 5))

When I put the date value into a parameter

curs.execute('''
    UPDATE mytable
    SET city = ?,
        mydate = date(to_date(?, 'YYYY-MM-DD'))
    WHERE id = ?''', ('Somewhere', '1999-05-03', 5))

I get the error:

[..]
File "/usr/local/lib/python3.6/dist-packages/py4j/protocol.py", line 320, in get_return_value
format(target_id, ".", name), value)
py4j.protocol.Py4JJavaError: An error occurred while calling o1.prepareStatement.
: java.sql.SQLException: [SQL0171] Argument 01 der Funktion TO_DATE ungültig.
[..]

which means 'argument 01 of function TO_DATE is invalid'.

When I use a datetime.date value as Parameter

curs.execute('''
    UPDATE mytable
    SET city = ?,
        mydate = ?
    WHERE id = ?''', ('Somewhere', datetime.date.today(), 5))

I get the error

File "/usr/local/lib/python3.6/dist-packages/py4j/protocol.py", line 290, in get_command_part
command_part = REFERENCE_TYPE + parameter._get_object_id()
AttributeError: 'datetime.date' object has no attribute '_get_object_id'

Has anyone succeeded in storing dates into an AS400? Or any suggestions what I could try to do?

Regards Volker

Solution

After trying a little bit more I found that

curs.execute('''
    UPDATE mytable
    SET city = ?,
        mydate = date(cast(? as varchar(10)))
    WHERE id = ?''', ('Somewhere', '1999-03-05', 5))

or

curs.execute('''
    UPDATE mytable
    SET city = ?,
        mydate = date(cast(? as varchar(10)))
    WHERE id = ?''', ('Somewhere', datetime.date.today().isoformat(), 5))

that means providing an ISO-8601-string and doing a date(cast(? as varchar(10)), works as desired.

In case of a timestamp field and a datetime.datetime variable you should cast to varchar(26).

And the other possible solution with tinkering with SQL-snippets and variables as text

curs.execute("""
    UPDATE mytable
    SET city = ?,
        mydate = '""" + datetime.date.today().isoformat() +
    "'\nWHERE id = ?", ('Somewhere', 5))

is very bad practice, since it leads - not in this case with a date - to possible SQL-injections and errors with single quotes in text variables and is nearly unreadable.