How to handle binary data in Perl?

[LindaLiner]

Hi, there,

I am using PerlBI to talk with SQL server. I want to get the varbinary data from database and saved as .wav file. I couldn't find useful information about handling binary data in Perl. And my code doesn't work.
Can somebody help me out?

Thank in advance!

Below is my code:
++++++++++++++++++++++++++++
$query = "select Audio_Data from English_English where Audio_Name = 'ENG_2.WAV'";
$sth = $dbh->prepare($query) or die "prepare failed\n";
$sth->execute() or die "unable to execute query $query error $DI::errstr";

my $out_audio = "ENG_2.wav";
open(OUTF, ">$out_audio") or die "Can't open $out_audio\n";

binmode(OUTF);

while(@first = $sth->fetchrow_array) {
foreach $field(@first) {
printf OUTF $field;
}
}
close(OUTF);

$sth->finish() or die "unable to finish query -- error $DI::errstr";

$dbh->disconnect;

 

[Kirsle]

What doesn't work about it? Do you get an empty wav file? A corrupted wav file? More than one record returned from the query? No records returned at all?

Cuvou.com | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[prex1]

Why are you using [tt]printf[/tt] ?

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[LindaLiner]

Thank you, Kirsle.
1. Yes, I do get an empty wav file.
2. I am not sure what happened in the database. But I know it was a good valid wav audio file before it got into database.
3. When I output the count(*) from the same query, it is ONE. So I believe it only got one wav file back.

Thank you, prex1.
I want save the binary data, which stored in SQL database, into a wav file. So I think I need use a "printf" to print the data back to a file.

Any more tips?

 

[prex1]

Well, sure not. I thought it was a mistyping, that's why I didn't elaborate further.
Of course you must use [tt]print OUTF $field;[/tt] . [tt]printf[/tt] is for formatted print, and the first argument to it (your [tt]$field[/tt] ) is interpreted as a format string.

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[LindaLiner]

I am using "print OUTF $field". Still can't get audio file saved.

Thank you for pointing that out.

 

[BrianAtWork]

Maybe you have done this, but I would make sure you are getting something returned from your database query.

Maybe inside your while loop, inside your foreach loop, do:

while(@first = $sth->fetchrow_array) {
    foreach $field(@first) {
        print STDOUT "TESTING\n";
        print OUTF $field;
    }
}

That way, if you run your program and don't see TESTING, you know that your query didn't return data. If you do see TESTING, you could also print the $field to STDOUT and see if the data looks like a mess of binary data. If it's only a couple of characters that gets printed, maybe something is wrong with the data in the table?

Also, another way to write your code without the for loop would be this:

while( my ($field) = $sth->fetchrow_array) {
        print OUTF $field;
}

But that's style and doesn't really answer your question

 

[LindaLiner]

Thank you, BrianAtWork.
I did output something from the loop. And I notice if I use while(my ($field) = $sth->fetch) {
print "TESTING\n";
print OUTF $field;
}
I can see forever loop of printing TESTING. But when I use
while(my ($field) = $sth->fetchrow_array) {
print "TESTING\n";
print OUTF $field;
}
I don't see anything print out.
I am studying the difference between this two function.

Actually, my problem could be the binary data is too large to be handled by printing to character string. Since I saw this error message:
error_handler: Data-conversion resulted in overflow.

But I don't know how to solve it by using Perl.

 

[BrianAtWork]

Your second one should be printing to STDOUT instead of OUTF if you want to see it on the screen. Are you seeing multiple "TESTING" lines appear on your screen? If so, something seems a little off - Your query is "select Audio_Data from English_English where Audio_Name = 'ENG_2.WAV'" - I would expect only 1 row to come out of this query. Of course, the table could have multiple rows with audio_name = eng_2.wav, but since you are only creating 1 output file, I would expect your table to only have 1 row for eng_2.wav. If that is true about your table, you should see the "TESTING" message only appear once.

You could also try this:

print STDOUT length($field);

inside your loop to see if the field has a length.

Just my thoughts

 

[LindaLiner]

There is no duplication in database. Since I got 1 as output from the database when I run below code.

CODE
$query = "select count(*) from English_English where Audio_Name = 'ENG_2.WAV'";
$sth = $dbh->prepare($query) or die "prepare failed\n";
$sth->execute() or die "unable to execute query $query error $DI::errstr";

while(@temp_count = $sth->fetchrow_array) {
foreach $field(@temp_count) {
print "we found $field audio file(s) from table.\n";
}
}
$sth->finish() or die "unable to finish query -- error $DI::errstr";

 

[LindaLiner]

The first fecth will give me multi-line outputs of TESTING.
When I use length($field), got 0s all over of screen.

But the second one (fetchrow_array)I got error message of
error_handler: Data-conversion resulted in overflow.
and nothing was displayed besides that.
ALso, when I use your length($field), nothing shows, either.

 

[BrianAtWork]

Not sure about the data-conversion error. I would try running your query outside of Perl and see if you get the error or not.

 

[LindaLiner]

I run the same query from SQL Database Management Studio, and I can get the binary data out as 0x524946469C3F0300.......

So I think the query and data are good. It must be this conversion error.

 

[BrianAtWork]

Which Database server are you using? I'm guessing either SQL Server or SyBase.

I was reading the DBI documentation (

http://search.cpan.org/~timb/DBI-1.605/DBI.pm#Handling_BLOB_/_LONG_/_Memo_Fields).

Apparently you can set $dbh->{LongReadLen} or $dbh->{LongTruncOk} (to truncate long data).

You could also run this after your execute, before your while loop:

print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
print "Field names: @{ $sth->{NAME} }\n";

and see what that returns.

Once I know what database you are using we can look at the DBD page on CPAN for that driver and see if there are special cases for long data. For example, the DB2 driver lets you pull long data in chunks:

http://search.cpan.org/~ibmtordb2/DBD-DB2-1.71/DB2.pod#Fetching_LOB_Data_in_Pieces