macdevcenter.com
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Open Database Connectivity in Jaguar, Part 2

by Andrew Anderson
07/25/2003

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.

psqlodbc Driver

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 psqlodbc 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 psqlodbc project.

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.

Related Reading

Practical PostgreSQL
By John C. Worsley, Joshua D. Drake

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:

  1. Run the configure command.
  2. Once that completes, run the make command.
  3. Once that completes, run the make install command.

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 make install 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 the 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 for 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
  • port:5432

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 odbctest 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 odbctest.


[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 message in 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