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

Hi monks. I'm trying to store files using ActiveState perl 5.6, dbi, and mssqlserver. I've read the article by gmax on the subject, but the files are not huge--each of them are no more than 100K in size, so I don't think I have to break them up to upload/download. I need to store both binary & text files (pdfs, html, and gif). Can someone point me to some code or documentation on how to store and retrieve this type of data? thanks so much, Michael

Replies are listed 'Best First'.
Re: storing files with dbi & sqlserver
by gmax (Abbot) on May 04, 2003 at 20:39 UTC

    MS SQL has some different BLOB types, such as IMAGE, TEXT and NTEXT, which you can use according to your needs.

    As for inserting them into the database, you may use the same upload subroutine that you saw in my node about BLOBs, without the stuff about max length.

    However, be aware that inserting large objects in a database through DBI may be tricky. Check the DBI documentation about LongReadLen and LongTruncOK before attempting to achieve your goal.

    That aside, retrieving variables from BLOBs is not different from other types.

    # --- untested ---
$dbh->LongReadLen = 100 * 1024; # 100 KB
$dbh->LongTruncOK =0;           # will fail if the column is too big

my $sth = $dbh->prepare(qq{select image from table where id=  ? });
my $image = undef;
$sth->execute(1);
my $record = $sth->fetchrow_hashref();
$image = $record->{"image"};
$sth->finish;

    [download]

    HTH

     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
Re: storing files with dbi & sqlserver
by The Mad Hatter (Priest) on May 04, 2003 at 18:15 UTC
    Wouldn't it be easier to store the files on the filesystem somewhere and then use the database to hold their locations and any metadata? That way you can lookup the file in the DB, get the location, and grab it from the harddrive.
[reply]
      Depending on how the system is architected, storing files on disk may not be appropriate. In large systems, SQL server usually lives on its own box or a cluster of boxes, and application that uses this SQL server has no direct access to the disk(s).

      Also, if you store file names only, you can't easily delete files via SQL statements. You'd either need a separate application for that or you'd have to write some sort of stored procedure. This is not necessarily bad, it's just something that you'd have to keep in mind.

      --perlplexer
[reply]
      Yes, it would be much better, but our client doesn't seem to think so. it seems so pointless to store all these files when the hard disk is the perfect repository. michael
[reply]
        Ah, I see. You should really try to convince the client otherwise. You, after all, are the one with the experience and knowledge; they shouldn't be telling you what is best.

        In any case, sorry I can't help. I don't do work with Microsoft products and haven't ever stored files in a DB. If you were using MySQL though, you'd probably want to use a blob for the column type. I believe it can hold binary or ASCII data. I don't know if MSSQL has that type.

[reply]

Back to Seekers of Perl Wisdom