JayDeBe: Store a date into an AS400/DB2 (jt400 driver)

Question

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

try to use a variable string as parameter with format string like it : 'yyyy-MM-dd' — Esperento57, Mar 07 '18 at 08:14
@Esperento57 That's what I did in my second example. If you meant, I should use a bare '?' in SQL (`.., mydate = ? WHERE ...`) and the the ISO-formatted String as parameter; that leads to the error 'java.sql.SQLException: Data type mismatch. (1999-05-03)'. — Volker Böhm, Mar 07 '18 at 08:54
Since I use the _JayDeBe_ driver which is a proxy for **python** to use a **java-JDBC-library** I dont know exactly to what java type my parameters are converted. A `string` should remain a `string` and hopefully a `datetime.date` shout become a `java.util.date`. — Volker Böhm, Mar 07 '18 at 09:10
hum may be a driver problem too. Peraps can you build completely your query with concatenation before to run curs.execute ? — Esperento57, Mar 07 '18 at 09:24
i think datetime.date.today() dont give a good format. Try to build a string with format 'yyyy/MM/dd' — Esperento57, Mar 07 '18 at 09:30
In both assignment (`mydate = ?` and `mydate = date(to_date(?, 'YYYY-MM-DD'))` resp. `mydate = date(to_date(?, 'YYYY/MM/DD'))`) I get the same errors when I use `1999-05-03` or `1999/05/03` as Parameter. `java.sql.SQLException: Data type mismatch` for `mydate = ?` and `java.sql.SQLException: [SQL0171] Argument 01 der Funktion TO_DATE ungültig.` for `mydate = date(to_date(?, 'YYYY-MM-DD'))` — Volker Böhm, Mar 07 '18 at 10:10
dont use argument '?', try to build the query your-self with concatenation — Esperento57, Mar 07 '18 at 10:31

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

JayDeBe: Store a date into an AS400/DB2 (jt400 driver)

1 Answers1

Solution