I discovered tonight when I was cleaning up my DB, that I never altered the setfiletest table to change the sDate from VARCHAR to DATETIME. When I wrote the code for my client app to perform this function, I used the same statement as list in my last post here. This caused an error because the Date was the incorrect format.
After a while of consideration and re-reading the Reference manual, I noticed that the statement:
mysql> load data infile 'C:/test/scanner/setup/5015709.set' into table setfiletest
-> FIELDS TERMINATED BY '\r\n'
-> SET sDate=Str_to_Date(sDate,'%m/%d/%Y %r');
was actually doing things in the following order:
1st: Reading and inserting the data in the file
2nd: Changing the sDate value to the Str_to_Date() result
Because of this, it was trying to insert the original "8/6/2010 9:57:05 PM" first, which of course failed.
The proper way (or at least a way that so far produces no errors...) is
mysql> show create table machTallySet;
+--------------+------------------------------
| Table | Create Table
+--------------+------------------------------
| machTallySet | CREATE TABLE `machtallyset` (
`ticknum` varchar(20) NOT NULL,
`sDate` datetime DEFAULT NULL,
`specieLong` varchar(45) DEFAULT NULL,
`custLong` varchar(45) DEFAULT NULL,
`workOrder` int(11) DEFAULT NULL,
`gradeAbrv` varchar(45) DEFAULT NULL,
`thickness` double DEFAULT NULL,
`setMaxLen` int(11) DEFAULT NULL,
PRIMARY KEY (`ticknum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+------------------------------
1 row in set (0.00 sec)
mysql> load data infile 'c:/test/scanner/setup/1013444.set' into table
-> machtallyset fields terminated by '\r\n' ignore 1 lines (ticknum, @Var1,
-> specieLong, custLong, workOrder, gradeAbrv, thickness, setMaxLen)
-> SET sDate=Str_to_date(@Var1,'%m/%d/%Y %r');
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
The difference being, I assigned a variable "@Var1" to the sDate column then defined it in the SET statement rather than SET sDate=*sDate*.
This evidently runs the steps above in reverse, allowing the date field to be modified before it tries to insert instead of after.
Hope this makes sense. I didnt want to cause confusion with the last post indicating my statement worked. (It only worked because I forgot to set my table up.
Anyway, thanks for reading. G'night