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

Hi,

Can anyone point me in the right direction to find a SIMPLE example of storing a file in a blob field of a MySQL database and then retrieving it to display it in its original format.

Been googling for hours!

Thanks.

Replies are listed 'Best First'.
Re: BLOBs
by exussum0 (Vicar) on Jun 18, 2004 at 12:38 UTC
    this may help. Googled it myself. :)
    (Update) - In case the page is ever down or gone... here is JUST the example w/o the text, thank you raditha.com. 
    
use DBI;

our $dbh = DBI->connect('DBI:mysql:filemanager:localhost',
                       'visual',  # user name
                       'not4you',  # password
                       { RaiseError => 1 });

my $sth= $dbh->prepare(
"INSERT INTO uploadedFiles(fileType,fileName,fileSize,fileData) VALUES
+ ( ?,?,?,?) ");
open(my $fh, 'index.gif' ) or die $!;
binmode $fh; #Update, added for podmaster by sporty to the example
read( $fh, $var, -s $fh );
$sth->execute('image/gif','index.gif',1471,$var);

    [download]

    -- Bart: God, Schmod. I want my monkey-man.

[reply]
[d/l]

      It might also be wise to include the following text from the link.

      Before we get any further it should be noted that database is not the best place to store a file. Usually when reading or writing to a blob field the entire contents are held in memory. If you are concerned about scalability forget that such a thing called blobs exist. Having said that though it's possible at least with java to pass in an input stream instead of a byte array as the parameter
      Examine what is said, not who speaks.
      "Efficiency is intelligent laziness." -David Dunham
      "Think for yourself!" - Abigail
      "Memory, processor, disk in that order on the hardware side. Algorithm, algoritm, algorithm on the code side." - tachyon
[reply]
        Definitely. 100% agreed. Use NFS or something. But sometimes, it's not a bad idea for tiny data, like encryption keys.

        -- Bart: God, Schmod. I want my monkey-man.

[reply]
        Need it to be secure though, don't have any protected folders, so only allow access to files in the db with username and password
[reply]
      Yes I binmode, and so should YOU :)

      You might run into "issues" if you don't binmode, so it's good to always binmode 

      ...
open(my $fh, 'index.gif' ) or die $!;
binmode $fh;
..

      [download]

      MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
      I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
      ** The third rule of perl club is a statement of fact: pod is sexy.

[reply]
[d/l]
        I binmode therefore I am

        pelagic
[reply]
        Well, alternatively you can make your program die before you get burned by binmode if you expect funny stuff to work, like sending "\n" to sockets, using semget extensively, and opening a pipe to "/usr/lib/sendmail" for all your mailing needs.

        every time i see this 'binmode' I remember how backwards OSs can be... *sigh*.

        -nuffin
        zz zZ Z Z #!perl
[reply]
      doesn't tell you how to get it back though...
[reply]
Re: BLOBs
by cchampion (Curate) on Jun 18, 2004 at 13:09 UTC
[reply]

Back to Seekers of Perl Wisdom