DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection
Last Changed $Date: 2005-04-25 10:10:17 +0100 (Mon, 25 Apr 2005) $ Publisher: Easysoft Limited Copyright © 1993-2005 by Easysoft Limited. All rights reserved. You may not reverse engineer, decompile or disassemble this document. Information in this document is subject to change without notice. Companies, names, and data used in examples are fictitious unless otherwise noted. The names of companies referred to herein, their corporate logos, the names of their hardware and software may be trade names, trademarks or registered trademarks of their respective owners. Easysoft and the Easysoft logo are registered trademarks of Easysoft Limited.
This is part 1 of a series of Easysoft tutorials on using Perl DBI with DBD::ODBC.
We used Perl 5.8 but you only need the minimum required by the DBI and DBD::ODBC modules which is currently 5.6. Use perl --version to see what version of Perl you have installed.
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.
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 Applications and Interfaces: 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.
Unsurprisingly you will need an ODBC driver for most of this tutorial and we recommend you use an ODBC driver manager under DBD::ODBC (see Applications and Interfaces: Perl DBI and DBD::ODBC). Easysoft can supply ODBC drivers for many databases and operating systems and all ODBC drivers come with the unixODBC driver manager.
You probably have the unixODBC driver manager installed if you have the odbcinst command (for ODBC drivers from Easysoft, the unixODBC driver manager is located in /usr/local/easysoft/unixODBC and the odbcinst command in the bin sub directory of that path).
We used unixODBC 2.2.9 in this tutorial. You can find out the unixODBC version you are using with:odbcinst --version
We also used the Easysoft ODBC-ODBC Bridge as the ODBC driver to access a remote MS SQL Server database from UNIX.
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 ManagerWe 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.
Perl script using DBI methods | v API | v DBI | v DBD::ODBC | v ODBC Driver Manager (e.g. unixODBC) | v ODBC Driver
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.
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.
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:
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 SERVER = demo.easysoft.com PORT = 8888 TRANSPORT = tcpip 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 ODBC-ODBC Bridge specific and define which server to connect to, login information and target DSN to use.
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".
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.
In unixODBC there a few environment variables, and settings which affect your database connections:
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.
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).
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.
These unixODBC specific data source attributes work like DMEnvAttr (above). The format is:
DMConnAttr = CONNECTION_ATTRIBUTE=value DMStmtAttr = STATEMENT_ATTRIBUTE=value
where:
e.g.
DMConnAttr = SQL_ATTR_AUTOCOMMIT=SQL_AUTOCOMMIT_OFFN.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.
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:
my $dbh = DBI->connect($ENV{DBI_DSN});
my $dbh = DBI->connect('dbi:ODBC:Test', $ENV{DBI_USER}, $ENV{DBI_PASS});
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);
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
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):
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:
ODBC defines a set of standard attributes:
For DSN-less connections you use the DRIVER attribute and concatentate all the other required attributes separated by semi-colons. e.g. Assume you had this ODBC-ODBC Bridge DSN:
[test] Driver = OOB Port = 8888 Transport = tcpip Server = demo.easysoft.com TargetDSN = pubs LogonUser = user LogonAuth = password
Instead of using a DSN you can connect to it using:
my $CONNECT = "DRIVER={OOB};Port=8888;Server=demo.easysoft.com;" . "TargetDSN=pubs;LogonUser=user;LogonAuth=password;". "Transport=tcpip;"; 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};Port=8888;Server=demo.easysoft.com;" . "TargetDSN=pubs;LogonUser=user;LogonAuth=password;". "Transport=tcpip;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 Port = 8888 Server = demo.easysoft.com TargetDSN = pubs LogonUser = user LogonAuth = password Transport = tcpip UID = dbuser
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 above output with SAVEFILE, 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 dsn's 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. Please
see Miscellaneous Issues
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
See the DBI documentation for further information on $DBI::err.
Currently, in the DBD::ODBC driver, $DBI::err will be an empty string
for successful methods and is mostly the return code from an ODBC API
call for unsuccessful methods (e.g. SQL_ERROR = -1) and hence the -1
above.
$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.
You can find a much more comprehensive description of ODBC error messages and states in the Easysoft Guide to ODBC Diagnostics & Error Status Codes.
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 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:
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:
A full description of transactions is not appropriate in this document and may be provided in future tutorials.
RowCacheSize is not used by DBD::ODBC.
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:
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 atempt 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).
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 = Yesand 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 connecttion With connection pooling: 0.9s in total, 0.19s per connection
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".
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.