Hi
I have written a script which inserts a file (pdf) into the mysql database using DBI. Now in my cgi script I want to create a link in my webpage clicking which will read the file from the database and show it.
Basically I can not keep the file in the server hence I am inserting it into the database. The link in my cgi script will look something like below :
<a href = "Manual.pdf" target = _blank>PDF </a>
I am giving my script here. Please notice I have created a database table where I have inserted the whole Manual.pdf file by small chunks of bytes.
Please suggest me how can I read the file from there to show in the a href link.
Thanks.
use strict;
use warnings;
use DBI;
my $driver = 'mysql';
print "env home $ENV{HOME} \n";
my $dbh = DBI->connect("DBI:$driver:database=ST;host=mysql;port=3306;
+"
. "mysql_read_default_file=$ENV{HOME}/.my.cnf",
"user", "p123");
$dbh->do(qq{CREATE TABLE IF NOT EXISTS software_repos
(id INT not null auto_increment primary key,
name varchar(50) not null,
description varchar(250),
vers varchar(15),
bin mediumblob,
filename varchar(50) not null,
username varchar(30) not null,
updated timestamp(14) not null,
key name(name),
unique key idname (id, name)
)});
my $name = "data";
my $filename = "Manual.pdf";
my $version = "1.0";
my $description = "pdf";
&upload($name,$filename,$version,$description);
sub upload {
my ($sname, $fname, $vers, $descr) = @_;
open FILE, "< $fname" or die "can't open $fname\n";
my $maxlen = getmaxlen(); # gets the value of max_allowed_packet
my $bytes=$maxlen;
$fname =~ s{.*/}{}; # removes the path from the file name
print "$fname\n";
my $sth = $dbh->prepare(qq{
INSERT INTO software_repos
(name, vers, bin, description, filename, username, updated)
VALUES ( ?, ?, ?, ?, ?, user(), NULL)});
# before uploading, we delete the package with the same name
remove($sname);
# now we read the file and upload it piece by piece
while ($bytes) {
read FILE, $bytes,$maxlen;
$sth->execute( $sname, $vers, $bytes, $descr, $fname)
if $bytes;
}
close FILE;
}
sub getmaxlen {
my $rows = $dbh->selectall_arrayref(
qq{show variables LIKE "max_allowed_packet"});
for (@$rows) {
# returns the max_allowed_packet
# minus a safely calculated size
print "Inside for \n";
return $_->[1] - 100_000
}
die "max packet length not found \n";
}
sub remove {
my $sname = shift;
$dbh->do(qq{ delete from software_repos
where name = "$sname"});
}
$dbh->disconnect();