Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

How to read a stored file from database and show in browser

by ghosh123 (Monk)
on Jul 11, 2013 at 15:36 UTC ( #1043763=perlquestion: print w/replies, xml ) Need Help??
ghosh123 has asked for the wisdom of the Perl Monks concerning the following question:

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.

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();

Replies are listed 'Best First'.
Re: How to read a stored file from database and show in browser
by mje (Curate) on Jul 11, 2013 at 16:20 UTC

    It is not what you asked but lines 41 to 53 look wrong to me. Firstly if that while loop is executed more than once (in otherwords if $maxlen is less than the size of the file) you will insert multiple rows into your table. You are probably getting away with this because your manual.pdf is smaller than maxlen. Also wouldn't it be better to check what read returns rather than what has been read.

    Also, what if max_allocated_packet was less than 100_000 - you've got a problem.

    Lastly, are you sure you want a unique constraint on the combination of id and name? As id is an auto increment that will mean you can have two rows with the same name. Your remove sub rather suggests you expect name to be unique but that is a guess on my part.

      Thanks for pointing out the bugs.
      Can you please help me on the displaying of that stored file in browser . Not to forget, saving the file in server by downloading from the database is not an option.

Re: How to read a stored file from database and show in browser
by poj (Prior) on Jul 11, 2013 at 18:09 UTC
    You need 2 scripts, e.g. pdf_index.cgi and pdf_display.cgi. The index page creates the links to the other cgi with an id parameter (assuming it is unique) to determine the pdf. This example puts the links in a table with the other fields.
    #!perl use strict; use CGI qw(:standard); use DBI; my $dbh = get_dbh(); # html page print header,start_html; my $sql = 'SELECT id,name,description,vers FROM software_repos'; my $sth = $dbh->prepare($sql); $sth->execute(); print q!<table border="1" cellspacing="0" cellpadding="3"> <tr> <td>ID</td> <td>Name</td> <td>Description</td> <td>Version</td> <td>PDF</td> </tr>!; while (my @f = $sth->fetchrow_array()){ print qq!<tr> <td>$f[0]</td> <td>$f[1]</td> <td>$f[2]</td> <td>$f[3]</td> <td><a href="pdf_display.cgi?id=$f[0]" target="_blank">pdf</a></td> </tr>\n!; } print q!</table>!; print end_html; # whatever you need to get a connection sub get_dbh{ my $database = ""; my $user = ""; my $pw = ""; my $dsn = "dbi:mysql:$database:localhost:3306"; my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError => 1, AutoComm +it => 1 } ); return $dbh; }
    The other script to display the pdf is relatively simple
    #!perl use strict; use CGI qw(:standard); use DBI; my $id = param('id'); my $dbh = get_dbh(); my $sql = "SELECT bin FROM software_repos WHERE id=?"; my ($pdf) = $dbh->selectrow_array($sql,undef,$id); print header('application/pdf'), binmode(STDOUT); print $pdf; # whatever you need to get a connection sub get_dbh{}

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1043763]
Approved by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2017-07-29 12:29 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (436 votes). Check out past polls.