From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cedar Cox <cedarc@visionforisrael.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: more ODBC driver
Date: Wed, 12 Jul 2000 14:16:07 -0400
Message-ID: <24282.963425767@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.21.0007111052420.8323-100000@nanu.visionforisrael.com>
References: <Pine.LNX.4.21.0007111052420.8323-100000@nanu.visionforisrael.com>
Cedar Cox <cedarc@visionforisrael.com> writes:
> Despite this, other things seem to work. Now here's my problem. When
> executing a query in Access, I get the error:
> Error while executing the query;
> ERROR: parser: parse error at or near "{" (#1)
> The SQL received by the back end is (get ready!):
> (((SELECT "T1"."TreeID" ,"T1"."Name" ,(textcat(({fn concat(({fn
> concat(({fn concat(({fn concat(({fn concat(({fn concat(({fn
> concat("T1"."Name" ,' ' )) ,"T2"."Name" )}) ,' '
> )}) ,"T2"."Description" )}) ,' ' )}) ,"T3"."Name" )}) ,' '
> )}) ,"T3"."Description" )}) ,
> The query text in Access is (equally as ugly):
> SELECT T1.TreeID as TreeID, T1.Name AS Name, T1.Name&' '&T2.Name&'
> '&T2.Description&' '&T3.Name&' '&T3.Description AS Description,
It looks like the & operators are being translated to the ODBC notation
{fn concat(a,b)}, which isn't valid SQL. The ODBC driver is supposed
to translate that to the Postgres equivalent, namely textcat(a,b).
Unfortunately our ODBC driver isn't bright enough to do that for nested
function calls, and as you can see in the SQL log only the outermost
instance is getting fixed properly.
Probably the best short-term workaround is to write the query using
the Postgres function to begin with:
SELECT ..., textcat(T1.Name,textcat(' ',textcat(T2.Name,textcat(' ', ...
I'm not sure if we have a TODO item to fix the ODBC driver's function
translation code, but we should...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, cedarc@visionforisrael.com
Subject: Re: more ODBC driver
In-Reply-To: <24282.963425767@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox