Open Database Connectivity in Jaguar, Part 2by Andrew Anderson
In my previous article, I explored the intricacies of setting up MySQL with ODBC under Jaguar. Since MySQL is not the only open source database available, I'll now cover setting up ODBC with PostgreSQL, which, in many ways, is a less frustrating configuration.
If you haven't already, I recommend reading the first article, "Open Database Connectivity in Jaguar," as it covers some of the basics of ODBC and how to set up ODBC components. For reference, we will once again be using iODBC's Administrator, since it's better behaved and has more features than Apple's tool.
The first step in setting up PostgreSQL was to get the driver. I went
to the PostgreSQL's web site,
looked around for
psqlodbc driver, and found it at the
project section of PostgreSQL's GBorg site. GBorg is home to "PostgreSQL
related projects" and contains all sorts of interesting projects in
addition to the
When to the download section of the page, there were five different distributions available: four different Windows distributions and a source distro. At first I was a little scared by this, but then I recalled that the standard PostgreSQL distribution that I was using came as source, as well. When I originally installed PostgreSQL, I had used the article on Apple's Developer pages, substituting version 7.3.3 for version 7.3.1, and it worked really well.
Before attempting the download and install, I figured it was wise to make sure that I did not already have the ODBC drivers installed from my original setup. Why reinstall if I didn't need to? I ran a search from the finder for pgsqlodbc and nothing came up, so I downloaded version 7.2.5 of the source distribution of pgsqlodbc and hoped for the best.
Once I unzipped and untarred the distribution, the next step was to compile, link, and install the source. PostgreSQL, like many open source, source-code distributions, has a three-step process for compiling, linking, and installing the code:
- Run the
- Once that completes, run the
- Once that completes, run the
Of course, these three commands mask a number of underlying processes:
configure probes your machine and determines your type of machine,
operating system, libraries, compilers, and dozens of other criteria;
make does the actual compiling and linking, and
puts the compiled code into its final home.
Note: Before you can compile and set up the driver, you need to make
sure that you have Apple's Developer Tools installed. The easiest way
to do this is to see if you have a
/Developer directory; if you do not
have one, then you will have to download Apple's Developer Tools from Apple's Developer site.
If you haven't already, sign up for a free Apple Developer Account.
Once you do that and start the download, go get dinner -- the download
is over 300 megs.
To start the compile process, I went into the source directory and ran
configure command followed by the
make command. Both
of these commands took a few minutes and spit out loads of messages
on the screen, but they both succeeded with no errors reported. After
that, I ran the
make install command, which was supposed to install
the just-compiled files into /usr/local/lib. After running it, I checked
out /usr/local/lib and sure enough, psqlodbc.la and psqlodbc.so
were there. With the compile and install out of the way, I was now
ready to set up PostgreSQL to use the ODBC driver.
Setting up PostgreSQL
To use ODBC drivers, PostgreSQL needs to be able to accept connections
through TCP/IP sockets. By default, this functionality is turned off, so I needed to turn it on. Doing this was simple. I needed
to change a parameter in postgresql.conf and restart PostgreSQL. First
I located postgresql.conf, which was in the default data directory
for the PostgreSQL database that I was using. In this case, the file
was in /usr/local/pgsql/data. I then opened the file and searched
tcpip_socket. In my case, the line was:
#tcpip_socket = false
To allow TCP/IP socket connections I had to make the line not be a comment
and change the value to
true, so that it looked like:
tcpip_socket = true
Once that was done, all I had to do was restart PostgreSQL. There are several ways to do this; I recommend doing a complete shutdown of PostgreSQL, followed by a startup. To do that, I did the following:
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
Now I was ready to set up the driver and DSN in the iODBC Administrator.
Setting up iODBC
Once the driver was installed, the next step was to set up the ODBC driver
for PostgreSQL in the iODBC Administrator, which is basically the same
process as setting up the driver for MySQL from my previous article.
I ran the ODBC administrator, chose the "ODBC Drivers" tab, and chose
"Add a Driver." From there, I gave the driver a name and choose the PostgreSQL
driver. As we saw before, the default
make install puts psqlodbc.so in /usr/local/lib. Like the MySQL install,
there was no need to set up key-value pairs at this stage, as I had no
options that were universal across all instances of this driver.
Now it was time to set up a DSN, so I switched to the "User DSN" tab, chose "Add," chose my driver, then set up the Data Source Name and the key-value pairs. The key-value pairs that I use for PostgreSQL are:
- database: test
- servername: localhost
- username: user
- password: password
Testing the DSN
Once everything was set up, the next step was to test the connection. As
in the previous article, there are two ways to test an ODBC connection
-- by using the test button in the iODBC Administrator, or the
program. Since I was already in the iODBC Administrator, I hit the test
button and got the following message:
DSN:postgrestest The connection DSN was tested successfully, and can be used at this time
That looked promising; now it was off to test the connection in
[troll:~] aaa% /usr/bin/odbctest iODBC Demonstration program This program shows an interactive SQL processor Enter ODBC connect string (? shows list): ? DSN | Description --------------------------------------------------------------- postgrestest | postgresql Enter ODBC connect string (? shows list): dsn=postgrestest Have a nice day. [troll:~] aaa%
This result looked strange, since normally
odbctest would drop
you into an interactive SQL processor or give back some sort of error
or status message. I figured that, since there was no specific error
odbctest and the first test appeared to work, the
only way to see if the connection was alive would be to test it in an
ODBC-compliant, end user program.
Accessing PostgreSQL Data in Excel
To do this, I went into Excel, opened a new workbook, chose "Get External Data" from the "Data" menu, and chose the "New Database Query" menu option. At this point, Microsoft Query ran and brought up a screen to "Select a Data Source." I chose my PostgreSQL test DSN, and was brought into Microsoft Query. I chose the "SQL View" tab and ran a test query, and it ran perfectly.
What was weird, though, is that normally, when you run Microsoft Query, it comes up with a list of tables in the current database that you are querying; this did not happen when I ran it with the PostgreSQL driver. The table list box appeared, but it appeared empty. I am not sure if this is a PostgreSQL issue or an Excel issue, but it did not affect my test queries in any way. I just had to know what tables were in the database.
MySQL vs. PostgreSQL
When it gets down to it, which open source database is the best to use on Jaguar if you want ODBC support?
Obviously, there's no simple answer, but you can narrow your choices down with a few simple questions:
- Do you really have a choice between the two products?
PostgreSQL and MySQL, while similar, have some specifically different feature sets that can affect your target application. Some of these other features are often more important than ODBC support, so I would go through a comparison of the databases first, and then worry about ODBC.
- Do you have existing data in one of the programs?
If you already have one of these databases set up in a production environment, think about the benefits before switching to the other for ODBC support. While switching to a different version of MySQL is still changing databases, it is not as severe as changing your entire database platform.
- How critical is ODBC support to you at this stage?
While ODBC is crucial for certain applications, there are other applications and programming languages that can access data from both products without using the ODBC layer (these methods also have the bonus of generally being faster, as well). If you need to use MySQL and can wait for ODBC support, I would wait.
What if you have the choice between the two products, you have no existing data, and you really need ODBC right now? Then what would I recommend?
In that case, I would lean towards using PostgreSQL. While it may be
awkward for some to compile the
psqlodbc driver, the driver works "out
of the box" with no problems, the setup is straightforward, and there
are no issues with the driver. This is not a knock against MySQL/MyODBC,
though. After my initial problems with it, it performed really well.
We know the MyODBC/MySQL can work in Jaguar, so I would say once the
MySQL development team releases a working version of MySQL/MyODBC, that
it would be a perfect choice.
Andrew Anderson is a software developer and consultant in Chicago who's been using and programming on the Mac as long as he can remember.
Return to Mac DevCenter