Perl to upload blob file to Mysql

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

Hi there, I'm trying to write a little perl module that would upload log tarballs to mysql database. However I'm having issues, because it doesn't seem to upload the content of the file at all. Here is my code:

open(TARFILE, "< $domain_path/$file_name");
while(<TARFILE>)
    {
         $filedata = $filedata . $_;
    }
$db_handle = DBI->connect('DBI:mysql:PMCONTROL:host:port', 'db', 'pass
+word', { RaiseError => 1, AutoCommit => 1});
my $sth = $db_handle->prepare("insert into LOGS (Domain, ID, Batch, LO
+G_Type, File_name, Comment, File_Data) values(?,?,?,?,?,?,?)");
$sth->execute($doma, $d_id, $entry->{batch}, $entry->{log_type}, $file
+_name, $entry->{comment}, $filedata);
$sth->finish();
$db_handle->disconnect();
[download]

Can anybody give me any idea of how to do it properly. thanks.

Edit by BazB: remove user, host, database and password info.

Comment on Perl to upload blob file to Mysql Download Code

Replies are listed 'Best First'.
Re: Perl to upload blob file to Mysql by rnahi (Curate) on Aug 29, 2005 at 18:03 UTC
Look at the Monastery's Tutorials. There is a great example of what you need at Handling huge BLOB fields with DBI and MySQL.	[reply]
Re^2: Perl to upload blob file to Mysql by xspikx (Acolyte) on Aug 29, 2005 at 18:07 UTC
Thank you	[reply]
Re: Perl to upload blob file to Mysql by cfreak (Chaplain) on Aug 29, 2005 at 17:52 UTC
Not without an error message. You have RaiseError=>1 as an option. What does it say? If nothing, the most likely thing is you aren't getting anything in $filedata, have you checked it?. You're not checking to see if the call to `open()` fails either, that's another likely culprit. Lobster Aliens Are attacking the world!	[reply] [d/l]
Re^2: Perl to upload blob file to Mysql by xspikx (Acolyte) on Aug 29, 2005 at 18:05 UTC
I did print out the content of $filedata and it contains the binary code. same as I would cat the file itself. Does not error out at all.	[reply]
Re: Perl to upload blob file to Mysql by fmerges (Chaplain) on Aug 29, 2005 at 18:19 UTC
Hi, It's not about the BLOB, is about code... You can make the same effect but without using the while loop. `my $filedata; { local $/; open TARFILE, "$domain_path/$file_name"; $filedata = <TARFILE>; close TARFILE; }` [download] Concerning BLOB and DB, take a look at the links posted before. Ahh, and next time, use something like $host, $user, $password. ;-) So you don't get the surprise! Regards, \|fire\| at irc.freenode.net	[reply] [d/l]
Re^2: Perl to upload blob file to Mysql by xspikx (Acolyte) on Aug 29, 2005 at 18:42 UTC
oh god. tell me i didn't put the pass in the code. ahh... i'm gonna kill myself now. :(	[reply]
Re^2: Perl to upload blob file to Mysql by blazar (Canon) on Aug 31, 2005 at 09:39 UTC
Only a cosmetic suggestion: `my $filedata = do { local $/; open my $tarfile, '<:raw', "$domain_path/$file_name" or die $!; # this particular line not that cosmetic... <$tarfile>; };` [download]	[reply] [d/l]
Re: Perl to upload blob file to Mysql by jZed (Prior) on Aug 29, 2005 at 17:56 UTC
Binmode the file after opening. Read the file using read(). If needed, specify the data type with bind_param() when inserting.	[reply]