ING Logo

Using ODBC to access PostgreSQL databases at ING

PSQL-ODBC-1

$Revision: 1.4 $



Author: D. B. Armstrong
dba@ing.iac.es

1. Introduction

Purpose of this document

This document describes how to install and configure the ODBC driver to access the PostgreSQL databases at ING. It assumes that a Postgres Database is installed and functional. It describes how to setup Open Office, Microsoft Word and Star Office to use the ODBC driver.

2. Driver installation

Solaris and Linux

Obtain either the unixODBC tar file for installation on Sun Machines using the Solaris Operating system or the rpm file for installing on Linux machines.
The current unixODBC version installed is 2.0.7 and the rpm file is named unixODBC-2.0.7-1.i386.rpm.
For the Linux install use the Red Hat Package manager from the command line or a suitable GUI like tkrpm. You will require root access to install the libraries and configuration files


rpm installs libraries and configuration files either into /usr/lib and /etc or /usr/local/lib and /usr/local/etc.
 ll /usr/local/etc/
total 20
drwxr-xr-x    2 root     root         4096 Nov 20 15:16 ODBCDataSources
-rw-r--r--    1 root     root         1493 Nov 20 16:06 odbc.ini
-rw-r--r--    1 root     root          317 Nov 20 10:45 odbcinst.ini
-rw-r--r--    1 root     root          431 Nov 20 11:06 template.ini

Microsoft Windows

To install ODBC on Microsoft Windows three components are required to access the PostgreSQL Database server. Firstly you require the Microsoft Data Access Components (MDAC). This can be downloaded from :-
http://www.microsoft.com/data/download.htm
Here you will find free downloads for all of the MDAC releases. These downloads are redistributable according to the End User License Agreements posted with each release. Eight releases of MDAC are available here: MDAC 2.7, two of MDAC 2.6, three of MDAC 2.5, and two of MDAC 2.1. You can also find several release manifests. Also please look at the information about installing MDAC.
MDAC 2.7 RTM Refresh (2.70.9001.0) is the version which I downloaded from this site. This release contains the same version of the MDAC components that are installed by Windows XP.
Secondly also download the recently updated Component Checker tool from this page. This release includes updated snapshots for MDAC 2.6 SP2 and MDAC 2.7 Refresh releases (filename cc.exe)
Thirdly go to :-
http://ftp.rge.com/pub/database/postgresql/odbc/versions/full/
Download the latest version of psqlodbc-xx_yy_zzzz.zip where xx and yy are the same as the version of PostgreSQL in use (version 7.2 xx=07 and yy=02). The number zzzz is the release number of psqlodbc. I have currently installed from the psqlodbc-07_02_0001.zip file.

Then install all 3 downloaded components by executing the 2 exe files and unzipping the psqlodbc zip file. Check using the component checker (ComCheck.exe) that PostgreSQL is available.

Microsoft ODBC Configuration

3. ODBC configuration

On the Sun or Linux machine edit the odbc.ini file and add the following:-

[ODBC Test Database]
Description = ODBC Test to Postgres
Driver = PostgreSQL
Trace = Yes
TraceFile = sql.log
Database = testodbc
Servername = csgsrv.ing.iac.es
UserName = database
Password = REMEMBER to SET the database password Here!!
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =

Now edit the odbcinst.ini file ensuring the correct location of the library files :-

[TXT]
Description = Text file driver
Driver = /usr/local/lib/libodbctxt.so
Setup = /usr/local/lib/libodbctxtS.so
FileUsage = 1

[PostgreSQL]
Description = PostgreSQL driver for Linux and Win32
Driver = /usr/local/lib/libodbcpsql.so
Setup = /usr/local/lib/libodbcpsqlS.so
FileUsage = 1

The ODBC configuration should now be complete.

4. Using the ODBC Driver with Star Office

Start up Star Office on the computer this should provide you with the following window:-

Star Office Startup window

Expand the sidebar to allow selection of the database. Click on the Database Icon to select a database.

Database Selection window

Select the Type tab to display the Database type and Data source options.
Select ODBC from the drop down list in the Database type selection window.

Type tab Database type Selection

Click on the Browse button to get a selection for the Data source.

Data Source Selection

Select ODBC Test Database (PostgreSQL) and click on the OK button to complete the selection.
You should see the window as shown below:-

