Perl DBI DBD::ODBC tutorial: Drivers, data sources, and connection

So, why is this so good? The ODBC API SQLDriverConnect is a lot more flexible. Instead of passing just a DSN name, database user name and database password (like SQLConnect) you pass one string containing a semi-colon separated list of ODBC driver attributes. This means:

1. You can pass more attributes to the ODBC driver than just a DSN name, a database user name, and password.
2. Instead of putting valuable user names and passwords into a file that someone else can potentially read, you can prompt for them and then pass then on to the ODBC driver.
3. You can use DSN-less connections.

ODBC defines a set of standard attributes:

• DSN=xxx The name of the data source name other attributes can be retrieved from.
• DRIVER={xxx} The driver to be loaded. You cannot use this at the same time as DSN or FILEDSN.
• FILEDSN=/path/file A file containing the DSN definition instead of using the odbc.ini file. You cannot use this at the same time as DSN or DRIVER.
• UID=db_user = name The database user name.
• PWD=db_password The password that goes with UID.
• SAVEFILE=/path/file If a successful connection is made, save the attributes for the connection in /path/file. Must be used with FILEDSN or DRIVER.

For DSN-less connections, you use the DRIVER attribute and concatentate all the other required attributes separated by semi-colons. For example, assume you had this Easysoft ODBC-ODBC Bridge DSN:

[test] 
Driver     = OOB 
ServerPort = demo.easysoft.com:8888
TargetDSN  = pubs 
LogonUser  = user 
LogonAuth  = password

Instead of using a DSN, you can connect to it using:

my $CONNECT = "DRIVER={OOB};ServerPort=demo.easysoft.com:8888;"
            . "TargetDSN=pubs;LogonUser=user;LogonAuth=password;";
my $dbh->connect("dbi:ODBC:$CONNECT", "dbuser", "dbpassword");

If you wanted to save this data source information into a file after a successful connection, you can add SAVEFILE=file.

my $CONNECT = "DRIVER={OOB};ServerPort=demo.easysoft.com:8888;"
            . "TargetDSN=pubs;LogonUser=user;LogonAuth=password;"
            . "SAVEFILE=/tmp/test.dsn";
my $dbh->connect("dbi:ODBC:$CONNECT", "dbuser", "dbpassword");

If you don't use an absolute file path for SAVEFILE with unixODBC, it will attempt to save the file DSN into /etc/ODBCDataSources, which you may not have permission to write to. The Driver Manager will remove the PWD attribute so as not to save a password in the file. The DSN saved for the above Perl would be:

[ODBC] 
DRIVER     = OOB 
ServerPort = demo.easysoft.com:8888
TargetDSN  = pubs 
LogonUser  = user 
LogonAuth  = password 
UID        = dbuser

To use FILEDSN to point the Driver Manager at a different file, use the FILEDSN attribute. You need to create a file like the one output like SAVEFILE above, containing a DRIVER attribute to tell the Driver Manager which ODBC driver to use and all the attributes the ODBC driver needs to connect.

Note There is a small gotcha using file DSNs created with SAVEFILE, as the Driver Manager will remove PWD (database password). You need to add PWD=xxx to the end of the connection string. Refer to Miscellaneous issues.

Obtaining the ODBC out connection string

When you use ODBC API SQLDriverConnect to connect to a data source, a string is returned that you can use in subsequent connection requests. The ODBC driver may add additional attributes to your connection string. (On Windows, the ODBC driver may even display a dialog box to allow the user to fill in fields not specified in the connection string. This does not apply to DBD::ODBC, which uses SQL_DRIVER_NOPROMPT.) For example:

my $h = DBI->connect('dbi:ODBC:DSN=mydsn');
print "Out Connection String: ", $h->{odbc_out_connect_string}, "\n";

outputs the following connection string when connecting to Microsoft SQL Server:

Out Connection String: DSN=mydsn;UID=;Trusted_Connection=Yes;WSID=my_workstation

