Re: textcat() and ODBC driver - Mailing list pgsql-interfaces
From | Cedar Cox |
---|---|
Subject | Re: textcat() and ODBC driver |
Date | |
Msg-id | Pine.LNX.4.21.0012280127080.23025-100000@nanu.visionforisrael.com Whole thread Raw |
In response to | Re: textcat() and ODBC driver (Thomas Lockhart <lockhart@alumni.caltech.edu>) |
List | pgsql-interfaces |
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
pgsql-interfaces by date: