SQL XOP errors

Share icon

hegedus 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

Correct Data as imported via MySQLworkbench

hegedus

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

February 28, 2019 at 09:44 am - Permalink

Forum

Support

Gallery