Forum OpenACS Q&A: ODBC-connection to Postgres database

Have you already looked at psqlODBC? (I'm only using it indirectly through pgAdmin II.)

Good Morning Lars,

Go into your /usr/local/pgsql/postgresql.conf file and uncomment the connection parameters. I left all as default. Then open the pg_hba.conf file and configure which hosts can connect.

That should do it, I had to modify both to get pgadmin2 to work here...

Good Luck,

Once you've configured PostgreSQL to allow connections from the Windows machine and installed the drivers as Gilbert said, you could test the connection through Excel.  Open a new workspace and select Data -> Get External Data-> New Database Query -> <New Data Source>.  Give your connection a name ("PostgreSQL on MyMachine") and select the PostgreSQL driver.  Enter the database name, machine name, and your username and password.

That's it!  The Query Wizard will step you through creating a query and the data will be slurped into the Excel Worksheet.
You could probably even record this as a macro and use the resulting VBA code as a starting point for your Mail Merge.

I did this (once again) just yesterday, using the RH 8 distribution to get unixODBC.  For pg I am using the RHDB 2.0.  To reiterate some of what has been said....

In postgresql.conf ensure that pc can connect via sockets.

tcpip_socket = true

then in pg_hba.conf, give yourself a raging security hole (or do better with your own configuration.)  For testing through a firewall, I use ssh tunneling to get the remote client to the localhost.... so I am using just localhost connections at the moment, and then on Windows, I create an ssh tunnel.

host all 127.0.0.1 255.255.255.255 trust

Still it took me too long to get the odbc.ini working, and that's because I was using the one in /etc.  I finally got it to work after copying /etc/odbc.ini to ~postgres/.odbc.ini (or generally to ~/.odbc.ini for whichever user you are using.)

Now my understanding is that /etc/odbc.ini should represent system dsns, but I'm wondering if the RH distribution of unixodbc expects that file somewhere else.  Anyone know where?

You can test it locally with isql (the -v flag is very useful) and/or with the simple apps found in "Using ODBC with Red Hat Database".  If you get ViewTable to connect and to work, you're in.

The gborg pg driver for windows appears to work fine.  Unfortunately, the driver setup doesn't include a connection test routine (other driver setups do do that), but you can use either excel or access to quickly test the connection.

You can also check various debug/trace settings by including these in your odbc.ini:

trace = yes
tracefile = /tmp/psql.log
debug = yes
debugfile = /tmp/pdbg.log
commlog = yes

Now, I didn't actually find log files with *those* names in /tmp, but two log files *do* show up, and they can help you understand if you're making the connection or not.

Are you using RH's unixodbc distribution?  For some mysterious reason they appear to fail to include the gui odbc config tools, ODBCConfig and DataManager.  Anyone know why?

Hi Lars,
we (Quest Computing) just recently uploaded an OpenACS templated letters module which may be of some help to you. It's currently Oracle only, but the datamodel is trivial. See https://openacs.org/forums/message-view?message_id=71372 for more information.

Brian