LOAD DATA LOCAL INFILE

brin has asked for the wisdom of the Perl Monks concerning the following question:

Hello I am new to perl and was trying to load data from a text file on my hard drive and failing that, from a text file on the server, to a table in a database on the server, and neither worked.

It only worked when I went into PHPMyAdmin on the server, from a text file on my hard drive to the table, but I wanted to do it from the script as hereunder which was the script I tried to load data on a text file on the server to a table in the database. I did not receive any error notices when I brought the file up in the address and pressed enter and/or refresh. I knew it didn't work because when I refreshed the table there was no new data therein:-

#!/user/bin/perl
use CGI::Carp qw(fatalsToBrowser);
use DBI;

print "Content-type:text/html\n\n";

#open connection to access database
$dbh =
DBI->connect("DBI:mysql:bgoddard_musicexperiences:localhost","","");

#prepare and execute SQL statement

$sqlstatement="LOAD DATA LOCAL INFILE 'reas_orders.txt' INTO TABLE 're
+as_orders'
FIELDS TERMINATED BY '/' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'";
$sth = $dbh->prepare($sqlstatement);
$sth->execute or
     die "Could not execute SQL statement ... maybe invalid?";

$dbh->disconnect;
[download]

Comment on LOAD DATA LOCAL INFILE Download Code

Replies are listed 'Best First'.
Re: LOAD DATA LOCAL INFILE by davido (Cardinal) on Jul 16, 2004 at 15:54 UTC
You're not checking the success status of your DBI->connect() call. How do you know that it connected in the first place? Also, why did you leave your database username and password in the clear when you made this post? That's risky. Dave	[reply]
Re^2: LOAD DATA LOCAL INFILE by brin (Initiate) on Jul 16, 2004 at 16:23 UTC
Dave, Thanks for your prompt reply. I couldn't believe that you had replied so quickly, just seconds after I had posted the query. It's getting late here in Western Australia, so I will reply to you properly tomorrow. In answer to your question; it's because I am a fool. I have updated it now thanks and cleared the username and password. Brian	[reply]
Re: LOAD DATA LOCAL INFILE by BrowserUk (Patriarch) on Jul 16, 2004 at 16:51 UTC
I think what your being bitten by is not a perl thing nor even a DBI thing, but a MySQL limitation. From the MySQL manual section 6.4.8 The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, it is interpreted with respect to the client end of the connection. When LOCAL is specified, the file is read by the client program on the client host and sent to the server. If LOCAL is not specified, the file must be located on the server host and is read directly by the server. (LOCAL is available in MySQL Version 3.22.6 or later.) Try it without the LOCAL keyword. Examine what is said, not who speaks. "Efficiency is intelligent laziness." -David Dunham "Think for yourself!" - Abigail "Memory, processor, disk in that order on the hardware side. Algorithm, algoritm, algorithm on the code side." - tachyon	[reply]
Re: LOAD DATA LOCAL INFILE by naChoZ (Curate) on Jul 16, 2004 at 15:56 UTC
Show us a few lines from reas_orders.txt as well as your db schema. And you should probably remove your password... -- People who want to share their religious views with you almost never want you to share yours with them. naChoZ	[reply]
Re^2: LOAD DATA LOCAL INFILE by brin (Initiate) on Jul 16, 2004 at 17:00 UTC
Hello naChoZ Thanks for your prompt reply. I couldn't believe it as it was only seconds before that I had posted my query. It's getting late here in Western Australia so I will answer your comments tomorrow. brin	[reply]