On Sat, Nov 23, 2002 at 09:53:43PM -0800, David Wheeler wrote:
> On Tuesday, October 29, 2002, at 02:50 AM, Tim Bunce wrote:
>
> > Beware that your semantics for SQL_BINARY must match those of ODBC.
> > I think ODBC requires SQL_BINARY values to be formated as pairs of
> > hex chars.
> >
> > Best to use bind_param($col, $val, { pg_type => NNN })
> > where NNN is the value of the PostgreSQL bytea type.
>
> Looking at this again, I find that the code already does this, after a
> fashion. If you use bind_param($col, $val, { pg_type => 17 }) (where 17
> represents the PostgreSQL BYTEA type), then it does the proper
> escaping. The thing is, if you use bind_param($col, $val, { pg_type => SQL_BINARY }),
Don't do that, it's wrong.
The value of the pg_type attribute is supposed to be the *PostgreSQL*
type value. To use the ANSI standard type values you'd use the
TYPE attribute, ie { TYPE => SQL_BINARY }.
> instead, then DBD::Pg uses its internal sql_pg_type()
> function to convert SQL_BINARY to 17 -- even though, according to what
> you say above, Tim, they're not strictly speaking the same thing.
Using TYPE implies certain semantics about the value being bound that are
defined by ANSI/Microsoft. Using SQL_BINARY means the value is expresses
(by the application to the driver) as a string of pairs of hex chars
(I think, I've not checked my ODBC books).
> We can do two things about this. We can either remove this translation
> bit, so that SQL_BINARY doesn't become BYTEA. Then anyone who is
> binding SQL_BINARY will have to change it. Or we can document that it's
> *going* to change in the next version -- and maybe throw a warning --
> and put it off till the next version.
The TYPE should mainly be used to describe the value being passed from
the application to the driver. If a driver supports two types of
incompatible strings and the app uses SQL_BINARY, for example, then
the driver needs to default to using one of those string types when
it passes the value on to the database. If that default may not suit all
cases then the driver needs to provide a way to let the app specify
which type it should use. For example:
bind_param($col, unpack("H*",$val), { TYPE => SQL_BINARY, pg_type => 17 })
But in practice that would rarely be used because perl is happy
dealing with binary data so there's little need for binding a value
as a hex string. The app could just do:
bind_param($col, $val, { pg_type => 17 })
A situation that would require specifying both TYPE and pg_type
might be where an app was ported from another database, like Oracle,
and it was making heavy use of SQL_BINARY already. In this case
just adding pg_type would cleanly resolve the ambiguitiy of which
pg string type to use.
But, having said all that, supporting SQL_BINARY probably shouldn't
be high on your list of priorities. Very few apps use it.
I hope all this is clear enough.
Tim.
