[ psqlodbc-Bugs-1001906 ] Unable to obtain list of tables from the datasource

Bugs item #1001906, was opened at 2007-02-09 12:03
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1001906&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Dave Martin (dave)
Assigned to: Nobody (None)
Summary: Unable to obtain list of tables from the datasource

Initial Comment:
Attempting to use Visio 2007 to access postgres databases for link data to shape purposes results in the error "Unable to obtain list of tables from the datasource". I have tried both the ansi and unicode drivers.

Using the reverse-engineer database portion of visio works fine.

This might make it seem like a bug in Visio, however link data to tables works fine with other odbc drivers.

I have watched a packet trace between the postgres server and the PC in question, and verified that the list of tables is in fact returned from postgres to the PC, but apparently it never makes it from the odbc driver to visio or is corrupted somehow.

I have tried both 8.01 and 8.02 drivers.

Here is mylog:

[5888-0.001]globals.extra_systable_prefixes = 'dd_;'
[5888-0.003]exe name=VISIO plaformId=2
[5888-0.004][[SQLAllocHandle]][5888-0.005]**** in PGAPI_AllocEnv **
[5888-0.005]** exit PGAPI_AllocEnv: phenv = 05713B58 **
[5888-0.007][[SQLSetEnvAttr]] att=200,2
[5888-0.008][[SQLAllocHandle]][5888-0.008]PGAPI_AllocConnect: entering...
[5888-0.009]**** PGAPI_AllocConnect: henv = 05713B58, conn = 05713B88
[5888-0.011]EN_add_connection: self = 05713B58, conn = 05713B88
[5888-0.012] added at i =0, conn->henv = 05713B58, conns[i]->henv = 05713B58
[5888-0.014][SQLGetInfoW(30)][5888-0.015]PGAPI_GetInfo: entering...fInfoType=77
[5888-0.016]PGAPI_GetInfo: p='03.51', len=0, value=0, cbMax=12
[5888-0.017][SQLSetConnectAttrW][5888-0.017]PGAPI_SetConnectAttr for 05713B88: 115 00000000
[5888-0.018]the application is unicode
[5888-0.019][SQLDriverConnectW][5888-0.019]PGAPI_DriverConnect: entering...
[5888-0.020]**** PGAPI_DriverConnect: fDriverCompletion=3, connStrIn='DSN=opennms;'
[5888-0.029]our_connect_string = 'DSN=opennms;'
[5888-0.030]attribute = 'DSN', value = 'opennms'
[5888-0.031]copyAttributes: DSN='opennms',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[5888-0.033]getDSNinfo: DSN=opennms overwrite=0
[5888-0.035]rollback_on_error=-1
[5888-0.037]force_abbrev=0 bde=0 cvt_null_date=0
[5888-0.040]globals.extra_systable_prefixes = 'dd_;'
[5888-0.049]our_connect_string = 'DSN=opennms;'
[5888-0.050]attribute = 'DSN', value = 'opennms'
[5888-0.050]CopyCommonAttributes: A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;

AND psqlodbc:
conn=05713B88, PGAPI_DriverConnect( in)='DSN=opennms;', fDriverCompletion=3
DSN info: DSN='opennms',server='postgresql.somedomain.net',port='5432',dbase='opennms',user='xxiii',passwd='xxxxx'
onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''

----------------------------------------------------------------------

>Comment By: Dave Martin (dave)
Date: 2007-02-15 16:13

Message:
Thats fine if thats the case, but I want to be on pretty
sure footing when I push the issue on the Visio side.

----------------------------------------------------------------------

Comment By: Dmitriy Ivanov (divanov)
Date: 2007-02-15 07:42

Message:
Sounds like the ODBC driver is not to blame. It cannot foresee misbehavior of every wondrous application, e.g. Microsoft Visio.

----------------------------------------------------------------------

Comment By: Dave Martin (dave)
Date: 2007-02-13 15:34

Message:
The double-quoting is not intentional as far as a know, note
that the log was generated from running Microsoft Visio,
essentially a point'n'click application that completely
hides the fact that SQL is even involved. It just presents a
list of tables and asks me to click on the one I want to
use, upon which the error appears.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-02-13 14:29

Message:
I see the following query in your log.
SELECT * FROM "public"".""ipinterface"
It also fails in a psql session.
Is double-quoting the period(.) intentional ?

----------------------------------------------------------------------

Comment By: Dave Martin (dave)
Date: 2007-02-13 11:11

Message:
Sorry about the double post, browser refresh did it.

Attaching new mylog showing problem related to:

SELECT :ERROR: relation "public"."ipinterface" does not exist

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-02-12 17:24

Message:
There are 2 places where you can handle Mylog options.
One is global option and another is per datasource one.
Probably you turn on the global option but don't turn
on per datasource option. Please turn on per datasource
option and attach the Mylog output.

----------------------------------------------------------------------

Comment By: Dave Martin (dave)
Date: 2007-02-12 16:38

Message:
The exact error from visio for the password situation is:

Microsoft Office Visio cannot connect to this data source.
[more info]
Error 80004005: fe_sendauth: no password supplied

Also, the server is PostgreSQL 8.1.1.

----------------------------------------------------------------------

Comment By: Dave Martin (dave)
Date: 2007-02-12 12:42

Message:
The exact error from visio for the password situation is:

Microsoft Office Visio cannot connect to this data source.
[more info]
Error 80004005: fe_sendauth: no password supplied

Also, the server is PostgreSQL 8.1.1.

----------------------------------------------------------------------

Comment By: Dave Martin (dave)
Date: 2007-02-12 12:32

Message:
Ok, with the snapshot it gets the list of tables, however
two new problems:

First, upon trying to reuse an existing connection, it
simply informs me that the connection failed because a
password was not supplied (it never prompts for one, and
when I created the connection I did not allow it to save the
password in the connection file). So, I created a new
connection, and from there:

Second problem: It shows a list of tables and asks me to
click on the one I would like to use, which then results in
an error that it does not exist.

In the postgres log appears this message (ip address altered):

2007-02-12 12:16:15 MST 192.168.1.1(1653)/xxiii%opennms
SELECT :ERROR: relation "public"."ipinterface" does not exist

If I cut'n'paste that into the following in a psql session:

select * from "public"."ipinterface" limit 5;

It works fine. All I can figure is that when the query goes
through the odbc driver the quote marks are literally being
used as part of the table (and schema) name, and causing it
to fail (or perhaps some other problem, but thats my
assumption). I have attached the mylog output.

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-02-09 17:51

Message:
Could you try the snapshot dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?
If it also fails, please attach the Mylog output after setting the Mylog option at setup Dialog page 1.

regards,
Hiroshi Inoue

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1001906&group_id=1000125