MySQL Forums
Forum List  »  Newbie

Re: 1 other thing to correct this issue
Posted by: Chris Rife
Date: August 10, 2010 09:16PM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: 1 other thing to correct this issue
August 10, 2010 09:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.