Site navigation
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
The prerequisite software for this tutorial is:
We used Perl 5.8, but you only need the minimum version required by the DBI and DBD::ODBC modules, which is currently 5.6. Use perl --version to find out what version of Perl you have installed.
We used DBI 1.45 and DBI 1.607, but the samples in this tutorial should work with anything after 1.40. To find out 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 and DBD::ODBC 1.17. You can use similar methods to the ones shown above to check whether DBD::ODBC is installed and to see what version you have.
To check that you have the DBD::ODBC module installed:
perl -e 'use DBD::ODBC;'
If you have not got DBD::ODBC installed, refer to Enabling ODBC support in Perl with Perl DBI and DBD::ODBC for installation 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;'
Unsurprisingly, you'll need an ODBC driver to follow along with this tutorial. We recommend that you use an ODBC Driver Manager under DBD::ODBC. Easysoft supply ODBC drivers for many databases and operating systems. All Easysoft ODBC drivers come with the unixODBC Driver Manager.
You probably already have the unixODBC Driver Manager installed if you have the odbcinst command. (For Easysoft ODBC drivers, the unixODBC Driver Manager is located in /usr/local/easysoft/unixODBC, by default, and the odbcinst command in the bin subdirectory of that path.
We used unixODBC 2.2.9 in this tutorial. You can find out the unixODBC version you're using with:
odbcinst --version
We also used the Easysoft ODBC-ODBC Bridge as our ODBC driver. We used the ODBC-ODBC BRidge to access a remote Microsoft SQL Server database from UNIX.
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
This tutorial was designed on UNIX and Linux, and we have assumed you are using UNIX or Linux too. However, all the Perl examples should work equally well on Windows as long as some minor alterations to the command line are made.
We assume that you're using the unixODBC Driver Manager.
Perl script using DBI methods | v API | v DBI | v DBD::ODBC | v ODBC Driver Manager (for example, 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";
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
For example:
$ odbcinst -q -d [INTERBASE] [OOB] [Easysoft ODBC-JDBC Gateway] [mysql] [PostgreSQL] [EASYSOFT_ISAM] [EASYSOFT_JOINENGINE]
For unixODBC, ODBC drivers are installed in the odbcinst.ini file. You can find out which odbcinst.ini file unixODBC is using with:
odbcinst -j
For example:
$ 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 and location of the shared object that provides a dialog box allowing you to create and edit data sources for the driver.
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
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 ODBC driver.
There are two types of data sources: user and system. User data sources are only available to the logged in user and are usually placed in the user's home directory or the 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
For example:
$ 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 ran the odbcinst command, in our example, this is martin) go in /home/martin/.odbc.ini.
The DSN is the key to a set of attributes in the odbc.ini file that:
DRIVER attribute).When you use DBD::ODBC in Perl and want to connect to a database, you specify the name of the DSN that defines 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. This tells unixODBC which shared object to load for that ODBC driver. The remaining attributes are all Easysoft ODBC-ODBC Bridge specific and define the server to connect to, login information for that server, and the target DSN to use.
With unixODBC you can list all the user and system data sources with:
odbcinst -q -s
To list the available data sources 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";
}The strings returned by the DBI datasources method are all in a form suitable for passing to the DBI connect method. For example, dbi:ODBC:xxx.
When you call DBI's connect method, you pass a string defining the DBD driver to use (ODBC for this tutorial) and the DSN. For example, dbi:ODBC:my_data_source. When DBI gets 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 (my_data_source in the 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 identifies which ODBC driver to use. The Driver Manager looks up the driver name in the odbcinst.ini file and finds the final Driver attribute, which points to a shared object that is the required ODBC driver. This is then loaded and SQLConnect or SQLDriverConnect are called in the ODBC driver.
As a convenience, if you specify a connection string, but miss the driver part (for example, dbi::my_data_source) then DBI will use the DBI_DRIVER value ($ENV{DBI_DRIVER}) if it is defined.
In unixODBC there a few environment variables and settings that affect your database connections:
ODBCINIYou can use this environment variable to override the default locations unixODBC checks for your data sources. Set it to the path and file where you have defined your data sources. For example:
$ ODBCINI=/home/martin/mydsns.ini $ export ODBCINI
then place your DSN definitions in mydsns.ini.
ODBCSYSINIThis environment variable overrides the default location where unixODBC checks for ODBC driver definitions (odbcinst.ini) and system data sources (odbc.ini). You might use it if you have no access permissions to /etc. ODBCSYSINI should be set to a directory where unixODBC should look for the system odbcinst.ini and odbc.ini files. If not set, unixODBC looks in the directory defined by --sysconfdir on its configure line (usually /etc or /usr/local/etc).
DMEnvAttr and SQL_ATTR_UNIXODBC_ENVATTRThis 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 (for example, SQL_ATTR_CONNECTION_POOLING).
unixODBC defines a new environment attribute for itself called SQL_ATTR_UNIXODBC_ENVATTR. If your ODBC driver needs some environment variables defined to run (for example, ORACLE_HOME or DB2INSTANCE) you can set them with SQL_ATTR_UNIXODBC_ENVATTR:
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={envvar=value;envar=value}
For example:
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR= {ORACLE_HOME=/opt/OraHome}
sets the ORACLE_HOME environment variable to /opt/OraHome before loading the Oracle ODBC driver.
DMConnAttr and DMStmtAttrThese unixODBC specific data source attributes work like DMEnvAttr. The format is:
DMConnAttr = CONNECTION_ATTRIBUTE=value DMStmtAttr = STATEMENT_ATTRIBUTE=value
where:
CONNECTION_ATTRIBUTE is the name of an ODBC connection attribute (for example, SQL_ATTR_CONNECTION_TIMEOUT).STATEMENT_ATTRIBUTE is the name of an ODBC statement attribute (for example, SQL_ATTR_NOSCAN).value is the value you want to set the attribute to. For example, SQL_ATTR_CONNECTION_TIMEOUT=30 or SQL_ATTR_NOSCAN=SQL_NOSCAN_OFF.For example:
DMConnAttr = SQL_ATTR_AUTOCOMMIT=SQL_AUTOCOMMIT_OFF
Note If you prefix the attribute name with a *, this fixes the value of that attribute. Any attempt by the application to set that attribute is ignored and unixODBC uses the value specified with DMxxxAttr.
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.
Assuming you have installed your ODBC driver under unixODBC (that is, there's an entry in unixODBC's odbcinst.ini file) and created a system DSN in the odbc.ini file called TEST, a simple connect script would be:
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Test');Refer to How is the DSN used with DBI? for the composition of the connection string argument.
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's important to note here that:
connect method can actually be called with no arguments at all, in which case it checks for an environment variable called DBI_DSN for the connection string, as if you had used:
my $dbh = DBI-> connect($ENV{DBI_DSN});DBI_USER and DBI_PASS as if you had used:
my $dbh = DBI-> connect('dbi:ODBC:Test', $ENV{DBI_USER}, $ENV{DBI_PASS});AutoCommit and PrintError default to ON (refer to DBI Connection attributes).dbi:NODRIVER:Test, or one you have not installed yet, you'll get an error indicating install_driver(NODRIVER) failed because DBD/NODRIVER.pm cannot be found in @INC.SQLConnect and if DBI_USER or DBI_PASS are not defined, the ODBC API SQLConnect with a NULL database user name and password.| 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 turns off 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);If your database engine needs to authenticate, you can pass the database user name 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're likely to get the ODBC state 28000, but the error message text is ODBC driver specific For example, with the Microsoft SQL Server ODBC driver:
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):
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.DSN=XXXDRIVER={XXX}UID=xxx or PWD=xxxSo, 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:
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.
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.
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::errstrThe 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::stateThe 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.
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)
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:
my $dbh = DBI->connect('dbi:ODBC:test', 'dbuser', 'dbpassword', {attribute => value});$dbh->{attribute} = valueAutoCommitIf 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:
AutoCommit off for these databases is an error and using commit and rollback methods generates a warning.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.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.RowCacheSizeRowCacheSize is not used by DBD::ODBC.
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:
odbcinst.ini file to use it.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).
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.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
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."
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.
Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase, DB2, & more.