Prev: FAQ 9.8 How do I fetch an HTML file?
Next: Writing to the end of a record
From: Tomasz Chmielewski on 11 Mar 2010 11:06
I have a database which has a lot of files saved as blobs (some "fancy" CMS system).

I would like to save them as files.

I saved them as files using such a query (for each blob):

my $sql = $db->prepare("SELECT blob_data FROM tx_drblob_content WHERE uid = (?)");
$sql->execute($uid); # $uid is ID of the blob in the database
my $blob = $sql->fetchrow_array;
open BLOBFILE, ">$datadir/$uid" or die "Cannot open $!";
print BLOBFILE $blob;
close BLOBFILE;

Unfortunately, the files (PDF, ZIP etc.) are corrupted.

I "uploaded" a text file to the database using system's web interface, then fetched it with the above perl code.

Here are some example differences (- denotes original file; + denotes the file fetched with perl):

-# From ``Assigned Numbers'':
+# From ``Assigned Numbers\'\':


So we can see that the file has \ appended in front of each '.

Which could be because the CMS system stores the files as such, or perhaps I should fetch/save the files differently?

Does anyone have some obvious thoughts on why I see \ appended before certain characters?



--
Tomasz Chmielewski
http://wpkg.org
From: Ben Morrow on 11 Mar 2010 12:42

Quoth Tomasz Chmielewski <tch(a)nospam.wpkg.org>:
> I have a database which has a lot of files saved as blobs (some "fancy"
> CMS system).
>
> I would like to save them as files.
>
> I saved them as files using such a query (for each blob):
>
> my $sql = $db->prepare("SELECT blob_data FROM tx_drblob_content
> WHERE uid = (?)");
> $sql->execute($uid); # $uid is ID of the blob in the database
> my $blob = $sql->fetchrow_array;
> open BLOBFILE, ">$datadir/$uid" or die "Cannot open $!";

Use 3-arg open. Use lexical filehandles. Since you're printing binary
data, you should use 'binmode' or the :raw layer.

open my $BLOBFILE, ">:raw", "$datadir/$uid" or die "...";

> print BLOBFILE $blob;
> close BLOBFILE;

If this is important work you should check the return value of close.
(It's not useful to check the return value of print: checking close is
both necessary and sufficient.)

> Unfortunately, the files (PDF, ZIP etc.) are corrupted.
>
> I "uploaded" a text file to the database using system's web interface,
> then fetched it with the above perl code.
>
> Here are some example differences (- denotes original file; + denotes
> the file fetched with perl):
>
> -# From ``Assigned Numbers'':
> +# From ``Assigned Numbers\'\':
>
> So we can see that the file has \ appended in front of each '.
>
> Which could be because the CMS system stores the files as such, or
> perhaps I should fetch/save the files differently?

Which database are you using? Which DBD? What type is the 'blob_data'
field?

Can you get the field out using the DB's own command-line tool (psql, or
equivalent for other databases) to compare?

> Does anyone have some obvious thoughts on why I see \ appended before
> certain characters?

Well, it looks to me as though the data has been SQL-quoted, since ' is
a special character in SQL but ` isn't (depending on the dialect, of
course). However, without knowing where the quoting is happening (in the
database, in the client library, in the DBD) it's hard to say.

Ben

 | 
Pages: 1
Prev: FAQ 9.8 How do I fetch an HTML file?
Next: Writing to the end of a record