Infile Works From Sqlyog But Not From Perl Or Mysql

[tinny]

I have a file that is not entirely corrected formatted. The first line is empty and the second line contains header names.

I know that this is easely solved by IGNORE 2 LINES but I am also having issues witht a double field not being correctly formatted even though I can see nothing wrong with it.

Now the thing is that SQLyog does load this file fine. It copes with all the bad data and populates all the good fields ok. It even handles the price field with the badly formated doubles fine and I end up with a correctly populated table.

When I copy paste the load data infile statement from SQLyog to either my Perl application or a mysql session I get errors like the ones below.

mysql> LOAD DATA INFILE 'c:\pnl\trades\trades.out' INTO TABLE temp_trade IGNORE 2 LINES;

ERROR 1265 (01000): Data truncated for column 'price' at row 1

mysql>

Is there any way I can make my Perl session behave as tolerant as the SQLyog session when doing the load data infile?

This works in SQLyog:

DROP TABLE IF EXISTS temp_trade;

CREATE TABLE temp_trade

(

date date,

action varchar (20),

qty int,

symbol varchar (20),

description varchar (100),

price double,

amount varchar (20),

fees double

);

LOAD DATA INFILE 'c:\pnl\trades\trades.out' INTO TABLE temp_trade;

This does not work in perl:

# make connection to database

my $dbh = DBI->connect($connectionInfo,$userid,$passwd);

#create the temp table

$dbh->do (“DROP TABLE IF EXISTS temp_trade”);

$dbh->do (“CREATE TABLE temp_trade

(

date date,

action varchar (20),

qty int,

symbol varchar (20),

description varchar (100),

price double,

amount varchar (20),

fees double

)”);

#bcp the data in

my $sql=”LOAD DATA INFILE 'c:\\pnl\\trades\\trades.out' INTO TABLE temp_trade IGNORE 2 LINES”;

[tinny]

I found the solution using the useful general log (switched on by specifying –log as a command line paramater to mysqld).

Add

$dbh->do (“set sql_mode=''”);

to your code.

[Author]

Posts