D3 OpenDB & PostgreSQL

[Kevin Powick]

I've been doing more work lately with D3's OpenDB product and PostgreSQL and discovered an inaccuracy in what I had once posted to the old CDP group. In order to get the information into this new group, and correct my mistake, I"ve reposted the information below, along with a correction appended to the first bullet point.

* PGSQL Schema-qualified table names are not possible (ex:
MySchema.MyTable). You will have to set up your ODBC DSN entry to use
the option "SET search_path TO <schema name>". This is done at the
bottom of "page 2" of the configuration dialogue for the Windows
standard ODBC driver available from PostgreSQL.org

# This above is not the case. With only a single ODBC DSN entry in Windows, you can have D3 connect to any database and schema in PGSQL. All you need to do is ensure that the entry you make in the HOSTS file in D3 has the appropriate connection information.  Examples:

Windows DSN entry for PostgreSQL instance is "PGDB"

2 Host entries for different schemas within the "Sales" database.

Sales for Company 01

001 08
002 localhost]3458]]30
003 dsn=PGDB;DATABASE=Sales;ConnSettings=SET search_path TO company01

Sales for Company 02

001 08
002 localhost]3458]]30
003 dsn=PGDB;DATABASE=Sales;ConnSettings=SET search_path TO company02

Host entry for the DataWarehouse database using the "purchasing" schema

001 08
002 localhost]3458]]30
003 dsn=PGDB;DATABASE=DataWarehouse;ConnSettings=SET search_path TO purchasing

I expect that there is even more flexibility in the options available in the "connection string" one is providing by attribute #  3 of the HOSTS entry (i.e. username, password, etc), but I have no need to examine such options. 

* Whenever you make a change to a dm,hosts entry for OpenDB, or a
SuperQ pointer for a referenced ODBC table, always log off then back
on again. SuperQ entries are cached, and the only way to clear that
cache, making your changes active, is to relog.

* Whenever you change a setting on your ODBC driver, restart the
OpenDB service AND relog any terminal sessions.

* Take advantage of the /debug switch on the OpenDB.exe to run the
service in the foreground while trouble-shooting. There's not a lot
of information, but some is better than none.

* The /ping switch for OpenDB.exe only works when OpenDB is running as
a foreground process (/debug). When running as a background Windows
service, /ping returns nothing, making it appear as if your service is
not working.

* Use the ODBC driver's built in logging and tracing capabilities to
help narrow down problems.

Getting OpenDB working with MySQL was really pretty easy, but when I
switched to PGSQL, I got misleading, and sometimes inconsistent, error
messages that sent me on a few wild goose chases.

--

Kevin Powick