Re: LOAD DATA LOCAL INFILE ... SET ...
-> I AM GOING TO WRITE CAPITAL CASE FOR YOU TO DIFFERENTIATE OUR CONTRIBUTIONS TO THE TOPIC. I AM AS CALM AS I COULD BE IN LOWER CASE. THANK YOU FOR YOUR ANSWER.
I'd be surprised if your script, as shown here, does not generate a compile-time error. Your whole sql statement is surrounded by double quotes.
-> COOL !! I THOUGHT 2BLE QUOTES AND SINGLE QUOTES WERE THE SAME. I INVERTED THEM AND ALL WORKS FINE !!
Variables referenced within will be interpolated. When you have the @ sign followed by anything other than a space Perl will try to interpolate the variable.
In this case, Perl would look for an array named @f2. There probably isn't one and that should generate an error.
The other thing is that $dbh->prepare, as far as I know, will only accept a single statement and you are passing 2 statements. I don't think that will work.
-> MY MISTAKE, THERE WAS JUSt ONE ; IN MY SCRIPT
You haven't mentioned your version of MySql.
-> MYSQL 5.1.31
If your version supports stored procedures (5.0 an up?), that would be the way to go.
Put all this in the stored procedure:
load data local infile 'c://f.txt'
into table
`test`.`table`
lines terminated by '\r\n'
(
field1,
@f2,
field3
);
set field2=replace(replace(@f2,'Non',0),'Oui',1);
-> STORED PROCEDURES DON'T ALLOW TO RUN load data infile, THAT'S PRECISELY WHY I AM TRYING TO HAVE IT DONE BY SOMEONE ELSE, HERE PERL BECAUSE SOMEONE LEFT A PERL SCRIPT ON INTERNET ALLOWING TO DO IT.
Create the stored procedure with a name like 'LoadTable'
Then $dbh->do('LoadTable') instead of prepare/execute. Use prepare/execute when rows are expected to be returned, use do when no rows are expected.
Honestly, I can't tell what you expect the second line of the procedure to do.
(set field2=replace(replace(@f2,'Non',0),'Oui',1);) That said, I don't think it's going to do what you think.
-> THANK YOU AGAIN !!