[RESOLVED] sending 400MB text file to MySQL and retrieve it from web

[farhan26]

hi,
i have an issue to upload such big file-400MB text file to MySQL database.
furthermore, this file should be downloadable from the web in ZIP format and also can be viewed in the web.the user can choose to download the ZIp format or just look it at the web..

please help.

thanks.

[PeejAvery]

i have an issue to upload such big file

So what is your issue? What code do you have already? What are your MySQL server settings for file reading?

[farhan26]

here i post the script.. the cell.qz size is almost 400MB. so, how can i handle this such big file ?
how can i put it in the database? as a file?what the datatype to put this file ? text?blob?

after that, how could i the user download it in zip file ?

is there any other better solution for this ?


#pushfile.pl
#!/usr/bin/perl5.87
#Mohd Farhan


use strict;
use warnings;
use DBI;

#make a connection to database
my $database = DBI->connect("DBI:mysql:tool","...","...");


#the file
my $file = "/nfs/png/home/john/power/cell.gz";
my $data;
#assign to file handle
open (FH,"$file") || die "Cant open the file: $!\n";
{

local $/ = undef;
$data = <FH>;
}
close (FH);


#sql insert statement
#inserting the file to "BigData" table in "tool" database

my $sql = "INSERT INTO BigData (File) VALUES ('$data')";

my $sth = $database->prepare($sql);
my $row = $sth->execute($data);

# We are done with the statement handle
$sth->finish();
# I am finished with this connection to the database
$database->disconnect();


when i run this script, the error occured..
"DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed at pushfile.pl line 31."

what is that mean?


thanks...

[PeejAvery]

Well, I work with PHP so mmetzger is going to have to help you out with this one.

Concerning zipping it, if your users want to download 400Mb, you can use CGI ZIP.

[farhan26]

i did change the sql statement. because the previous scripts was't working..

my $sql = "INSERT INTO BigData (File) VALUES (?)";

when i run the scripts, it takes long time to send the data.and the error occured.


"DBD::mysql::st execute failed: Got a packet bigger than 'max_allowed_packet' bytes at pushfile.pl line 32."

how to solve this problem? i set the datatype for the file at database as blob. i know, because the file is too big..but, i dont have any idea how to solve it..

thanks.

[farhan26]

i manage to split this file and send them to MySQL database.

split script:

#splitfile.pl
#!/usr/bin/perl5.87
#Mohd Farhan
#splitting the data.txt file(319MB) into several file, sized less than 8MB each.

use strict;
use warnings;

my $atime = time;

#the file
my $file = "/disk1/cgi-bin/custom/pfile/data.txt";
my @data;
my @current;
my $limitline = 35000; #max line

open (FH,"$file") or die "Can't open the file: $!\n";

my $count = 1;
my $flag = 1;

while (<FH>){
push(@data,$_);

$count++;
if ($count >= $limitline) {
$count = 1;
open(FILE,"> file${flag}.txt"); #write to separate file
print FILE @data;
close(FILE);
$flag++;
@data = ();
print "Written to file${flag}.txt! \n";
}
}

close FH;

my $btime = time - $atime;

print "This script took $btime seconds to finish!\n";
# end if script.

--- it took 170 seconds to split 319MB files---
--- created : 56 files.(each files has size less than 8MB, which is the max allowable packet for MySQL server)---


the script for sending the files to MySQL :

#sendfile.pl
#!/usr/bin/perl5.87
#Mohd Farhan
#sending the splitted files(from pfile directory) to MYSQL database.
#the original file so huge, that's why need to be splitted to less than
# 8MB in size of each file.
#the MySQL server maximum allowable packet is 8MB.

use strict;
use warnings;
use DBI;

my $FILE;
my $sthinsert;
my $out;
my $num;
my $filename;


#make a connection to database
my $dbh = DBI->connect("DBI:mysql:tool","...","...");


my $i;
#sql insert statement
#inserting the file to "tableD" table

for ($i=1;$i <= 56;$i++) {

$filename = "file${i}.txt"; #the file name is file1.txt,file2.txt ...file56.txt

print "inserting $filename to tableD table","\n";

$sthinsert = $dbh->prepare("INSERT INTO tableD (File) VALUES (?)");

$FILE = "/disk1/cgi-bin/custom/$filename";

open (RD,"$FILE") || die "cant open :$!\n";

while (<RD>){
$out .= $_;

}
close RD;

$sthinsert->execute($out);
$sthinsert->finish();
$out = ();
}

$dbh->disconnect();

#end of script.

for the web part, i'm still working on it..