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 DSNBefore 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.
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.
Microsoft AccessAccess 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 PostgreSQLSay 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:
- Make backup copy of your Access database.
- Export data to pgsql.
- Set rights, alter Access autonumber fields, and create indexes.
- Rename the tables in the Access database.
- Add links to data now stored in Pgsql.
- Test your application.
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.