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 8

Learn More

Igor XOP Toolkit

Learn More

Igor NIDAQ Tools MX

Learn More