Re: textcat() and ODBC driver

On Wed, 27 Dec 2000, Thomas Lockhart wrote:

> > The response I got was that the odbc code is not smart enough to nest
> > textcat functions. A recent discovery ;) brings me to this question, Why
> > is textcat being used? Why not just use the || operator? Unless there's
> > something I don't know it seems to be a direct replacement for the &
> > operator in Access/VB. Thoughts? Someone willing to change it?
>
> There *may* be some translation in the ODBC driver to get from an ODBC
> function call to textcat() (I haven't looked at it since last spring).
> But I'm pretty sure that there is no "operator mapping" in the driver,
> and that Access itself is converting from the (nonstandard) ampersand to
> some function call. Have you tried forming the query with the SQL92 "||"
> operator? Or is this some automatic query from Access which you cannot,
> uh, access?
>
> - Thomas

No, I'm sure it was something we typed. I tried the similar example query
using linked tables but access just did the & operation locally. I'm not
sure what I did before. From my post back in July:
--
On Sun, 2 Jul 2000, Cedar Cox wrote:
> Subject: [INTERFACES] more ODBC driver
>
> 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" )}) ,"T1"."Weight" ,"T1"."NumPerBox" ,"T1"."UnitID" ,"T1"."SurID" ,"T1"."ParentID" FROM
> "tblStResTree" "T1","tblStResTree" "T2","tblStResTree" "T3" WHERE
> ((("T1"."Description" IS NULL ) AND ("T1"."ParentID" =
> "T2"."TreeID" ) ) AND ("T2"."ParentID" = "T3"."TreeID" ) ) ) UNION (SELECT
> "T1"."TreeID" ,"T1"."Name" ,(textcat(({fn concat(({fn concat(({fn
> concat("T1"."Name" ,' ' )) ,"T2"."Name" )}) ,' '
> )}) ,"T2"."Description" )}) ,"T1"."Weight" ,"T1"."NumPerBox" ,"T1"."UnitID" ,"T1"."SurID" ,"T1"."ParentID" FROM
> "tblStResTree" "T1","tblStResTree" "T2" WHERE (("T1"."Description" IS NULL
> ) AND (("T1"."ParentID" = "T2"."TreeID" ) AND ("T2"."ParentID" = 0
> ) ) ) )) UNION (SELECT
> "TreeID" ,"Name" ,"Name" ,"Weight" ,"NumPerBox" ,"UnitID" ,"SurID" ,"ParentID" FROM
> "tblStResTree" "T1" WHERE (("Description" IS NULL ) AND ("ParentID" = 0
> ) ) )) UNION (SELECT
> "TreeID" ,"Name" ,"Description" ,"Weight" ,"NumPerBox" ,"UnitID" ,"SurID" ,"ParentID" FROM
> "tblStResTree" WHERE NOT(("Description" IS NULL ) ) )
>
> 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, T1.Weight,
> T1.NumPerBox, T1.UnitID, T1.SurID, T1.ParentID
> FROM tblStResTree AS T1, tblStResTree AS T2, tblStResTree AS T3
> WHERE T1.Description is null AND T1.ParentID=T2.TreeID AND
> T2.ParentID=T3.TreeID
> UNION
> SELECT T1.TreeID, T1.Name AS Name, T1.Name&' '&T2.Name&' '&T2.Description
> AS Description, T1.Weight, T1.NumPerBox, T1.UnitID, T1.SurID, T1.ParentID
> FROM tblStResTree AS T1, tblStResTree AS T2
> WHERE T1.Description is null AND T1.ParentID=T2.TreeID AND T2.ParentID=0
> UNION
> SELECT T1.TreeID, T1.Name AS Name, T1.Name AS Description, T1.Weight,
> T1.NumPerBox, T1.UnitID, T1.SurID, T1.ParentID
> FROM tblStResTree AS T1
> WHERE T1.Description is null AND T1.ParentID=0
> UNION SELECT TreeID, Name, Description, Weight, NumPerBox, UnitID, SurID,
> ParentID
> FROM tblStResTree
> WHERE Description is not null;
>
--
..With no attempt to clean up ;) We're now using passthrough queries so
this is not an issue any more, I just thought I'd throw it out there..
besides our problem query is now obsolete. I can't get Jet to leave it
alone and let the server do the text concats, apart from using a
passthrough query of course. Anyway, I'm sure it was a problem then and
other people have probably seen it as well, it would just be nice to get
it out of the way.. I love swatting at bugs, even if I can't kill them
myself ;)

-Cedar