[ 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=10019 06&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_SetConnect Attr for 05713B88: 115 00000000
[5888-0.018]the application is unicode
[5888-0.019][SQLDriverConnectW][5888-0.019]PGAPI_DriverConne ct: 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_premat ure=-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',sho wsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''


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

>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=10019 06&group_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend