SQL XOP errors

Thu, 02/28/2019 - 09:18 am

Hi,

I am working with IP8 to load some data into an MySQL data base.

Basically I am running through a list of files and doing a data load.

    For(index=1;index<maxindex;index+=1)
        statement = "load data infile '/Volumes/Patents/MySQL/"+StringFromList(index, thefiles)+"' INTO TABLE patents.ipcr FIELDS TERMINATED BY"
        if(!index)
            Statement+= "'\t'IGNORE 1 LINES;"
        else
            Statement +="'\t';"
        endif
        SQLHighLevelOp /CSTR={connectionStr,SQL_DRIVER_COMPLETE} /O /E=1 statement
    endfor

This is returning an error.

  [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.12]Incorrect date value: '0000-00-00' for column 'action_date' at row 1

  SQL XOP: SQLExecute failed.

However if I execute this in MySQLworkbench.

LOAD DATA INFILE '/Volumes/Patents/MySQL/xaa.tsv' INTO TABLE patents.ipcr FIELDS TERMINATED BY '\t' IGNORE 1 LINES;

It works as expected as seen by the data table in the image. On the right hand side you see 0000-00-00 entered into the action date column. This is correct a represents a missing value. This is the correct and desired behavior.

Note: If statement on index is to prevent column headings from the data file being entered as data.

Background: MySQL server is on the same computer. And I have loaded many files successfully. I am recreating the patent database to do some analytics.

How do I resolve?

Andy