ODBC

Open database connectivity (ODBC) is an API that provides a product-neutral interface between front-end applications and database servers. ODBC drivers are primarily used to connect Windows-based applications to various RDBMSs. However, ODBC drivers are available for almost all platforms, including UNIX, Mac, and others.

Installation

PostgreSQL can be compiled (or installed from packages) with the necessary drivers for ODBC access. Although PostgreSQL includes some built-in ODBC drivers, other projects are more supported. One of the more popular ODBC access methods is currently the unixODBC project (see www.unixodbc.org).

Installation of the ODBC drivers can be broken up into five steps:

Before the actual installation of the chosen ODBC driver can begin, an ODBC manager must previously exist on the system.All versions of Windows from Windows 95 on already include an ODBC manager. For UNIX/Linux clients, there are several choices. There is the unixODBC manager applet, and there is a free ODBC client called iODBC. (More information can be obtained from www.unixodbc.org or www.iodbc.org.)

If your system was installed from source code, the option --enable-odbc could've been supplied at compile time. (See Chapter 10, "Common Administrative Tasks," for more compile-time options.) Likewise, most of the package-based installs also provide an optional package that includes the required ODBC functionality (for example, postgresql-odbc-7.1.2-4PGDG.i386.rpm).

Alternatively, if the system has previously been compiled without the ODBC option, it can still be compiled by running the make install command in the appropriate directory (for example, src/interfaces/odbc).

The base catalogs require some modifications to be completely ODBC compatible. The file odbc.sql is a collection of modifications that need to be made to the base catalog structure. It is designed to be executed as a script and should require no human inter-action. To automatically apply these changes, execute the following command as the PostgreSQL DBA user:

>psql -d template1 -f PATH/odbc.sql 

Additionally, make sure you start the postmaster with the -i option (or make the appropriate change to the postgresql.conf file), which enables access from TCP/IP connections.Additionally, most systems require that the pg_hba.conf file be edited. (See Chapter 10 for more information on PostgreSQL administration.) Otherwise, the client would need to be local to connect successfully.

As for installing the client machines, the easiest method is to download the Windows executable that automatically installs and configures Windows machines. This installer can be obtained from the following (check mirrors also):

ftp://ftp.postgresql.org/pub/odbc/versions/full/

Additionally, the MS Installer (MSI) or plain DLL versions of the driver can be obtained from the following:

ftp://ftp.postgresql.org/pub/odbc/versions/msi/

ftp://ftp.postgresql.org/pub/odbc/versions/dll/

The next step is to configure the odbc.ini file (or preferably, use the provided GUI management dialog).

The odbc.ini file has three required sections:

• [ODBC Data Sources] 

  A list of database names.

  This section must include the following:

Driver = Path (e.g., prefix/lib/libpsqlodbc.so) 
Database=DatabaseName 
Servername=localhost 
Port=5432 

• [Data Source Specification] 

  A configuration section for each ODBC data source.

• [ODBC] 

  Defines the InstallDir keyword.

An alternative to specifying all of these options within an .ini file is to utilize the GUI configuration tool provided with the Windows driver.

The options provided via this method include the following:

• Disable Genetic Optimizer. Automatically turns off the back-end genetic optimizer at connection time.

• Keyset Query Optimization (KSQO). Some applications, specifically the MS Jet Database Engine, use "keyset" queries. Many of these queries will most likely crash the back end without the KSQO feature.

• CommLog. Log communications to/from the back end to that file.

• Recognize Unique Indexes. This setting enables Access 95 and 97 to ask the user at link time what the index should be.

• ReadOnly (default). New data sources will inherit the state of this box for the data source read-only attribute.

• Use Declare/Fetch. If true (default), the driver automatically uses declare cursor/fetch to handle SELECT statements and keeps 100 rows in a cache.

• Parse Statements. If enabled, the driver will parse a SQL query statement to identify the columns and tables and to gather statistics about them such as precision, nullability, aliases, and so on.

• Unknown Sizes. This controls what SQLDescribeCol and SQLColAttributes will return for precision for character data types. This was more of a workaround for pre-6.4 versions of Postgres. Options are as follows:

Maximum. Allows return of the maximum precision of the data type.

Dont Know. Returns a "Dont Know" value and lets the application decide.

Longest. Returns the longest string length of the column of any row.

• Data Type Options. Affects how some data types are mapped. Options are as follows:

Text as LongVarChar. Postgres TEXT type is mapped to SQLLongVarChar; otherwise, SQLVarChar.

Unknowns as LongVarChar. Unknown types (arrays and so on) are mapped to SQLLongVarChar; otherwise, SQLVarChar.

Bools as Char. Boolean returns are mapped to SQL_CHAR; otherwise, to SQL_BIT.

• Cache Size. When using cursors, this is the row size of the tuple cache. If not using cursors, this is how many tuples to allocate memory for at any given time.

• Max VarChar. The maximum precision of theVarChar and BPChar (char[x]) types.

• Max LongVarChar. The maximum precision of the LongVarChar type.

• SysTable Prefixes. By default, names that begin with pg_ are treated as system tables.This allows defining additional ones. Separate each prefix with a semicolon (;).

• Connect Settings. These commands will be sent to the back end upon a successful connection. Use a semicolon (;) to separate commands.

The driver also has these data source/connection options:

• ReadOnly. Determines whether the data source will allow updates.

• Row Versioning. Allows applications to detect whether data has been modified by other users while you are attempting to update a row. The driver uses the xmin system field of Postgres to allow for row versioning.

• Show System Tables. The driver will treat system tables as regular tables.

• OID Options:

Show Column. Shows the OID.

Fake Index. Fakes a unique index on OID.This is mainly useful for older MS Access–style applications.

• Protocol:

  6.2. Forces the driver to use Postgres 6.2 protocol, which had different byte ordering, protocol, and other semantics.

  6.3. Use the 6.3 protocol. This is compatible with both 6.3 and 6.4 back ends.

  6.4. Use the 6.4 protocol. This is only compatible with 6.4.