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

Posted by Lars Pind on
I'd like to open up so a Windows client can make an ODBC connection to my OpenACS database on PostgreSQL, which should make it fairly easy to do mail merge with MS word and the like.

Does anyone have any experience with how to set this up on both the Linux and Windows side?


Posted by Jamie Rasmussen on
Have you already looked at psqlODBC? (I'm only using it indirectly through pgAdmin II.)
Posted by Lars Pind on
Yeah, I saw that. And my RH Linux 7.2 already has the following ODBC-related RPMs installed:

$ rpm -qa|grep odbc

Now, how do I get PG to listen for ODBC-connections? Is there a standard ports which ODBC uses?

How can I test that the PG ODBC interface is up?

How can I configure Windows/Office to make ODBC connections to my PG running on my server? Do I need a special Windows PG ODBC client driver to do that?

In short, I don't have a clue how this works, so I'm hoping someone could clue me in :)


Posted by Gilbert Price on
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,

Posted by Gilbert Price on
Oh, as far as drivers go, if you install the pgAdmin2, it should install the latest pg odbc drivers for you, if you don't instll the pgAdmin then you can find the drivers at the postgresql site.

I haven't actually configured a connection for anything other than setting up pgAdmin2, but it should be pretty straight forward once the ODBC is installed on the windows side. I have done alot of MSSQL connections with no problems.

Again_good luck...

Posted by Jamie Rasmussen on
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.

Posted by Roberto Mello on
This document should help on the PostgreSQL setup side: "Installation and configuration of PostgreSQL & ODBC on Redhat Linux"

It even talks about how to setup PG so LOBs work from odbc.

I know I've done this in the past, when porting an MS Access app to PostgreSQL, but can't remember all the steps.


Posted by Jerry Asher on
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 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?

Posted by Jerry Asher on
using strace on isql, I can see that "something" looks for odbcinst.ini in /etc/pgsql, but I am wrong to think it is looking for odbc.ini there.  Instead, I can see "something" (everyone!) opening up /etc/odbc.ini and ~/.odbc.ini. I swear they open these files more than twenty times each.  Huh?

Also of note: in my odbc.ini under driver I give the exact path of the driver itself.  The syntax is that you can specify  either the complete path as I do, or a driver name to be found in the odbcinst.ini file.  If I do the latter, I get a segv.



Posted by Brian Fenton on
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 for more information.

Posted by Richard Hamilton on
I have this set up on our servers so that I can connect to postgres using MS Access down an SSH tunnel. This avoids having to risk exposing postgres to the outside world and it works very well. Most of the postgres steps have been covered in this thread and I will email you the windows ODBC driver that I am using.

There are a couple of little catches (one is that postgres cannot cope with the Access "Yes/No" datatype), the other is that you must run a modification query against the underlying postgres template (mine is template1) in order to add support for certain ODBC calls that postgres does not support by default. You will find the details of this not in the administrator manual but in the docs in the distribution file tree under ODBC install. All of your databases must be created (createbd) AFTER this modification to the template for obvious reasons.

If the tcpip= entry in the postgres config file is not set to true, postgres will not listen to any IP based connections even localhost, it cannot use unix sockets for ODBC. That is why I use tunnelling to add a layer of protection. It also avoid having to try to configure postgres with encryption.

Hope that helps.