Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: Inserting binary data into MySQL

by blokhead (Monsignor)
on Sep 17, 2003 at 00:29 UTC ( [id://292007]=note: print w/replies, xml ) Need Help??


in reply to Inserting binary data into MySQL

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://292007]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2024-04-24 01:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found