I am trying to insert some data into table(mysql database) using odbc stage.
Code: Select all
sequential stage ------->TX--------->ODBC
One of the columns in the table is nullable with date as a datatype. The input for the date is a char in the input file. I am using oconv to convert that to date and assigning a default NULL for null values. The inserts for non nullable values from the input are going on fine, but for null values I am getting the below error
SQL statement:INSERT INTO table (USER_ID, MIDDLENAME, FIRSTNAME, LASTNAME, ZIPCODE, DATE, TITLE, GENDER, MIGRATION_TIMESTAMP, UPDATED) VALUES (?,?,?,?,?,?,?,?,?,?)
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client]Illegal date/time value
USER_ID = 12345
MIDDLENAME = ""
FIRSTNAME = "XXXXX"
LASTNAME = "XXX"
ZIPCODE = "123456"
DATE = NULL
TITLE = ""
GENDER = "UNKNOWN"
MIGRATION_TIMESTAMP = "2010-09-23 19:49:13"
UPDATED = "2010-09-23 19:49:13"
FYI :I am able to execute the same query with the same values with success using sql tool.
Did anyone face this issue?
I am posting this only after searching the dsxchange.com extensively.