Can someone explain the current state of BLOB fields and Postgres ODBC?

This is a multipart message in MIME format.
--=_alternative 005D889A85257745_=
Content-Type: text/plain; charset="US-ASCII"

We have an ODBC application that among other things needs to be able to
read and write binary files to the database. Originally I tried using oid
fields, but now it looks like bytea with the "bytea as lo" parameter is
the recommended way. From my searching on Google, there seems to be a lot
of information (mostly old) that talks about oid, bytea, and lo, and I'm
now pretty confused. Can anyone clear this up for me?

When working with BLOBs we always use bind parameters and if the binary
data is over 2500 bytes, we read or write the data in chunks. This all
seems to work ok, but when writing files to the database, its returning
the warning message "WARNING: nonstandard use of escape in a string
literal". When I pull the files out, they still seem to look ok, but I'm
worried that something is trying to interpret the data instead of just
passing it in directly. Is it safe to ignore this warning? Or is it
possible that data corruption could occur?

Jon
--=_alternative 005D889A85257745_=
Content-Type: text/html; charset="US-ASCII"

We have an ODBC application that among
other things needs to be able to read and write binary files to the database.
 Originally I tried using oid fields, but now it looks like bytea
with the "bytea as lo" parameter is the recommended way.  From
my searching on Google, there seems to be a lot of information (mostly
old) that talks about oid, bytea, and lo, and I'm now pretty confused.
 Can anyone clear this up for me?



When working with BLOBs we always use
bind parameters and if the binary data is over 2500 bytes, we read or write
the data in chunks.  This all seems to work ok, but when writing files
to the database, its returning the warning message "WARNING: nonstandard
use of escape in a string literal".  When I pull the files out,
they still seem to look ok, but I'm worried that something is trying to
interpret the data instead of just passing it in directly.  Is it
safe to ignore this warning?  Or is it possible that data corruption
could occur?



Jon
--=_alternative 005D889A85257745_=--

Re: Can someone explain the current state of BLOB fields andPostgres ODBC?

This is a multipart message in MIME format.
--=_alternative 006730C185257746_=
Content-Type: text/plain; charset="US-ASCII"

I'm going to assume that the lack of response is due to people being away
on holiday enjoying the summer weather or just don't know the answer.. I
believe I found the proper solution for this and wanted to share it. Feel
free to disagree and set me straight :)

It seems this whole escape sequence stuff is nearly legacy support for the
old Postgres (and even Ingres?) support for specifying control codes
inline in a sql statement. This support appears to be slowly removed in
favor of being more ANSI compliant. To that end, the solution is to
enable the database parameter standard_conforming_strings (set it to yes).
This will disable support for the escape sequences, allowing ODBC apps to
behave as you would expect.

Can anyone confirm this? Also, are there other parameters that I should
look at to make my ODBC experience more "standard"? We do have the
ability to add workarounds for specific drivers, but I feel that "less is
more".

Thanks and enjoy your weekends..

Jon




From:
raiford@labware.com
To:
pgsql-odbc@postgresql.org
Date:
06/17/2010 01:17 PM
Subject:
[ODBC] Can someone explain the current state of BLOB fields and Postgres
ODBC?
Sent by:
pgsql-odbc-owner@postgresql.org



We have an ODBC application that among other things needs to be able to
read and write binary files to the database. Originally I tried using oid
fields, but now it looks like bytea with the "bytea as lo" parameter is
the recommended way. From my searching on Google, there seems to be a lot
of information (mostly old) that talks about oid, bytea, and lo, and I'm
now pretty confused. Can anyone clear this up for me?

When working with BLOBs we always use bind parameters and if the binary
data is over 2500 bytes, we read or write the data in chunks. This all
seems to work ok, but when writing files to the database, its returning
the warning message "WARNING: nonstandard use of escape in a string
literal". When I pull the files out, they still seem to look ok, but I'm
worried that something is trying to interpret the data instead of just
passing it in directly. Is it safe to ignore this warning? Or is it
possible that data corruption could occur?

Jon

--=_alternative 006730C185257746_=
Content-Type: text/html; charset="US-ASCII"

I'm going to assume that the lack of response
is due to people being away on holiday enjoying the summer weather or just
don't know the answer..  I believe I found the proper solution for
this and wanted to share it.  Feel free to disagree and set me straight
:)



It seems this whole escape sequence
stuff is nearly legacy support for the old Postgres (and even Ingres?)
support for specifying control codes inline in a sql statement.  This
support appears to be slowly removed in favor of being more ANSI compliant.
 To that end, the solution is to enable the database parameter standard_conforming_strings
(set it to yes).  This will disable support for the escape sequences,
allowing ODBC apps to behave as you would expect.



Can anyone confirm this?  Also,
are there other parameters that I should look at to make my ODBC experience
more "standard"?  We do have the ability to add workarounds
for specific drivers, but I feel that "less is more".



Thanks and enjoy your weekends..



Jon