Обсуждение: DBD::Pg, bytea type
Hiyas, Currently, there's a problem using bytea type transparently with DBD::Pg driver. Postgres requires you to quote things in a special way to handle bytea data, for example, to insert a null character, you have to use '\000'. Select, on other hand, will require you to uncode such data before you can actually use it in perl. I'm making patches for DBD::Pg which will properly quote bytea data before insertion and unquote it on select. Let me know if its not a good idea. Another thing: Currently, Pg quote() method only will properly escape backslash if the datatype is not specified. If the datatype is specified, it'll give wrong result: $dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should result in 'foo\\bar'. Anyone minds if I fix this one as well? -alex
On Thu, Mar 08, 2001 at 02:48:16PM -0500, Alex Pilosov wrote: > Hiyas, > > Currently, there's a problem using bytea type transparently with DBD::Pg > driver. Postgres requires you to quote things in a special way to handle > bytea data, for example, to insert a null character, you have to use > '\000'. Select, on other hand, will require you to uncode such data before > you can actually use it in perl. > > I'm making patches for DBD::Pg which will properly quote bytea data > before insertion and unquote it on select. Let me know if its not a good > idea. Surely other 8-bit clean interfaces besides DBD::Pg run into this problem also? Any insights? I'd think that it should be transparant for both the quote() method and placeholders. Placeholders also *segfault* the client (not something we're used to in Perl-land) if you try to use more than 64k of data (not sure if that's per-placeholder, statement, row or something else). > Another thing: Currently, Pg quote() method only will properly escape > backslash if the datatype is not specified. If the datatype is specified, > it'll give wrong result: > $dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should > result in 'foo\\bar'. Anyone minds if I fix this one as well? I've heard a variety of different problem reports about quote(). Here's a patch one of my cow orkers sent me: http://www.postgresql.org/mhonarc/pgsql-interfaces/2001-02/msg00199.html -- meow _ivan
As described in the below email, here's the patch. Few notes: It will always properly unquote things of bytea type when retrieved from select. If someone has code that relies on the old behaviour, you'll need to change that. It will only properly enquote things when you do $sth->bind_param(1, $xxx, DBI::SQL_BINARY); (ie you must specify binding type to bind_param). On Thu, 8 Mar 2001, Alex Pilosov wrote: > Hiyas, > > Currently, there's a problem using bytea type transparently with DBD::Pg > driver. Postgres requires you to quote things in a special way to handle > bytea data, for example, to insert a null character, you have to use > '\000'. Select, on other hand, will require you to uncode such data before > you can actually use it in perl. > > I'm making patches for DBD::Pg which will properly quote bytea data > before insertion and unquote it on select. Let me know if its not a good > idea. > > Another thing: Currently, Pg quote() method only will properly escape > backslash if the datatype is not specified. If the datatype is specified, > it'll give wrong result: > $dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should > result in 'foo\\bar'. Anyone minds if I fix this one as well? > > -alex > > > > > -- -- Alex Pilosov | http://www.acecape.com/dsl CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area 325 W 38 St. Suite 1005 | (Stealth Marketing Works! :) New York, NY 10018 |
As described in the below email, here's the patch. Few notes: It will always properly unquote things of bytea type when retrieved from select. If someone has code that relies on the old behaviour, you'll need to change that. It will only properly enquote things when you do $sth->bind_param(1, $xxx, DBI::SQL_BINARY); (ie you must specify binding type to bind_param). On Thu, 8 Mar 2001, Alex Pilosov wrote: > Hiyas, > > Currently, there's a problem using bytea type transparently with DBD::Pg > driver. Postgres requires you to quote things in a special way to handle > bytea data, for example, to insert a null character, you have to use > '\000'. Select, on other hand, will require you to uncode such data before > you can actually use it in perl. > > I'm making patches for DBD::Pg which will properly quote bytea data > before insertion and unquote it on select. Let me know if its not a good > idea. > > Another thing: Currently, Pg quote() method only will properly escape > backslash if the datatype is not specified. If the datatype is specified, > it'll give wrong result: > $dbh->quote('foo\\bar', SQL_CHAR) will result in 'foo\bar', but it should > result in 'foo\\bar'. Anyone minds if I fix this one as well? > > -alex > > > > > -- -- Alex Pilosov | http://www.acecape.com/dsl CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area 325 W 38 St. Suite 1005 | (Stealth Marketing Works! :) New York, NY 10018 |