Drivers, Data Sources and Connection - Perl DBI/DBD::ODBC Tutorial Part 1

Introduction
Pre-requisites
Assumptions
DBI and ODBC drivers
- DBI, DBD::ODBC architecture
- What DBI drivers have I got?
- What ODBC drivers have I got?
ODBC Data Sources
- What is a data source name (DSN)?
- What ODBC data sources have I got?
- How is the DSN used with DBI?
- The unixODBC environment
Database Connection
- Simple Connect/Disconnect
- Connection with database authentication
- Connecting using ODBC connection syntax
- Obtaining the ODBC out connection string
- Connection Errors
- Connection Warning and Informational Messages
- DBI Connection attributes
- unixODBC Connection Pooling
Miscellaneous Issues
- Data Source not found and no default driver
- Disconnecting without committing

Introduction

Pre-requisites

Before you start part 1 of this tutorial you need to ensure you satisfy all the pre-requisites:

Perl We used Perl 5.8 but you only need the minimum required by the DBI and DBD::ODBC modules which is currently 5.8.1. Use perl --version to see what version of Perl you have installed.
DBI module We used DBI 1.45 but this tutorial should work with anything after 1.40. To see if you have a recent enough version of DBI installed run:
```
perl -e 'use DBI 1.40;'
```
If you get an error like "DBI version 1.40 required--this is only version 1.30 at -e line 1." you need to upgrade DBI.
If you get an error saying DBI cannot be found in @INC you have probably not got DBI installed.
Go to CPAN to get an up to date version of the DBI module.
DBD::ODBC We used DBD::ODBC 1.11. You can use similar methods as above to determine if DBD::ODBC is installed and to see what version you have:
To check you have the DBD::ODBC module installed:
```
perl -e 'use DBD::ODBC;'
```
If you have not got DBD::ODBC installed you should see Enabling ODBC support in Perl with Perl DBI and DBD::ODBC for instructions.
To show the DBD::ODBC version:
```
perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
```
To show all drivers DBI knows about and their versions:
```
perl -MDBI -e 'DBI-> installed_versions;'
```
Go to CPAN to get an up to date version of the DBD::ODBC module.
ODBC driver and driver manager Unsurprisingly you will need an ODBC driver for most of this tutorial and we recommend you use an ODBC driver manager under DBD::ODBC

Assumptions

Operating System
This tutorial was designed on UNIX and we have assumed you are using UNIX too. However, all the Perl examples should work equally well on Windows so long as minor alterations for the command line are made.
ODBC Driver Manager
We have assumed you are using the unixODBC driver manager. all discussion in this document relating to the location and definition of ODBC data sources is for unixODBC.

DBI and ODBC drivers

DBI, DBD::ODBC architecture

Perl script using DBI methods 
            |
            v
           API 
            |
            v
           DBI
            |
            v
        DBD::ODBC
            |
            v
    ODBC Driver Manager (e.g. unixODBC)
            |
            v
        ODBC Driver

What DBI drivers have I got?

In DBI you can programmatically discover what DBI drivers are installed.

#!/usr/bin/perl -w
require DBI;

my @drivers = DBI->available_drivers;
print join(", ", @drivers), "\n";

and add:

