Forum OpenACS Q&A: ODBC-connection to Postgres database
Does anyone have any experience with how to set this up on both the Linux and Windows side?
$ 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 :)
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...
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.
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.
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.
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?
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.
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 http://openacs.org/forums/message-view?message_id=71372 for more information.
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.