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

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

      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
        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.

        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
      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; ..

      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.

        I binmode therefore I am

        pelagic
        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
      doesn't tell you how to get it back though...
Re: BLOBs
by cchampion (Curate) on Jun 18, 2004 at 13:09 UTC