my $d = join(", @drivers);
print "DBD::ODBC";
print "not" if ($d !~ /ODBC/);
print "installed\n";

to check if ODBC is installed.

What ODBC drivers have I got?

You can find out what ODBC drivers are installed under unixODBC with:

odbcinst -q -d

e.g.

$ odbcinst -q -d 
[INTERBASE]
[OOB]
[Easysoft ODBC-JDBC Gateway]
[mysql]
[PostgreSQL]
[EASYSOFT_ISAM]
[EASYSOFT_JOINENGINE]

For unixODBC, drivers are installed in the odbcinst.ini file. You can find out which odbcinst.ini file unixODBC is using with:

odbcinst -j

e.g.

$ odbcinst -j 
unixODBC 2.2.9 
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini USER
DATA SOURCES..: /home/martin/.odbc.ini

Here, /etc/odbcinst.ini defines the ODBC drivers.
As an example the Easysoft ODBC-ODBC Bridge installs the following entry into the odbcinst.ini file:

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

The "Driver" attribute here tells the driver manager which shared object to load for the ODBC driver. The Setup attribute is the name/location of the shared object which provides a dialogue allowing you to create/edit DSNs for this driver.

ODBC Data Sources

What is a data source name (DSN)?

A data source name is a logical name for a data repository or database. Any attributes that define a data source are stored under the DSN for retrieval by the driver.
There are two types of data sources; User and System. User data sources are only available to the user logged in and are usually placed in their home directory or user-specific area of the Windows registry. System data sources are stored in a system area accessible by everyone.
With unixODBC you can find where it looks for data sources using:

odbcinst -j

e.g.

$ odbcinst -j 
unixODBC 2.2.9 
DRIVERS............: /etc/odbcinst.ini 
SYSTEM DATA SOURCES: /etc/odbc.ini 
USER DATA SOURCES..: /home/martin/.odbc.ini

Here, system data sources go in /etc/odbc.ini and user data sources (for the user who executed the odbcinst command - martin) go in /home/martin/.odbc.ini.
The DSN is the key to a set of attributes in the odbc.ini file which:

tells the ODBC driver manager which ODBC driver to use (this is the DRIVER attribute).
the driver needs to connect to the database. These attributes are ODBC driver specific.

When you use DBD::ODBC in Perl and want to connect to the database you specify the name of the DSN defining the database you want to connect to.
As a small example the following DSN is installed by the Easysoft ODBC-ODBC Bridge:

[demo] 
Driver      = OOB 
Description = Easysoft ODBC-ODBC Bridge demo data source 
SERVERPORT  = demo.easysoft.com:8888
TARGETDSN   = pubs 
LOGONUSER   = demo 
LOGONAUTH   = easysoft 
TargetUser  = demo 
Targetauth  = easysoft

The "Driver" attribute tells the unixODBC driver manager which ODBC driver to use. unixODBC looks up the Driver value in the odbcinst.ini file which tells it which shared object to load for that ODBC driver. The remaining attributes are all Easysoft ODBC-ODBC Bridge specific and define which server to connect to, login information and target DSN to use.

What ODBC data sources have I got?

With unixODBC you can list all the USER and SYSTEM data sources with:

odbcinst -q -s

To list the data sources available for DBD::ODBC in Perl you use a script like this:

use strict;
use DBI;
my @dsns = DBI->data_sources('ODBC');
foreach my $d (@dsns)
{
  print "$d\n";
}

You should note the strings returned by the DBI datasources method are all in a form suitable for passing to the DBI connect method e.g. dbi:ODBC:xxx.

How is the DSN used with DBI?

When you call DBI's connect method you pass a string defining the DBD driver to use (ODBC in the case of this tutorial) and the DSN. e.g. dbi:ODBC:data_source_name. When DBI sees this it loads the DBD::ODBC driver and passes the connection string to it. DBD::ODBC parses the connection string and passes the data source name to the ODBC driver manager's SQLDriverConnect or SQLConnect APIs. The ODBC driver manager parses this string ("data_source_name" in this example) and attempts to find it in the USER then SYSTEM data source files. Once unixODBC has located the DSN, the Driver attribute in the DSN names which ODBC driver to use. The driver manager then uses the Driver name to look it up in the odbcinst.ini file and hence finds the final Driver attribute which points at the shared object which is the required ODBC driver - this is then loaded and SQLConnect/SQLDriverConnect is called in the ODBC driver.
As a convenience, if you specify a connection string missing the driver part (e.g. 'dbi::data_source_name') then DBI will substitute the value of the DBI_DRIVER ($ENV{DBI_DRIVER})) if it is defined.

The unixODBC environment

In unixODBC there a few environment variables, and settings which affect your database connections:

