Re: more ODBC driver

Cedar Cox <cedarc(at)visionforisrael(dot)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