ODBC Connection pooling

As of release 2.0.0 of unixODBC the driver supports connection pooling. This is a method to increase the connection speed of drivers, but leaving connection's open and reusing them. This document discusses how to implement connection pooling in unixODBC, and also what you should be aware of before doing it.

Before going any further, I am going to say sorry to those people who I have told that connection pooling was not possible under non Windows. The reason I said that, was based on the belief that Windows connection pooling was doing something more that I have since found it does. It only pools connections within a application, and does not share connection between processes. This means it can be done on non Windows, and here it is.

First why you should not use pooling

There are a couple of points that should be considered before using pooled connections. It is possible that if used incorrectly this can produce a security risk, or at least introduce problems. If your web server using PHP (for example) and pooled connections, does things to the connection, that leaves it it a different state at the end to how it started, you shouldn't use pooling. Lets take a example of this. Assume you have a page that requests a password from a user, then using this password, alters the default database to one that other users are not allowed access to, if this connection is reused by another user, they will have access to data they should not be allowed to see.

If your scripts do things like this, or change default database, or in any way change the connection to the database, it may be worth avoiding pooling.

When can you use pooling

Pooling is only effective when used within a process, a good example is a web server using PHP and ODBC, the connections will be pooled within each web server process, and reused, with a hopefull performance increase. A bad example would be a external CGI program, as each time its run, its a different process, there is nothing to be gained from pooling.

How to do it

Pooling is enabled by editing the odbcinst.ini config file, and as such is enabled on a per driver basis. if its required to have some connections to a driver pooled and some not, then create two entries for the driver, and map different DSN's to the drivers. The setup to enable a pooled connection, would look like this...

[ODBC]
Trace       = No
Trace File      = /tmp/sql.log
Pooling = Yes

[INTERBASE-P]
Description     = Easysoft Driver for Interbase
Driver      	= /usr/local/lib/libib6odbc.so
Setup       	= /usr/local/lib/libib6odbcS.so
FileUsage       = 1
DontDLClose     = 1
CPTimeout       = 120

[INTERBASE]
Description     = Easysoft Driver for Interbase
Driver      	= /usr/local/lib/libib6odbc.so
Setup       	= /usr/local/lib/libib6odbcS.so
FileUsage       = 1
DontDLClose     = 1
CPTimeout       = 0

Using this file any DSN's that used a driver of INTERBASE, would not pool, and those that use INTERBASE-P would be eligible. The "Pooling = Yes" flag is a global switch to enable pooling in the driver manager if it is set to No there will not be any pooled connections. To pool a individual driver the "CPTimeout" value is set to a non zero numeric value. This value indicates the number of seconds a pooled connection will remain open if it is not being used. Note that the connections are only closed when another connection is opened, or checked.

Thats all there is to it, its still in development, but I hope will be of use, if you have any comments, let me know

Nick Gorham