RE: more ODBC driver - Mailing list pgsql-interfaces

It looks to me that the problem is that the ODBC driver is not correctly
handling the SQL syntax Access is handing to it.  You might want to try
playing with the SQL from Access and see if you can get any better
results.

I don't think this is related to the ODBC version mismatch problem that
you are also having.  That problem is easily fixed with the by
installing ODBC.  It's not easy to find, but there is an ODBC install
that you can download from the MS website.

Hope that helps...

> -----Original Message-----
> From:    Cedar Cox [SMTP:cedarc@visionforisrael.com]
> Sent:    Wednesday, July 12, 2000 10:34 AM
> To:    pgsql-interfaces@postgresql.org
> Subject:    [INTERFACES] more ODBC driver
> 
> Ok, one more time.. This is the third.. First try was just after
> joining
> the list, second never seemed to go anywhere.  If my posts are getting
> through, someone post some sort of reply (please?).. or is my problem 
> _that_ strange? :)   -Cedar
> 
> ---------- Forwarded message ----------
> From: Cedar Cox <cedarc@nanu.visionforisrael.com>
> To: pgsql-interfaces@postgresql.org
> Date: Tue, 4 Jul 2000 09:25:58 +0300 (IDT)
> Subject: more ODBC driver
> 
> I'm new.
> I'm working on developing an inventory system with PG as the back end
> and
> (eek..) MS Access as the front end.  I'm a bit lost because most of
> the DB design was done by someone else and I just convinced him that 
> a client/server model was better.. so I'm left with the server
> side of things and he's fighting 'Evil Access'.  Things seem to work
> ok,
> but I have a couple strange things.  
> 
> First of all, PG 7.0.2, PsqlODBC 6.50, Win98.
> 
> Whenever windows 'starts' ODBC (enter control panel, open ODBC
> connection...), I get an error message:
>   The ODBC resource DLL (ODBCINT.DLL) is a different version than the
> ODBC
>   setup dll (ODBCCP32.DLL).  You need to reinstall ODB....
> 
> Do I need to?  How?  Is there a nifty Microsoft update for a
> completely 
> new version?  I think I'm due for reinstall of 98 soon, but I'm not up
> to
> it right now (kinda like changing the oil in my car..)
> 
> 
> 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;
> 
> 
> I think I understand the workings of the system (PG+ODBC+Access), but
> I
> only have a little experience in Access, and less with PG/ODBC.  Help!
> 
> Thanks,
> -Cedar
>