Re: Bytea and perl

-----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.

> 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 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.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200603232139
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFEI15nvJuQZxSWSsgRAnDGAJ9CW2gb0qE53isrOfLjoALuQYetKQCgwCLQ
A1EKVpnIhjPHiqT0HTAfwjY=
=LmM9
-----END PGP SIGNATURE-----