LOAD DATA LOCAL INFILE ... SET ...
Hi,
I have the following PERL script :
------------------------------------------------------------------------------
#!/usr/bin/perl
###
### simply calls LOAD DATA INFILE for specified database, tablename and csv file
###
use DBI;
use Getopt::Long;
GetOptions( "host=s" => \$hostname,
"port=i" => \$port,
"username=s" => \$username,
"password=s" => \$password,
"database=s" => \$database,
"file=s" => \$file,
"table=s" => \$table);
#data file MUST be specified (plus we substitute all occurances of \ with a /)
#die "No path to *.csv file specified!!!" unless defined $file;
#die "No table name specified!!!" unless defined $table;
#$file =~ s/\\/\//g;
#here we set default DB connection parameters
if (not defined $hostname) {$hostname = "localhost";}
if (not defined $port) {$port = "3306";}
if (not defined $username) {$username = "root";}
if (not defined $password) {$password = '=mi0Scul0=';}
if (not defined $database) {$database = "";}
#connect to database
$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
$dbh = DBI->connect($dsn, $username, $password) or die("Could not connect to database '$database'!");
#create and execute SQL queries
$sql = "
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)
";
$sth = $dbh->prepare($sql);
$sth->execute;
#disconnect from database
$dbh->disconnect;
------------------------------------------------------------------------------
and the following file to load:
------------------------------------------------------------------------------
aze Non zer
qsd Non sdf
wxc Oui xcv
------------------------------------------------------------------------------
into the following table:
------------------------------------------------------------------------------
'field1', 'varchar(45)'
'field2', 'varchar(45)'
'field3', 'varchar(45)'
------------------------------------------------------------------------------
While the request works under MySQL Query Browser it doesn't inside this script.
I suspect it is because of the @ : when I remove @f2 and the set instruction everything goes fine.
Do you have an idea on the reason why this isn't working?
Thank you.