Database Selection completed

You should now select the ODBC tab to input the User name and Password. Click on the OK button to complete the user input.

User Name and Password

This will show a selection of icons for Forms, Reports, Queries and Tables. Select the icon named Tables:-

Forms Reports Queries and Tables

You should now see the available tables in this database.

Availiable Databases

If you click on the testtable icon you should then see the information contained in this table

Database Information

If you have reached this point then your installation was sucessful.

5. Using the ODBC Driver with Open Office

Start up Open Office on the computer this should provide you with the following window:-

Open Office Startup window

From the menu bar select the Tools->Data Sources option. This will open the Data Source Administration Window:-

Database Selection window

Select the New Data Source button.
Select ODBC from the dropdown list in the Database type selection window.
Set the Name option (default is Data source 1) to CSGSRV.
Click on the ... button in the Data Source URL field to pop up the list of databases to select:-

Data Source Selection

Select ODBC Test Database and click on the OK button to complete the selection.
You should see the window as shown below:-

Database Selection completed

Click on the Apply button to save the new Data Source.
You should now select the ODBC tab to input the User name and select the Password Required box. Click on the Apply button to complete the user input.

User Name and Password

To connect to the database select the Tables tab in the Data Source Administration Window. This will pop up a window in which to input the password for the "database" user:-

Password Request Window

Fill in the password and select OK. You should now see the available tables in this database.

Availiable Database Tables

If you click on the Queries Tab this will allow you to create a query for the selected tables:-

Queries Window

If you select the first icon (New Query Design) this will pop up 2 windows (Add tables and Query:Query1) to start designing a new query. Select mytest in the Add Tables window and click on the Add button:-

Add Tables Window

This should now be displayed in the Query:Query1 window:-

Query Window with mytest Table added

To create the query click on the first empty field in the Table row and select "mytest" from the drop-down menu. Click on the first empty field on the Field row and select "data" from the drop-down menu.
Run the command by clicking on the first icon (Run) above the mytest table display. If you were successful you should see the data from the table shown as follows:-

Query Window after running query on mytest Table

If you have reached this point then your installation was successful.

5. Using the ODBC Driver with Microsoft Word

Start Microsoft Word and show the database toolbar by selecting the option View -> Toolbars ->Database. An extra toolbar should appear if this option is not already selected.

Microsoft Word with Database Toolbar

To setup Word to insert data select the 7th icon from the left on this row (Insert Database) and the Database screen should pop up.

Microsoft Database Window

Click on the "Get Data..." button this opens the Open Data Source window

Microsoft Open Data Source Window

Select the "MS Query..." button which pops up the Choose Data Source window

Microsoft Choose Data Source Window

With the "New Data Source" option selected in the Databases tab click on the OK button. This will pop up the Create New Data Source window

Microsoft Create New Data Source Window

Type into line1 "Test Database" and press return. Line 2 will allow you to select a driver from the drop down selector on the right. Select the PostgreSQL option.

Microsoft Create New Data Source Window

Click on the Connect button (line 3) to obtain the following window:-

Microsoft PostgreSQL Connection Window

Insert the following Information (with the correct Password of course):-

Microsoft PostgreSQL Connection Window

Click on the OK button to bring you back to the Create New Data Source Window Line 4 will allow you to select a default table from the drop down menu. Select the mytest table and click on the Save my user ID and password option box. The Create New Data Source window should now look like this :-

Microsoft Database Table Window

Click on the OK button this will bring you back to the Choose Data Source window, This window now should have the option for Test Database available, highlight this and click on the OK button.

Microsoft Database Window

The PostgreSQL database table will now be accessed to obtain the data fields in this table. The query on this table can now be defined.

Microsoft Query Window

Select the "data" and "id" fields in the mytest window by double clicking the mouse this will show the data returned from the SQL database.

Microsoft Query Window

To return this data select File-> Return data to Word option on the query menu. This Menu option returns you to the Database Window.

Microsoft Database Window

Select the "Insert Data..." button which pops up the Insert Data window

Microsoft Database Window

Click on the OK button to insert the data into the Word document.

Microsoft Database Window

You have now inserted data from the PostgresSQL database into your Word document.


© 2001 by Isaac Newton Group
All rights reserved.
Last modified: Fri Jul 26 10:30:23 WEST 2002