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

Greetings

I am looking for some venerable guidance. My task, which has been well thought out(!), is to insert binary data into MySQL using Perl, naturally.

I wish to divide up any binary files into 1MB chunks and insert that into a LONGBLOB field in MySQL.

1) My question(s) is/are which Perl module will help me best to take a binary file and divide it up appropriately... and then at a later specified time re-assemble it back into its original format.

2) Also when inserting the chunks into MySQL what processing do I need to do on the file to make sure it goes in okay, a sort of Perl equivalent of the php addslashes function I suppose?

Thanks if you can spare the time to answer my question.

Replies are listed 'Best First'.
Re: Inserting binary data into MySQL
by blokhead (Monsignor) on Sep 17, 2003 at 00:29 UTC
    1. You don't need anything extra special to read 1MB chunks of a file at a time, the read function can do that quite easily.
    2. If you use placeholders in your SQL (and you always always always should whenever possible), you never have to worry about special/dangerous characters in the data you're inserting into the DB.

    Here's a little demo that reads 1MB at a time, and inserts it into the DB using placeholders. 

    open my $fh, 'big-binary-data.file'
    or die "Couldn't open file: $!";

# prepares an SQL statement with data to be filled in later
my $sql = "insert into table set chunk_num=?, data=?";
my $sth = $dbh->prepare($sql) or die "Couldn't prepare sql statement: 
+$!";

my $chunk_num = 0;
my $chunk_size = 1024 * 1024;

while ( read( $fh, my $buffer, $chunk_size ) ) {
  $chunk_num++;

  # executes the statement with the appropriate data filled in
  $sth->execute( $chunk_num, $buffer )
    or die "Couldn't insert data: $!";
}

$sth->finish;

    [download]
    This should give you a decent starting place. I highly recommend reading the database tutorials here on PM (as well as the DBI documentation) if you're a little unsure of what placeholders do. After understanding them, you will quickly realize their usefulness.

    blokhead

[reply]
[d/l]
Re: Inserting binary data into MySQL
by Zaxo (Archbishop) on Sep 17, 2003 at 00:38 UTC

    You can read a file in fixed-length chunks by setting $/ to a reference to an integer 

    {
    use bytes;
    local $/ = \1048576;
    open my $fh, '<', 'bigfile.bin' or die $!;
    while (<$fh>) {
        # stuff the db
    }
}

    [download]
    To reconstruct your file you can either concatenate the blobs in memory, or write them to a file. In either case you need a way to retain their order, which the db can do if you make a column to remember it by. To check that things go ok, all you really need is to set RaiseError in the DBI handle.

    After Compline,
    Zaxo

[reply]
[d/l]
Re: Inserting binary data into MySQL
by gmax (Abbot) on Sep 17, 2003 at 06:25 UTC
[reply]

      Thanks for that. I did try searching the monastery but sometimes a little help is needed to find the gold nuggets in the cloisters.

      Cheers again

[reply]

Back to Seekers of Perl Wisdom