Visio 2002 - Problem Reverse Engineering PostgreSQL DB using psqlODBC

[Gary Faulkner]

First the vitals....
Visio 2002 SR-1 (v10.0.2514)
PostgreSQL Database v7.3.4
psqlODBC v07.03.0100
Windows XP Professional SP1

When I attempt to reverse engineer a PostgreSQL database,
and specify that I want "Foreign key" objects to be
imported, I received the following error: "Data source
client error: State:S1000,Native:7,Origin: ERROR: parser:
parse error at or near "pg_catalog" at character 402".

I then enabled logging in the psqlODBC driver setup, and
was able to determine the cause of the problem - a
missing comma in a generated SQL statement. The actual
SQL statement recovered from the log file follows. The
missing comma is at character position 399, or after the
phrase "pg_catalog.pg_namespace pn"...

SELECT pt.tgargs, pt.tgnargs,
pt.tgdeferrable,
pt.tginitdeferred, pp1.proname,
pp2.proname, pc.oid,
pc1.oid, pc1.relname,
pn.nspname FROM pg_catalog.pg_class pc,
pg_catalog.pg_proc pp1,
pg_catalog.pg_proc pp2,
pg_catalog.pg_trigger pt1,
pg_catalog.pg_trigger pt2,
pg_catalog.pg_proc pp,
pg_catalog.pg_trigger pt,
pg_catalog.pg_class pc1,
pg_catalog.pg_namespace pn
pg_catalog.pg_namespace pn1 WHERE
pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND
pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND
pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid = pc.oid AND
((pc.relname='enroller_reference') AND (pn1.oid =
pc.relnamespace) AND (pn1.nspname = 'public') AND
(pp.proname LIKE '%ins') AND (pp1.proname LIKE '%upd')
AND (pp2.proname LIKE '%del') AND
(pt1.tgrelid=pt.tgconstrrelid) AND
(pt1.tgconstrname=pt.tgconstrname) AND
(pt2.tgrelid=pt.tgconstrrelid) AND
(pt2.tgconstrname=pt.tgconstrname) AND
(pt.tgconstrrelid=pc1.oid) AND (pc1.relnamespace=pn.oid))

Upon inserting the comma, the SQL statement executes
fine. The questions I have are:
1) Is this a psqlODBC bug?
2) Is this a Visio bug?
3) Is there a known fix for this issue?

Thanks in advance for any information pertaining to this
issue.

Gary Faulkner
Enterprise Information Solutions
(e-mail address removed)

 

[Patrick Tseng [MSFT]]

Hi Gary,

This is very interesting! Are you using the "Generic ODBC driver" provided by the Visio Database modeling solution. As far as I know, there's no native driver to work
with PostgreSQL. You're probably using the "Generic ODBC driver" with the backend. The "Generic ODBC driver" basically calls SQLForeignKeys (standard ODBC
API) to extract FKs. Therefore, I would think it's a bug in the PostgreSQL ODBC driver.

--

Patrick Tseng [MSFT]
Visual Studio Enterprise Frameworks and Tools

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


--------------------
Content-Class: urn:content-classes:message
From: "Gary Faulkner" <[email protected]>
Sender: "Gary Faulkner" <[email protected]>
Subject: Visio 2002 - Problem Reverse Engineering PostgreSQL DB using psqlODBC
Date: Thu, 28 Aug 2003 17:08:31 -0700
Lines: 60
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcNtwa31D6fg1bfNRdCsuK3MRw0N/A==
Newsgroups: microsoft.public.visio.general
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.visio.general:2353
NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
X-Tomcat-NG: microsoft.public.visio.general

First the vitals....
Visio 2002 SR-1 (v10.0.2514)
PostgreSQL Database v7.3.4
psqlODBC v07.03.0100
Windows XP Professional SP1

When I attempt to reverse engineer a PostgreSQL database,
and specify that I want "Foreign key" objects to be
imported, I received the following error: "Data source
client error: State:S1000,Native:7,Origin: ERROR: parser:
parse error at or near "pg_catalog" at character 402".

I then enabled logging in the psqlODBC driver setup, and
was able to determine the cause of the problem - a
missing comma in a generated SQL statement. The actual
SQL statement recovered from the log file follows. The
missing comma is at character position 399, or after the
phrase "pg_catalog.pg_namespace pn"...

SELECT pt.tgargs, pt.tgnargs,
pt.tgdeferrable,
pt.tginitdeferred, pp1.proname,
pp2.proname, pc.oid,
pc1.oid, pc1.relname,
pn.nspname FROM pg_catalog.pg_class pc,
pg_catalog.pg_proc pp1,
pg_catalog.pg_proc pp2,
pg_catalog.pg_trigger pt1,
pg_catalog.pg_trigger pt2,
pg_catalog.pg_proc pp,
pg_catalog.pg_trigger pt,
pg_catalog.pg_class pc1,
pg_catalog.pg_namespace pn
pg_catalog.pg_namespace pn1 WHERE
pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND
pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND
pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid = pc.oid AND
((pc.relname='enroller_reference') AND (pn1.oid =
pc.relnamespace) AND (pn1.nspname = 'public') AND
(pp.proname LIKE '%ins') AND (pp1.proname LIKE '%upd')
AND (pp2.proname LIKE '%del') AND
(pt1.tgrelid=pt.tgconstrrelid) AND
(pt1.tgconstrname=pt.tgconstrname) AND
(pt2.tgrelid=pt.tgconstrrelid) AND
(pt2.tgconstrname=pt.tgconstrname) AND
(pt.tgconstrrelid=pc1.oid) AND (pc1.relnamespace=pn.oid))

Upon inserting the comma, the SQL statement executes
fine. The questions I have are:
1) Is this a psqlODBC bug?
2) Is this a Visio bug?
3) Is there a known fix for this issue?

Thanks in advance for any information pertaining to this
issue.

Gary Faulkner
Enterprise Information Solutions
(e-mail address removed)