Re: Bytea and perl

On 3/23/06 9:50 PM, "Greg Sabino Mullane" <greg@turnstep.com> wrote:

> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> 
>> 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?
> 
> You wouldn't, but the trick is getting all the pieces to know that the column
> is bytea. DBD::Pg has no inherent way to find out for iteslf. Nor does libpq.
> The planner has an idea, but that information is not transmitted back to
> DBD:Pg.
> The difference then becomes that the low-level calls that DDB::Pg makes to
> PostgreSQL via PQexecParams and PQexecPrepared are different if any of the
> values
> are binary. If they are, we can't simply pass a string, but have to pass a
> separate array of string lengths, as we can't use \0 to indicae the end of the
> data anymore.

Ahhh.  Now things start to make sense.

>> 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.
> 
> Well, there are other column type cases where it will fail, but they are not
> as common as bytea. Unfortunately, there is no easy solution. Hopefully these
> high-level interface modules left some hooks and knobs to handle this sort
> of situation. If they don't, drop them a line, because they should. :)

I will.

>> I guess a third option is the large object interface, which I am trying to
>> avoid.
> 
> I suspect that this is even less supported by the other modules, so you might
> as well go with the binding at that point. Good luck: hopefully one of the
> four options will work out for you.

Oh, it really isn't that big a deal.  Right now, I am using base64 encoding,
which is fine. 

Thanks for elaborating.

Sean