Re: Bytea and perl

On 3/22/06 10:08 PM, "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>> What is the accepted way of inserting a binary string into
>> a bytea column in perl?
>
> It's pretty much as you described. Here's an example, modified
> from the test suite:
>
> use DBD::Pg qw(:pg_types);
> ...
> my $sth = $dbh->prepare(qq{INSERT INTO dbd_pg_test (id,bytetest) VALUES
> (?,?)});
>
> $sth->bind_param(2, undef, { pg_type => DBD::Pg::PG_BYTEA });

Greg,

Thanks for the answer.

OK. Here is my follow-up question. Why is this explicit parameter binding
necessary? When would I want to have pg_type be something other than
PG_BYTEA when inserting into a bytea column?

The reason this is important is that many (read this as ALL, as far as I
know) modules built on top of DBI do not use explicit paramater binding and
rely on the sth->execute(...) quoting to do the right thing, which it does
with all column types except bytea, it seems. This renders these
higher-level interfaces not so useful for bytea storage unless one base64
encodes (which increases storage needs by 25% and does add some nontrivial
overhead for very large files). I haven't looked at the DBD::Pg code on
this issue, so I don't know any intricacies, so I'm more curious than
anything else.

> $sth->execute(400, 'aa\\bb\\cc\\\0dd\\');
>
> Other options include storing just a filename, or base-64 encoding
> everything and storing it as a text. I tend to prefer the latter
> more often than not, as the encode/decode goes very quickly on
> most modern computers.

I guess a third option is the large object interface, which I am trying to
avoid.

Thanks again,
Sean