ODBCINI You can use this environment variable to override the initial places unixODBC looks for your data sources. If defined you should set it to the path and filename of where you have defined your data sources. e.g.
```
$ ODBCINI=/home/martin/mydsns.ini 
$ export ODBCINI
```
then place your DSN definitions in mydsns.ini.
ODBCSYSINI This environment variable may be used to override where unixODBC looks for ODBC driver definitions (odbcinst.ini) and system data sources (odbc.ini). You might use it if you had no access (permission) to /etc but you wanted to define ODBC drivers and system data sources. ODBCSYSINI should be set to a directory where unixODBC will look for the system odbcinst.ini and odbc.ini files. If not set, unixODBC looks in the directory defined on its configure line with --sysconfdir (usually /etc or /usr/local/etc).
DMEnvAttr/SQL_ATTR_UNIXODBC_ENVATTR This is a setting for the data source in the odbc.ini file. This is used to set ODBC environment attributes. The form is:
```
DMEnvAttr = ATTRIBUTE_NAME=value
```
and if VALUE might contain spaces:
```
DMEnvAttr = ATTRIBUTE_NAME={value}
```
where ATTRIBUTE_NAME is the name of an ODBC environment attribute (e.g. SQL_ATTR_CONNECTION_POOLING).
unixODBC defines a new environment attribute for itself called SQL_ATTR_UNIXODBC_ENVATTR. If your driver needs some environment variables defined to run (e.g. ORACLE_HOME, DB2INSTANCE) you can set them via SQL_ATTR_UNIXODBC_ENVATTR like this:
```
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={envvar=value;envar=value}
```
e.g.
```
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR= {ORACLE_HOME=/opt/OraHome}
```
which sets the ORACLE_HOME environment variable to /opt/OraHome before loading the Oracle ODBC driver.
DMConnAttr and DMStmtAttr These unixODBC specific data source attributes work like DMEnvAttr (above). The format is:
```
DMConnAttr = CONNECTION_ATTRIBUTE=value DMStmtAttr = STATEMENT_ATTRIBUTE=value
```
where:
- CONNECTION_ATTRIBUTE is the name of an ODBC connection attribute (e.g. SQL_ATTR_CONNECTION_TIMEOUT).
- STATEMENT_ATTRIBUTE is the name of an ODBC statement attribute (e.g. SQL_ATTR_NOSCAN).
- "value" is the value you want to set the attribute to. e.g. SQL_ATTR_CONNECTION_TIMEOUT=30 or SQL_ATTR_NOSCAN=SQL_NOSCAN_OFF.
e.g.
```
DMConnAttr = SQL_ATTR_AUTOCOMMIT=SQL_AUTOCOMMIT_OFF
```
N.B.
If you prefix the attribute name with a '*' then this fixes the value of that attribute i.e. in any attempt by the application to set that attribute the value specified by the application will be ignored and unixODBC will replace the value with that specified in the DMxxxAttr.

Database Connection

Simple Connect/Disconnect

