Using LOAD DATA INFILE in perl to insert values ​​in MYSQL

Question

I am trying to load a CSV file into a mysql database using perl. My perl script will be on a server that I will access through my browser. The browser will be able to download the file.

The question is exactly how am I going to do this

• Do I need to save this file somewhere on the server and then use it?
• Is it possible to directly use the file from my laptop by specifying a fixed path?

so far i tried to do this

if($update eq "fullLoad"){ $filename = param("customPricing"); my $upload_filehandle = upload("customPricing"); open ( UPLOADFILE, ">$filename" ) or die "$!"; binmode UPLOADFILE; while ( <$upload_filehandle> ) { print UPLOADFILE; } close UPLOADFILE; $query = "LOAD DATA INFILE '\bigmac\bm_src\html\cgi-bin\testbrocade\$filename' INTO TABLE customPricingTest FIELDS TERMINATED BY ','"; $sth = $dbh->do($query) or die "SQL Error: $DBI::errstr\n"; print $sth . "Records Uploaded <br/>"; 

}

Edited → Above code throws an error Access is denied to the user. I can successfully create the file on the server, but it looks like I'm getting an error. Any ideas?

Thanks Nitesh

Answer 1

In response to question 1: if you are using LOAD DATA INFILE , yes, the file must be available for the server system and the database user. If you use LOAD DATA LOCAL INFILE , the file can remain on a separate system running the Perl script, and the data is uploaded to the network connection. There are differences in resolutions and settings between them, but the performance is very good in any case, and we did not see a huge difference whether the data is downloaded locally or remotely.

On the second question, this means LOAD DATA LOCAL INFILE . Here, since the file is read and transmitted via the DBI connection, the file path should be relative to where the script is being executed. It should not be absolute in the way it would be for LOAD DATA INFILE . This does not even have to be a fixed path. We have successfully used temporary files for this purpose until the files are deleted until you disconnect from the database.