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>

[download]

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

[download]

In reply to How to read a stored file from database and show in browser by ghosh123

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.