As with FILEDSN, to use this string in further connect calls, you will need to supply the password with the PWD attribute.

Some applications store the out connection string in their application data for future use. For example, Microsoft Access does this when you link a table.

Connection errors

If the DBI connect method fails, it returns undef and, by default, prints the error (PrintError => 1). Refer to the example below and DBI connection attributes for information about how you can change this behaviour.

If the DBI connect method fails $DBI::err and $DBI::errstr will be set (note that $! is not explicitly set). So, taking our simple example in Simple connect and disconnect and adding implicit printing of errors, we have:

use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Test', undef, undef, {PrintError => 0, RaiseError =>0});
if (!$dbh)
{
  print "$DBI::err\n$DBI::errstr\n$DBI::state";
}
else
{
  $dbh->disconnect if ($dbh);
}

An example of running this with the Easysoft ODBC-ODBC Bridge client when the server is not listening is:

-1 [unixODBC][Easysoft ODBC (Client)] 
  Connection refused, connect(), after 5 attempts (SQL-HY000) 
[unixODBC][Easysoft ODBC (Client)] 
  Client unable to establish connection (SQL-08001) (DBD: db_login/SQLConnect err=-1) 08001

Here, DBI set $DBI::err to -1, the ODBC driver added two diagnostics Connection refused... and Client unable to establish connection, and the final ODBC error state was 08001.

$DBI::err

$DBI::err will be undefined for a successful method (SQL_SUCCESS), an empty string ("") for a successful method that returns SQL_SUCCESS_WITH_INFO, 0 for a warning, and an ODBC return status for an unsuccessful method (for example, SQL_ERROR = -1, as shown in the previous example).

$DBI::errstr

The format of the ODBC error diagnostic is defined by ODBC. The entries in square brackets show modules in the ODBC chain and you should read them left to right. (That is, the ODBC API call made by the application was passed through the leftmost module to the rightmost module.) Therefore, the item in the furthest right [ ] is the one actually reporting the problem.

$DBI::state

The state is the five character ODBC error state. The special case of 00000 (indicating success in ODBC) is translated to an empty string by DBI.

You can find a much more comprehensive description of ODBC error messages and states in the ODBC diagnostics and error status codes.

Connection warning and informational messages

ODBC calls may return errors (SQL_ERROR) or informational messages (SQL_SUCCESS_WITH_INFO). During the connection process, some ODBC drivers may report informational messages, for example, the Microsoft SQL Server ODBC driver often issues informational messages like:

