Thread: 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
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
>
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
I am not sure if this a bug report or a feature request. When I compiles postgres I changed the NAMEDATALEN #DEFINE to 64 ( a more reasonable length for object names IMHO). So far it has not caused any problems with postgres but it is causing problems with the ODBC driver. When I attempt to attach any table who has a long object name or an associated index with a long name it causes an error message and does not connect. It seems like it's returning a null or an empty sting or something. There was talk a while ago about Postgres changing NAMEDATALEN a while back so this might become more important if the official disto gets changed. :wq Tim Uckun Due Diligence Inc. http://www.diligence.com/ Americas Background Investigation Expert. If your company isn't doing background checks, maybe you haven't considered the risks of a bad hire.
On Wed, 12 Jul 2000, Tom Lane wrote:
> 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.
I see says the blind man... makes sense..
> 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 saw this need coming but wanted to avoid it. I think what I'll do is
place a tag somewhere USEODBC=? and use this to decide which SQL to use,
ODBC or Jet. Our unfortunate situation is that the guy developing the
Access end is unable to run PG anywhere so his datasource has to be a
.mdb file (any suggestions?).
> 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
What's the time table? Anyone expecting to work on this any time
soon? I'd help but I don't know C very well and I have no development
tools in Win95. :( -or is that :)
..
Matthew <matt@ctlno.com>:
> It's not easy to find, but there is an ODBC install
> that you can download from the MS website.
Thanks.. That's what I wanted to know.. I've been able to find things
before in MS's archives even when I didn't know what I was looking for.. I
just wanted to know if what I'm looking for exists before looking :)
Thanks for the quick reply
-Cedar
(My mailer was fouled up; I had sent this a few days ago...) > > What's the time table? Anyone expecting to work on this any time > > soon? I'd help but I don't know C very well and I have no development > > tools in Win95. :( -or is that :) > > Hmm. You could help by arranging testing once it is fixed. Are you > subscribed to the interfaces list? > > - Thomas
Dear Sir / madam
I wish to have my name removed from the mailing list.
Regards
Geoff Healey
----- Original Message -----
From: Cedar Cox <cedarc@visionforisrael.com>
To: <pgsql-interfaces@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, July 13, 2000 5:06 PM
Subject: Re: [INTERFACES] more ODBC driver
>
> On Wed, 12 Jul 2000, Tom Lane wrote:
> > 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.
>
> I see says the blind man... makes sense..
>
> > 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 saw this need coming but wanted to avoid it. I think what I'll do is
> place a tag somewhere USEODBC=? and use this to decide which SQL to use,
> ODBC or Jet. Our unfortunate situation is that the guy developing the
> Access end is unable to run PG anywhere so his datasource has to be a
> .mdb file (any suggestions?).
>
> > 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
>
> What's the time table? Anyone expecting to work on this any time
> soon? I'd help but I don't know C very well and I have no development
> tools in Win95. :( -or is that :)
>
> ..
> Matthew <matt@ctlno.com>:
> > It's not easy to find, but there is an ODBC install
> > that you can download from the MS website.
>
> Thanks.. That's what I wanted to know.. I've been able to find things
> before in MS's archives even when I didn't know what I was looking for.. I
> just wanted to know if what I'm looking for exists before looking :)
>
>
> Thanks for the quick reply
> -Cedar
>
>
>
>
> > Hmm. You could help by arranging testing once it is fixed. Are you > > subscribed to the interfaces list? > > > > - Thomas Yes, I am, and I plan on staying on for at least a few months (or longer if the list if not too much of a burden). Maybe I'll be able to start answering a few questions then :) What do you mean by 'arranging testing'? Just testing on my part, or finding other people to test it as well? -Cedar
> What do you mean by 'arranging testing'? Just testing on my part, or
> finding other people to test it as well?
Testing on your end, since it seems that you are exercising features
that others have not tried (yet).
- Thomas