Connecting to PostgreSQL from Windows Platforms

The purpose of this article is to show you how to connect to PostgreSQL (pgsql) from 32 bit Windows platforms (9X, NT, and 2000). This is important because for the time being the Windows platform outnumbers Linux on the desktop and as software developers we are more likely to be able to determine the backend portions of our application. The example I’ll use will consist of converting a Microsoft Access application to pgsql. It’s pretty easy to extend the knowledge in this article to just about any Windows application that can access ODBC enabled information.

Before you can try these examples yourself you will have to get the Win32 ODBC driver for pgsql from ftp://ftp.postgresql.org/pub/odbc/index.html. You’ll want to click the download’ link on the lower left part of the page to go to the download section. There are caveats listed on the pages about which platforms the ODBC driver will work with. I’ve personally used this driver on NT Workstation and Server as well as Windows 2000 Professional without problem. You will have to download and install the driver before you can continue with the examples provided in the rest of the article. If you’re interested in what makes an ODBC driver tick you will most certainly want to download the LGPL’ed source code as well.

A number of assumptions are in place for the duration of this article: 1) You have a working Pgsql installation, 2) You have modified pg_hba.conf to allow connections from hosts other than the server itself, 3) You have started pgsql with the i flag, and 4) You have created a sandbox database you have create table rights to.

This article won’t be discussing the merits of ODBC or the services it provides. This information can be easily obtained from the Microsoft website.

Setting up a DSN

Before you can use pgsql from your Windows applications you have to create a DSN. Assuming you have successfully installed the driver you will be presented with the dialog shown in Figure 1 when you create a pgsql DSN.

Figure 1

When you click the Driver button you will be presented with the dialog box show in Figure 2. Figure 3 displays the options given to you when you click the DataSource button.

The options shown in Figure 2 and Figure 3 are documented at ftp://ftp.postgresql.org/pub/odbc/psqlodbc_confighelp.html and reading the page is suggested or you can simply experiment with settings to see their effects.

Figure 2

Figure 3

Microsoft Access

Access is a great tool to link to and transform data. From this one application you can easily link to just about any type of database or file on the market. With the addition of the ODBC driver mentioned in the preceding paragraph Access can talk with pgsql. Making Access talk to Pgsql is as simple as left clicking on the database workspace and selecting Link from the popup menu assuming, of course, you have created a DSN for the pgsql database in question.

Moving Databases From Access to PostgreSQL

Say what you will about Microsoft Access the product allows folks to bank together quick little database apps with a minimum of fuss. Their New Database Wizard is nifty in that by answering a few short questions you end up with a nice fluffy single user database application. These applications range from inventory to contact managers. While individually these little database turkeys don’t amount to much they will serve as an example of how to move an Access database up to pgsql with a minimum of hassle.

Generally, you’ll want to proceed with the following steps when moving an Access database to Pgsql:

1. Make backup copy of your Access database.
2. Export data to pgsql.
3. Set rights, alter Access autonumber fields, and create indexes.
4. Rename the tables in the Access database.
5. Add links to data now stored in Pgsql.
6. Test your application.

The first thing you will want to do, after making a backup of your Access database, is to move (or upsize) your data to pgsql. This is simply a matter of right-clicking on the individual tables and selecting Export from the pop-up menu. After clicking the Export option you will be prompted to select a destination for your data. Looking over this dialog you will see an entry towards the bottom labeled Save as Type. Click this field to produce a dropdown list of export targets. At the bottom of the list you will see an entry named ODBC Databases. Select this export target.

The next thing you will be asked for is a name for this object. At this point you are naming a pgsql table that will hold the database from the current Access table. It is a good idea to name the pgsql the same as the Access table. You’ll learn why later on.

Once you have named the table you will be presented with a DSN list from your current ODBC setup. Select the Machine Data Source DSN that represents the pgsql server you are connecting to and click OK.

If there is any time during this process where errors can raise their ugly heads that would be now. Access will now proceed to move your data, row by row, to pgsql. If an error occurs the whole transaction will be swept back (which is a good thing). If you crossed your fingers and didn’t use a ton of exotic data types you shouldn’t have a major problem moving your data to pgsql.

If everything went according to plan you should now have a number of tables on your pgsql server. You will now want to create users and set access rights to mirror those who use your Access database. Access is granted in two steps: 1) creating the user account, and 2) issuing GRANT statements to allow users to view and update information in your database.

I won’t be discussing how to create users and granting access rights. This information is readily available in the pgsql documentation (which comes package in the source distribution as well as an HTML version on the pgsql website). Besides, that could be a topic for a whole article in and of itself.

A side effect of the export process is that whatever indexes you created in your Access database won’t be mirrored on the pgsql side. You will have to manually create your indexes on the pgsql side of the house after you complete the export process.

Finally, Access has this nifty little field type called an AutoNumber. Basically you put a field like this in your table to be used as a primary key. They’re easy to use (you never set an auto number field, they set themselves) but the problem is that AutoNumber fields are stored as only numeric fields under pgsql. Not to fear though there’s an easy solution.

Say, for example, you have a tabled named INVOICE with an AutoNumber field named INVNUM. When the INVOICE table is exported to pgsql you are left with a standard numeric field with no special auto numbering property. Pgsql has a feature called a Sequence. A sequence in pgsql operates much like an AutoNumber field in Access. It’s a manual process (read: you have to type a few commands) to create a sequence and modify a table but it isn’t all that difficult.

To continue the example above you have a table named INVOICE and a numeric field named INVNUM. How do you go about changing the INVOICE table to use a sequence? First you have to create a sequence with the following command:

CREATE SEQUENCE INVNUMSEQ START x;

Now that we have the sequence defined we need to modify the INVOICE. You do this with an ALTER TABLE command:

ALTER TABLE INVOICE ALTER INVNUM SET DEFAULT NEXTVAL(INVNUMSEQ');

At this point we have your data stored safely in pgsql and you have created a slew of users to access it. What we’re going to do now is replace the tables in your Access application with links to those now stored in pgsql. Before we do this you will want to make a copy of your Access database. Bad things happen to those who modify vast amounts if bits with wild abandon. Don’t be one of those people okay?

Now we want to rename the existing Access tables to a name other than what they are presently named. For the time being rename all the existing tables by append OLD to their current names.

Now right-click on the table space in your Access database and click the LINK option. You will be presented with a dialog box quite similar to the one shown when exporting the data to pgsql. As with the last dialog you will see a field labeled Files of Type. Select this item and select ODBC Databases.

You will then be presented with a DSN list. Select the DSN you created for your pgsql server. Once you select the proper DSN you will be presented with a Link Tables dialog. Depending on the options you chose when creating the DSN you will either see all the tables in the pgsql database (system catalogs and your tables) or just a list of the tables we just exported. Select one all the tables you just created and click OK.

Access will now proceed to link the pgsql tables to your Access database. For each table you link Access will ask you about a primary key: when prompted select the fields that comprise the primary key for each table you link.

At this point in the game you will have an Access database with links to pgsql. The name of the Access links will coincide with the name of the table you linked to. We’re now ready to start playing with your Access database, which isn’t a database now, so much as a code repository. Your logic is in Access while your data now resides on pgsql.

At this point you will want to start testing your Access application to verify the operation. When you encounter a problem fix it on the spot. Continue till you’re satisfied that everything went well and start using your application.

You can see that using pgsql from Windows is straightforward. Using the ODBC driver from the likes of VB, Excel, or Visual C++ is simply an extension of the topics covered here. If you need help connecting with Visual Basic send me email and I’ll help you out.