gmacfadden has asked for the wisdom of the Perl Monks concerning the following question:

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 ;

Replies are listed 'Best First'.
Re: downloading files from mySQL
by Joost (Canon) on Oct 31, 2006 at 22:25 UTC
    Your post is pretty hard to read. Please read writeup formatting tips.

    Anyway, here's a reasonable start: 

    # .. get data from blob into $blob_field.
print "Content-type: application/octet-stream\n"; # or use the right c
+ontent-type for the field if you know it
print "Content-disposition: attachment; filename=somefile.ext\n\n";
binmode STDOUT;
print $blob_field;

    [download]
    I have no idea what LWP has to do with any of this, though.

    update: ah it appears you want a "file-download field" in your form. There is no such thing as such in HTML. My code above will work fine if you link to it though - it will (or rather, should) cause the browser to open a "save as..." file selector and save the content to that file. You can only send one file per request this way, so your best bet is to create a list of links for each blob field. In other words, the script above should be called as a CGI for each field.

    "What should it profit a man, if he should win a flame war, yet lose his cool?"
[reply]
[d/l]
      Very helpful, thank you.
[reply]
      I implemented the solution proposed by Joost (q.v. my code below) and while it works, it's behavior is not what I extpected!

      When I run this program, in lieu of fetching the file and saving the file on my computer hard drive (where it could later be opened by the proper applications), my below program dumps the binary file to my browser screen where it shows as gobble-gook.

      I've worked to clean up the readability from before. Now I'm hopeful someone can please tell me how to tweek the below code to save the fetched file into a file in a Windows My Documents directory or some other directory? Thank you Monks. 

      #! /usr/bin/perl -wT
use strict;    #force all variables to be declared before use
use DBI;    # import database interface methods
use CGI qw(:standard);    
use CGI::Carp qw(warningsToBrowser fatalsToBrowser); 
use lib qw(/home/gmacfadd/public_html/cgi-bin/WorkControl); use WorkCo
+ntrolDB;

print header (),    # use the text/html (default) Content-type headet
start_html ();
my $dbh = WorkControlDB::connect ();
my ($sth, $stmt, $row, $blob_field);
$stmt = qq { SELECT * FROM testblob WHERE id = ?};
$sth = $dbh->prepare ($stmt);    # tell DBI what we are doing via prep
+are method
$sth->execute ("1"); # search for record with id = given number
$row = $sth->fetchrow_hashref();
$sth->finish ();        # close the DBI result set

$blob_field = $row->{file};     # .. get data from blob into $blob_fie
+ld.
print "Content-type: $row->{mime_type}\n"; # use content-type for the 
+field
print "Content-disposition: attachment; filename=somefile.bmp\n\n";
binmode STDOUT;
print $blob_field;
print end_html ();
exit (0);

      [download]
[reply]
[d/l]
[reply]
Re: downloading files from mySQL
by derby (Abbot) on Oct 31, 2006 at 22:29 UTC

    Your web page cannot go outside of the browser sandbox and write to a users filesystem. The best you can do is send the data with the appropriate mimetype (and filename) and hope the user is smart enough to save it where it needs to be (see Launching Excel from a CGI for sending the mimetype and setting the filename).

    -derby
[reply]

Back to Seekers of Perl Wisdom