MySQL Forums
Forum List  »  Perl

Re: LOAD DATA LOCAL INFILE ... SET ...
Posted by: filippo guenzi
Date: October 13, 2010 02:05AM

-> 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 !!

Options: ReplyQuote


Subject
Written By
Posted
Re: LOAD DATA LOCAL INFILE ... SET ...
October 13, 2010 02:05AM


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.