SQL XOP errors

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

Correct Data as imported via MySQLworkbench

I think I might have found the solution.  In MySQLworkbench there was an option set:

set sql_mode='';
 

I tested this without this setting and it appears to give the same date error code on loading.

 

Andy

Forum

Support

Gallery

Igor Pro 10

Learn More

Igor XOP Toolkit

Learn More

Igor NIDAQ Tools MX

Learn More