Thread: postgresql, odbc and escape characters?

Hello everyone...


... currently I am dealing with migrating the (unfortunately highly
proprietary) document management software we are running here from
MS Windows NT Server 4.0 to some more reliable environment. This
software relies on a file system to actually store the documents it
handles as well as on a database to keep them sorted. Right now, I
am trying to get postgresql set up as the database backend for this
software using the win32/odbc driver, but certain parts of the dms
application reproducibly fail with errors like:


DbException in SQL statement:
  SELECT A_.id, A_.name, A_.description, A_.remark
    FROM OrgRight A_
   WHERE ((A_.description LIKE '%^%' escape '\'))
  ORDER BY A_.description
Errornumber: 7
Errorstate: DbError
DbException in database
172-DSN=bf381;DATABASE=bf381;SERVER=192.168.1.170;PORT=5432;A6=;A7=
100;A8=4096;B0=254;B1=8190;BI=0;C2=dd_;;CX=1b1055dab[Thu Aug 26
14:06:15 2004] DbException: FEHLER:  Fehler »unterminated quoted
string« bei »'\'))  ORDER BY A_.description« bei Zeichen 158 [S1000]


For what I understood (and what sounds logical to me, looking at
those things are handled in unix environments), the \' in "escape
'\'" is parsed as an escaped ' char, so in the end the query fails
because of a missing string termination.

Question: Is there any way to modify the behavior of postgresql
server itself or of the odbc driver to deal with things like that? I
am sorry if this all sounds a little vague, but I am still trying to
figure out where to tweak, here, so hope anyone can give me a slight
clue...


TIA and bye
Kris


--
Kristian Rink     -- Programmierung/Systembetreuung
planConnect GmbH * Strehlener Str. 12 - 14 * 01069 Dresden
0176 24472771 * krink@pm-planc.de

Kristian Rink wrote:
> For what I understood (and what sounds logical to me, looking at
> those things are handled in unix environments), the \' in "escape
> '\'" is parsed as an escaped ' char, so in the end the query fails
> because of a missing string termination.
>
> Question: Is there any way to modify the behavior of postgresql
> server itself or of the odbc driver to deal with things like that?

No.  Just pick a different escape character or double the backslash.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Fri, 27 Aug 2004 09:44:59 +0200
Peter Eisentraut <peter_e@gmx.net> wrote:

> > Question: Is there any way to modify the behavior of postgresql
> > server itself or of the odbc driver to deal with things like
> > that?
>
> No.  Just pick a different escape character or double the
> backslash.

Guess this is something to be configured within the scope of the
application that tries to run the query through odbc, right? If so,
I am already doomed about that since this part of the dms
application appears to be closed-source. :(

Anyhow, thanks!
Cheers,
Kris



--
Kristian Rink     -- Programmierung/Systembetreuung
planConnect GmbH * Strehlener Str. 12 - 14 * 01069 Dresden
0176 24472771 * krink@pm-planc.de