AS400 integration via JDBC and dates in the data.

[Former Member]

Ive come across a problem when using PI JDBC adapter to write data to a AS400 using SQL. When a date field is involved we have found that if the <mydate> tag is removed as the date isnt set when the data is displayed in the AS400 it has current date. We have since changed this mapping to always put a date and when it isnt set we default in 0001-01-01 (known default for date in the AS400).

What Im trying to understand is - if there is no date tag in the outbound XML, is this default date of today coming from the adapter engine interaction with the AS400 or is it the AS400 SQL engine doing it.

The date is not part of any key field and I would have expected if NO TAG is passed for the field it would use 0001-01-01 not put current_date into the field.

Does anyone have insight around this behaviour, its worth noting if using an interactive session on the AS400 and creating a record this will default 0001-01-01 but when sent via PI SQL/JDBC adapter the result for a tag that doesnt exist is current_date.

[former_member192343]

Hi, Richard

What Im trying to understand is - if there is no date tag in the outbound XML, is this default date of today coming from the adapter engine interaction with the AS400 or is it the AS400 SQL engine doing it

I think the AS400 SQL engine doing it or jdbc driver.

Try to test SQL query exactly on DB side without setting date, what it would be?

Also test it with jdbc driver (for example in SAP NWDS, DB development view).

Regards, Mikhail