[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed database context to 'master'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed language setting to us_english. (SQL-01000)Rows affected: 5

DBD::ODBC reports these ODBC informational messages as DBI informational messages: it calls DBI's set_err with an empty string "" as the error type. This tells DBI it should not automatically print the message, but you can detect them and print them if you want. In addition, DBI supports warning messages that arise from calls to DBI's set_err with a type of 0.

Note In Perl, both "" and 0 are false, but both are defined. DBI normally sets PrintWarn if $^W is true. To print informational messages yourself (which DBI will not ever do) and warning messages (which DBI will only do if $^W is true), on the connect call, do this:

my $h = DBI->connect;
if (defined($h->err)) {
    if ($h->err eq 0) {
      print "Warning message : ", $h->errstr, "\n";
    } elsif ($h->err eq '') {
      print "Informational message : ", $h->errstr, "\n";
    }
}

which for Microsoft SQL Server will normally print something like this:

Informational message : [Microsoft][ODBC SQL Server Driver][SQL Server]
Changed database context to 'master'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed language setting to us_english. (SQL-01000)

DBI connection attributes

In DBI, there are attributes common to all handles (for example, PrintError) and attributes specific to a particular handle type (for example, autoCommit). Because this is a connection tutorial, we are only interested in connection handle attributes and there are only two that affect connections.

You can set connection attributes in two ways:

• at connect time, like this:

  my $dbh = DBI->connect('dbi:ODBC:test', 'dbuser', 'dbpassword', {attribute => value});
  

• after connection with:

  $dbh->{attribute} = value
  

AutoCommit

If AutoCommit is turned on (the default), database changes are automatically committed and can not be rolled back. If AutoCommit if turned off, database changes are not made until they are committed (with $dbh->commit) and they can be rolled back with $dbh->rollback. If AutoCommit is turned off and you fail to commit the transaction, DBI automatically rolls it back before disconnecting.

As far as DBI is concerned, there are three database categories with respect to transaction support:

• Databases which don't support transactions at all. Attempting to turn AutoCommit off for these databases is an error and using commit and rollback methods generates a warning.
• Databases in which a transaction is always active. If AutoCommit is off you need to use the commit method to commit the transaction or the rollback method to roll it back. If AutoCommit is on then DBI acts as if the commit method was called automatically after every successful database operation. Also, changing AutoCommit from off to on will trigger a commit.
• Databases in which a transaction must be explicitly started (C<'BEGIN WORK'>). We don't think any databases with ODBC drivers fall in to this category.

You can find out what transaction support your ODBC driver provides using DBI's get_info method. Query for SQL_TXN_CAPABLE (which is SQLGetInfo value 46):

use strict;
use DBI;
my $dbh = DBI->connect('dbi:ODBC:test', 'db_user', 'db_password');
print $dbh->get_info(46),"\n";

ODBC defines 5 possible returns:

• SQL_TC_NONE (0) Transactions are not supported.
• SQL_TC_DML (1) Transactions can only contain Data Manipulation Language (DML) statements. (For example, SELECT, INSERT, UPDATE, and DELETE.) Data Definition Language (DDL) statements generate an error.
• SQL_TC_ALL (2) Transactions can contain DML and DDL statements.
• SQL_TC_DDL_COMMIT (3) Transactions can only contain DML statements. DDL statements (for example, CREATE TABLE, DROP INDEX, and so on) cause the transaction to be committed.
• SQL_TC_DDL_IGNORE (4) transactions can only contain DML statements. DDL statements encountered in a transaction are ignored.

RowCacheSize

RowCacheSize is not used by DBD::ODBC.

unixODBC connection pooling

The unixODBC Driver Manager provides a connection pooling mechanism. Connection pooling can speed up connections in an application that runs continuously but closes and reopens the same connection.

Connection pooling is a mechanism where when a connection is closed the ODBC Driver Manager does not actually close the connection to the ODBC driver but keeps it open in the hope the next SQLConnect or SQLDriverConnect call can reuse it. The Driver Manager stores the connection attributes used in the first connection and if the application attempts to open a previously closed connection with the same attributes the Driver Manager simply returns the saved pooled connection. Note that:

1. Connection pooling is not enabled by default. You need to add settings to the unixODBC odbcinst.ini file to use it.
2. Connections are only pooled in the same process; they are are not shared between different processes. If you're running a single application that runs continuously and that opens and closes the same ODBC connection frequently, pooling can reduce connection times considerably. If the application runs, opens a connection, does something then exits, the pooled connection is lost and is not available the next time you start the application or another application using the same same ODBC data source.
3. Once connection pooling is enabled, all calls to SQLDisconnect will not actually result in a SQLDisconnect call in the ODBC driver so whilst the process is still running the different connections stay open. This obviously increases the total number of open connections at any one time (and therefore has an impact on database resources).

   The prime example of this is a web server, which creates subprocesses to handle HTTP requests (like Apache does when not running in a threaded model). Say you had some Perl or PHP running as CGI under Apache running in a non-threaded model. When a web browser asks for the URL handled by the Perl or PHP CGI module, the Apache web server hands off the request to one of its subprocesses. (Apache generally attempts to keep a number of subprocesses free to handle bursts in requests.) When the CGI completes and calls SQLDisconnect, the Driver Manager holds onto the ODBC connection to pool it. The next request for the same URL comes in and Apache may hand it off to a different subprocess, which again opens the ODBC connection and when it disconnects and exits you now have two pooled connections in two separate Apache subprocesses. As you can't control which subprocess Apache hands off the URL requests to, eventually you end up with quite a number of open ODBC connections. At this point, you are getting no benefit from pooled connections, but at some stage, Apache will hand the URL request off to the same subprocess that has closed the connection previously and you will. However, Apache is often configured in a multi-process model where each subprocess handles at most N requests then is killed off. When the Apache subprocess is killed off, you are relying on the endpoint recognising this (for example, a socket being closed) and tidying up (not all databases and ODBC driver endpoints handle this very well, although all Easysoft ODBC drivers are tested to handle this properly).

4. Generally speaking, a pooled connection is only held open for a certain amount of time (CPTimeout in unixODBC). However, the timeout on a pooled connection can only be checked when the process that closed a connection (which was returned to the pool) gets back into the connection code. CPTimeout (the time a pooled connection is held open) is the minimum time a connection is held in the pool; if the application never calls SQLConnect or SQLDriverConnect after closing a connection, the pooled connection is held open until the process exits.
5. Pooled connections are only reused if the arguments to SQLConnect or SQLDriverConnect match exactly those of a closed ODBC connection. For example, if you open an ODBC connection with:

   SQLDriverConnect(...,"DSN=fred;UID=user;PWD=password;", ...)

   and then close this connection but call SQLDriverConnect again in the same process with:

   SQLDriverConnect(...,"DSN=fred;UID=another_user;PWD=another_password;", ...);
   

   the pooled connection is not returned, as different connection attributes were used.

To enable connection pooling with unixODBC, you need to add Pooling = yes to the ODBC section of the odbcinst.ini file. For example:

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

and then add CPTimout=Nseconds to the driver section in odbcinst.ini. Do this for each driver whose connections you want to pool. For example:

[OOB] 
Description = Easysoft ODBC-ODBC Bridge 
Driver      = /usr/local/easysoft/oob/client/libesoobclient.so 
Setup       = /usr/local/easysoft/oob/client/libesoobsetup.so 
FileUsage   = 1 
CPTimeout   = 120

To iilustrate the potential performance benefits of connection pooling, the following timings are for an application opening and closing the same 50 connections over a very slow connection:

Without connection pooling: 75s in total, 1.5s per connection
With connection pooling: 0.9s in total, 0.19s per connection

Miscellaneous issues

Data source not found and no default driver

There are legitimate reasons for this error, such as specifying a DSN that does not exist or no DSN at all and not having a default DSN, but you can also get this error when you least expect it, because of the mechanism DBD::ODBC uses to connect.

If the connection string is longer than 32 characters or contains Driver, DSN, or FILEDSN attributes, DBD::ODBC calls SQLDriverConnect first. If the call to SQLDriverConnect fails, DBD::ODBC silently ignores this, not reporting the diagnostics and has another go with SQLConnect. As a result, you might get "data source not found and no default driver" messages from SQLConnect if you make a mistake in the connection string that you meant to be passed to SQLDriverConnect.

For example, suppose you use FILEDSN=file and file contains all the attributes other than PWD and your database needs authentication. You use dbi:ODBC:FILEDSN=file;, the Driver Manager loads your ODBC driver and passes all the attributes to the driver, which fails to authenticate because PWD is missing. The ODBC driver returns an error from SQLDriverConnect and a diagnostic saying you failed authentication. DBD::ODBC ignores this and attempts to call SQLConnect('FILEDSN=file') which fails to find a DSN called FILEDSN=file and returns the "data source not found error."

Disconnecting without committing

If your script terminates with:

Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

you have turned off autocommit and not committed your database changes. For example:

use strict;
use DBI;
my $dbh = DBI->connect('dbi:ODBC:test', db_user', db_password', {autoCommit => 0});
my $sql = q/insert into table values ('hello')/;
my $sth = $dbh->prepare($sql); $sth->execute();

You need to commit the insert ($dbh->commit) or roll it back ($dbh->rollback) before disconnecting. If you don't commit the transaction, DBI rolls it back.