MySQL Forums
Forum List  »  Perl

Re: LOAD DATA LOCAL INFILE ... SET ...
Posted by: Randy Clamons
Date: October 12, 2010 04:52PM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: LOAD DATA LOCAL INFILE ... SET ...
October 12, 2010 04:52PM


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.