Re: LOAD DATA LOCAL INFILE ... SET ...
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. 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.
You haven't mentioned your version of MySql. 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);
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.