Thread: Parsing of backslash in statements via ODBC
We are trying to use a suite of third party applications, which run on Windows 2000, that can make use of a database backend via ODBC. Most of the apps work just great with Postgres. One of the components (a key visualization application) does not. Note that this all works fine, as is, using Oracle or MS-SQL as the backend database. The vendor was kind enough to provide to us the SQL statements that application makes (there aren't many) and we were able to identify the problem by manually tinkering with the data. A column, called tagname, is a VARCHAR(40) and typical values are: Foo\Bar Baz\Blah\You\Get\The\Drift These values are inserted into the database just fine from other components on this suite (also via ODBC from a Windows client). When we get rid of any backslashes in the values, everything works just fine. Unfortunately, this is not an option. The statements are: 1) SELECT tn,ti,tt,tdt FROM t WHERE tn = 'mytag01' 2) SELECT dat,mt,ti,val,s,m FROM fl WHERE (ti = @1) AND dat BETWEEN @3 AND @5 ORDER BY dat DESC, mt DESC Where @1 is the result of statement (1). Since MS-SQL & Oracle (both via ODBC) work fine with this app, I would suspect the issue is that Postgres is interpreting backslashes as escape characters in situations that the supported databases do not. Has anyone run across something like this, and if so, can anything be done given that we don't have the source code of the application in question? Alex
"aboster" <aboster@at-sd.com> writes:
> Since MS-SQL & Oracle (both via ODBC) work fine with this app, I would
> suspect the issue is that Postgres is interpreting backslashes as escape
> characters in situations that the supported databases do not.
Postgres definitely considers backslashes to be escape characters in
string literals.
> Has anyone run across something like this, and if so, can anything be done
> given that we don't have the source code of the application in question?
Without the app source code you may be kinda stuck :-(. It's possible
that you could hack something at the ODBC level, though. Try asking on
the pgsql-odbc list.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Given that this issue is a violation of SQL compatibility, shouldn't there really be an option to turn off interpretation of backslash characters in string literals as escapes? Maybe as a session variable of some kind, with a default being set in postgresql.conf? On Jan 14, 2005, at 4:43 PM, Tom Lane wrote: > "aboster" <aboster@at-sd.com> writes: >> Since MS-SQL & Oracle (both via ODBC) work fine with this app, I would >> suspect the issue is that Postgres is interpreting backslashes as >> escape >> characters in situations that the supported databases do not. > > Postgres definitely considers backslashes to be escape characters in > string literals. > >> Has anyone run across something like this, and if so, can anything be >> done >> given that we don't have the source code of the application in >> question? > > Without the app source code you may be kinda stuck :-(. It's possible > that you could hack something at the ODBC level, though. Try asking on > the pgsql-odbc list. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > - ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) iD8DBQFB6EDd7aqtWrR9cZoRAguTAJ9sij6xZ2Xmd2XfcEzeE1Wu4tVfuwCfcj6B MIPrLSXexDnl36k7ubDIEUg= =7H3R -----END PGP SIGNATURE----- ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
On Fri, Jan 14, 2005 at 04:59:57PM -0500, Frank D. Engel, Jr. wrote: > Given that this issue is a violation of SQL compatibility, shouldn't > there really be an option to turn off interpretation of backslash > characters in string literals as escapes? Maybe as a session variable > of some kind, with a default being set in postgresql.conf? I'm not totally sure about how ODBC works, but if it's anything like Perl DBI, surely it's the responsibility of the ODBC layer to escape the baackslashes? Maybe it depends on whether they're using placeholders or not... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
"Frank D. Engel, Jr." <fde101@fjrhome.net> writes:
> Given that this issue is a violation of SQL compatibility, shouldn't
> there really be an option to turn off interpretation of backslash
> characters in string literals as escapes? Maybe as a session variable
> of some kind, with a default being set in postgresql.conf?
That has about as much chance of getting in as a session variable to
change the identifier-case-folding behavior, and for the same reason:
any such variable is certain to break tons of existing client-side code
that doesn't know about it. We already learned this lesson with respect
to autocommit :-(.
It is irritating that we can't easily support exactly-spec-compliant
applications, and I'd like to find a solution. But a GUC variable
ain't it. See past discussions about case-folding behavior for some
of the ramifications.
regards, tom lane
Martijn van Oosterhout <kleptog@svana.org> writes:
> I'm not totally sure about how ODBC works, but if it's anything like
> Perl DBI, surely it's the responsibility of the ODBC layer to escape
> the baackslashes? Maybe it depends on whether they're using
> placeholders or not...
I suppose they are not using placeholders, or we'd not be having this
discussion ...
regards, tom lane