Assuming you have installed your ODBC driver under unixODBC (entry in unixODBC's odbcinst.ini file) and created a SYSTEM DSN in the odbc.ini file called TEST a simple connect script looks like this:

#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Test');

Look back at "How is the DSN used with DBI?" for the composition of the string argument to connect.
The DBI connect method establishes a database connection to the requested data source and returns a database handle object or undef if the connection fails.
It is important to note here:

The DBI connect method can actually be called with no arguments at all in which case it looks for an environment variable called DBI_DSN for the connect string as if you had used:
```
my $dbh = DBI-> connect($ENV{DBI_DSN});
```
This example omits the optional second and third arguments which are the database username and password. If you database does not require a database username and password they can be omitted. Alternatively, like above, DBI will fetch the database username and password from the environment variables DBI_USER and DBI_PASS as if you had used:
```
my $dbh = DBI-> connect('dbi:ODBC:Test', $ENV{DBI_USER}, $ENV{DBI_PASS});
```
The DBI connect method has an optional fourth argument of a anonymous reference to a hash which defines the DBI attributes for the connection. If this is omitted these attributes are defaulted e.g. AutoCommit and PrintError default to on (see DBI Connection attributes).
If you use a DBI driver which does not exist e.g. 'dbi:NODRIVER:Test' or you have not installed yet, you will get an error indicating install_driver(NODRIVER) failed because DBD/NODRIVER.pm cannot be found in @INC.
In this case DBD::ODBC calls the ODBC API SQLConnect and if DBI_USER/DBI_PASS are not defined the ODBC API SQLConnect will be called with a NULL database username and password.

DBD::ODBC supports the ODBC connection string syntax:

Connection String	Description
`dbi:ODBC:DSN=test`	Use the attribute values defined in the test DSN to connect to the database. (DBD::ODBC also lets you use the DSN name without the usual ODBC connection string prefix of "DSN=", so 'dbi:ODBC:test' is therefore equivalent to 'dbi:ODBC:DSN=test').
`dbi:ODBC:DSN=test;UID=myusername;PWD=mypassword;`	Use attribute values defined in the test DSN and the connection string to connect to the database.
`dbi:ODBC:DRIVER={Easysoft ODBC-SQL Server};Server=myhost;UID=myusername;PWD=mypassword;`	Use a DSN-less connection string to connect to the database.

For example:

# This SQL Server-specific ODBC connection string:
# * Specifies that SQL Server authentication should be used to validate the connection.
# * Enables multiple active result sets (MARS) on the connection.
# * Requests that data should be encrypted before transmission and disables SSL authentication.
# * Specifies a mirror database server to connect to. (This is used if the initial connection to the principal database server fails).
# * Identifies the client application to SQL Server.
my $dbh = DBI-> connect('dbi:ODBC:DSN=Test;UID=mssqluser;PWD=mssqlpassword;Trusted_Connection=No;MARS_Connection=Yes;
Encrypt=Yes;TrustServerCertificate=Yes;Failover_Partner=123.34.45.57:4724;APP=MyApp');

Assuming the DBI connect method returns a database handle object you disconnect from the database with the DBI disconnect method:

use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Test');
$dbh->disconnect if ($dbh);

Connection with database authentication

If your database engine needs to authenticate you then you can pass the database username and password in the second and third arguments to the DBI connect method like this:

use strict;
use DBI;
my $dbh = DBI->connect('dbi:ODBC:Test', 'dbuser', 'dbauth');
$dbh->disconnect if ($dbh);

For DBD::ODBC this actually results in a call to the ODBC API SQLConnect as:

SQLConnect("Test", SQL_NTS, "dbuser", 6, "dbauth", 6);

If authentication fails you are likely to get the ODBC state 28000 but the error message text is ODBC driver specific e.g. with MS SQL Server an example is:

DBI connect('Test','dbuser',...) failed: 
[unixODBC][][Microsoft][ODBC SQL Server Driver][SQL Server] 
Login failed for user 'dbuser'. 
(SQL-28000)(DBD: db_login/SQLConnect err=-1) at ./ex5.pl line 5

Connecting using ODBC connection syntax

DBD::ODBC used to be an ODBC 2.0 application but it now can use ODBC 3.0 functionality. One example of this is in the connect method. DBD::ODBC normally calls the ODBC API SQLConnect but it will call the newer (and more flexible) ODBC API, SQLDriverConnect if the connection string (the part after 'dbi:ODBC:' in DBI->connect):

is longer than SQL_MAX_DSN_LENGTH (32) characters. This is because SQLConnect has a maximum of 32 characters for the DSN name you pass to it whereas SQLDriverConnect does not have any such limit.
contains DSN=XXX
contains DRIVER={XXX}
contains "UID=xxx" or "PWD=xxx"

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

you can pass more attributes to the ODBC driver than just a DSN name, a database username and password.
instead of putting valuable usernames 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.
you can use DSN-less connections - virtually the same as 2.

ODBC defines a set of standard attributes:

DSN=xxx - name the data source name other attributes can be retrieved from.
DRIVER={xxx} - name the driver to be loaded. You cannot use this at the same time as DSN or FILEDSN.
FILEDSN=/path/file - name 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 username for database authentication.
PWD=db_password - 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

[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 connect 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 look like this:

[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 to its defaults for USER and SYSTEM DSNs you 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.
N.B.
There is a small gotcha using file dsns created with SAVEFILE as the driver manager will remove PWD (database password). You will need to add the PWD=xxx to the end of the connection string.

Connecting using ODBC connection syntax

Obtaining the ODBC out connection string

When the ODBC API SQLDriverConnect is used to connect to a data source, a string is returned which you can use in subsequent connection requests. The driver may add additional attributes to your connection string. (In a Windows environment, the driver may even display a dialogue to allow the user to fill in fields not specified in the connection string, although this does not apply to DBD::ODBC, which uses SQL_DRIVER_NOPROMPT.) E.g.,

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, e.g., Microsoft Access does this when you link a table.

Connection Errors

The different aspects of warnings, errors and error handling deserves a tutorial in its own right so this section is just a brief introduction.
If the DBI connect method fails it will return undef and by default it prints the error (PrintError => 1) (see the example below and DBI Connection attributes for how you can change this behavior).
If the DBI connect method fails $DBI::err and $DBI::errstr will be set (note, $! is not explicitly set). So, taking our simple example in Simple Connect/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 ODBC driver 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 (e.g., 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 (i.e. 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.

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, e.g., 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 i.e., 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 normally for Microsoft SQL Server will 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 (e.g.PrintError) and attributes specific to a particular handle type (e.g.autoCommit). Because this is a connection tutorial we are only interested in connection handle attributes and there are only two which effect 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

AutoCommit is on by default. If AutoCommit is on then database changes are automatically committed to the database and can not be rolled back. If AutoCommit if off then database changes are not made until they are committed (with $dbh->commit) and they can be rolled back (not made in the database) with $dbh->rollback. If AutoCommit is off and you fail to commit the transaction DBI will automatically roll it back before disconnecting.
as far as DBI is concerned there are 3 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 will issue 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 querying 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 (e.g. select, insert, update, delete) and not Data Definition Language (DDL) statements which will cause 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 (e.g. create table, drop index etc) 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.

a full description of transactions is not appropriate in this document and may be provided in future tutorials.

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 which 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/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.
Some important details of connection pooling you should note are:

Connection pooling is not enabled by default. You need to add settings to the unixODBC odbcinst.ini file to use it (see below).
Connections are only pooled in the same process; they are are not shared between different processes. i.e. if you are running a single application which runs continuously and which 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.
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 hence impacts resources in the database). 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/PHP CGI the apache web server will hand off the request to one of the subprocesses it creates to handle requests (it will generally attempt to keep a number of subprocesses free to handle bursts in requests). When the CGI completes and calls SQLDisconnect the driver manager will hold 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 cannot 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 seeing 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 (e.g. a socket being closed) and tidying up (not all databases and ODBC driver endpoints handle this very well although all Easysoft drivers are tested to handle this properly).
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 which closed a connection (which was returned to the pool) gets back into the connection code. i.e. CPTimeout (the time a pooled connection is held open) is only the minimum time a connection is held in the pool; if the application never calls SQLConnect/SQLDriverConnect after closing a connection the pooled connection is held open until the process exits.
Pooled connections are only reused if the arguments to SQLConnect or SQLDriverConnect match exactly those of a closed ODBC connection. i.e. 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 e.g.

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

and then add CPTimout=Nseconds to each driver section in the odbcinst.ini you want pooled e.g.

[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

as an example the following timings are for an applications opening and closing the same 50 connections over a very slow modem 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 which 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 used in the connect method 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, if you can see "data source not found and no default driver" messages from SQLConnect because you made a mistake in the connection string you meant to be passed to SQLDriverConnect. e.g. With reference to FILEDSNs in Connecting using ODBC connection syntax. Suppose you use FILEDSN=file and the file contains all the attributes other than PWD and your database needs authentication. You use 'dbi:ODBC:FILEDSN=file;', the driver manager loads your driver and passes all the attributes to the driver which fails to authenticate due to the missing PWD. The driver will return an error from SQLDriverConnect and a diagnostic saying you failed authentication. DBD::ODBC will ignore this and attempt to call SQLConnect('FILEDSN=file') which will fail to find a DSN called "FILEDSN=file" and return 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 auto commit and not committed your database changes. e.g.

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 will roll it back.

Laddu Jo Sono Putra

Tuesday, September 2, 2014

Drivers,Data Sources and Connection

Drivers, Data Sources and Connection - Perl DBI/DBD::ODBC Tutorial Part 1

Contents

Introduction

Pre-requisites

Assumptions

DBI and ODBC drivers

DBI, DBD::ODBC architecture

What DBI drivers have I got?

What ODBC drivers have I got?

ODBC Data Sources

What is a data source name (DSN)?

What ODBC data sources have I got?

How is the DSN used with DBI?

The unixODBC environment

Database Connection

Simple Connect/Disconnect

Connection with database authentication

Connecting using ODBC connection syntax

Connecting using ODBC connection syntax

Obtaining the ODBC out connection string

Connection Errors

Connection Warning and Informational Messages

DBI Connection attributes

AutoCommit

RowCacheSize

unixODBC Connection Pooling

Miscellaneous Issues

Data Source not found and no default driver

Disconnecting without committing

No comments:

Easy Way to Handle Android Notifications

Translate

Total Pageviews