I have a mySQL database (q.v. phpMyAdmin SQL Dump below), which has a blob field defined in each row for a uploaded text or binary file. Uploading the files to mySQL was easy. Now I'm trying to download them so they can be used by the applications on the client. That's where it gets sticky and also where my research has come up with nothing helpful, so am turning to you. In the below Perl code, this field containing the file is referenced as $row->{file}. My requirement is to simply download this file and store it on my client. Ideally, the download would act reciprocally to the behavior of CGI's filefield (-name => "object_to_be_uploaded", -size => 80) - asking for a directory location and a file name. I don't need to do any heavy lifting such as opening, editting, etc. the downloaded files inside the script. As such don't need Win32::OLE (besides it's not loaded on my web host). LWP download capability looks too light (besides as LWP requires, the file to be downloaded is not in a URL (it's already in a mySQL field). Please help. 
#! /usr/bin/perl -wT
use strict;    #force all variables to be declared before use
use DBI;    # import database interface methods
use CGI qw(:standard escape escapeHTML);    
use CGI::Carp qw(warningsToBrowser fatalsToBrowser); 
use lib qw(/home/gmacfadd/public_html/cgi-bin/WorkControl); 
use WorkControlDB;
print header (),    # use the text/html (default) Content-type headet
start_html ();
my $dbh = WorkControlDB::connect ();
my @row3;                         #declare an array for listing of tas
+k items
push (@row3, qq(<table border=3 bordercolor="blue" align="left"
      bgcolor="rgb(153,255,153)">) );
#@ MAIN_LOGIC DISPATCHING LOGIC
my $choice = lc (param ("choice"));    # return lowercased version of 
+string
if ($choice eq "")            # initial script invocation
       {display_current_items ($dbh);
        print table(@row3);}
elsif ($choice eq "delete")    # else if delete record was selected...
+.
       {if (!delete_item($dbh, param("id")))    # if delete was not su
+ccessful 
        { print p ("No record id" . param ("id") . " was found.") }
        else             # else (record was not found), so 
        {display_current_items ($dbh);
             print table(@row3);}
       }
else
       {print p ("Logic error, unknown choice: $choice");}
$dbh->disconnect ();
print end_html ();
exit (0);

#@ DISPLAY_CURRENT_ITEMS
sub display_current_items
{
my $dbh = shift;  
my ($sth, $stmt, $count);
print <<EOF;
<B><FONT COLOR=#FF0000>List Of mySQL rows</FONT></B>
EOF
$stmt = qq { SELECT * FROM testblob ORDER BY id ASC   };
$sth = $dbh->prepare ($stmt);    # tell DBI what we are preparing to d
+o
$sth->execute ();        # send the command to MySQL
$count = 0;
while (my $row = $sth->fetchrow_hashref ())    
    {display_item ($row);
        ++$count;}
$sth->finish ();                
print p ("No items were found") if $count == 0;
}
#@ END DISPLAY_CURRENT_ITEMS
#@ START DISPLAY_ITEM
sub display_item
{
my $row = shift;    
my $delete_url = sprintf ("%s?choice=delete;id=%d", url (), $row->{id}
+);
push (@row3, Tr (
     qq(<TD ROWSPAN=4 ALIGN="LEFT" VALIGN="CENTER" BGCOLOR="rgb(153,25
+5,153)" COLSPAN="1">), 
     qq(<B>mySQL row #: </B><FONT COLOR="rgb(0,0,255)">$row->{id}</FON
+T>),
     qq(<TD ALIGN="LEFT" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COLSP
+AN="1">), 
     qq(<B>Filename: </B><FONT COLOR="rgb(255,0,0)">$row->{filename}</
+FONT>),
     qq(<TD ALIGN="LEFT" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COLSP
+AN="1">), 
   qq(<B>Mime-Type: </B><FONT COLOR="rgb(0,0,255)">$row->{mime_type}</
+FONT>)));
push (@row3, Tr (
     qq(<TD ALIGN="CENTER" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COL
+SPAN="2">), 
     qq(<FONT COLOR="rgb(255,0,255)">),
     a({-href=>$delete_url}, "Click here to delete this mySQL entry"),
     qq(</FONT>) ) );
push (@row3, Tr (
     qq(<TD ALIGN="CENTER" VALIGN="TOP" BGCOLOR="rgb(153,255,153)" COL
+SPAN="2">), 
     qq(<FONT COLOR="rgb(255,0,255)">),
######-after here insert logic here to download file in $row->{file}
     "insert link reserved for download here",


#####
     qq(</FONT>) ) );

push (@row3, Tr (
     qq(<TD ALIGN="CENTER" VALIGN="TOP" BGCOLOR="rgb(153,204,204)" COL
+SPAN="6">),
     qq (<FONT COLOR="rgb(0,0,0)">end of this record</FONT>) ) );
}
#@ END DISPLAY_ITEM
#@ START DELETE_ITEM
sub delete_item
{
my ($dbh, $id) = @_;    # @_ contains the parameters passed to that su
+broutine
return ($dbh->do(qq{DELETE FROM testblob WHERE id = ?},undef,$id)); 
# return 0 if deletion of this record "id" was successful 
}
#@ END DELETE_ITEM

[download]
CREATE TABLE `testblob` (
`id` int(2) unsigned NOT NULL auto_increment,
`filename` varchar(60) NOT NULL default '',
`file` longblob NOT NULL,
`thumbnail` blob NOT NULL,
`mime_type` varchar(40) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7 AUTO_INCREMENT=7 ;

In reply to downloading files from mySQL by gmacfadden

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.