I get a lot of queries to my personal/CPAN email address with connection
problems with DBD::ODBC. I've tried to describe the process at
http://www.martin-evans.me.uk/node/70 which you may find useful.
I know some people are a little cagey these days about self promotion of
web sites. If you are one of these, don't bother clicking on the link
above. On the other hand I'd welcome feedback and my intention is to add
this document to DBD::ODBC which would put it in the public domain
(further to the Creative Commons license my personal web site uses).
In addition I reproduce here (although it is possible it will exceed the
limits on postings on this list).
A great deal of issues arise in the dbi-users mailing list and to me
personally concerning ODBC connection issues so I thought I'd describe
the ODBC connection process here (with a slight Perl angle since I
look after DBD::ODBC). The information here should be of general
interest to anyone using ODBC.
The first thing to note is that in ODBC 3 there are two connection
APIs; SQLConnect (the first and older API) and SQLDriverConnect (the
new ODBC 3 API). DBD::ODBC uses both but more on that later.
Applications link to an ODBC Driver Manager and some of the driver
manager's responsibilities are to manage the connect APIs, locate the
ODBC Driver to load and pass information to the ODBC Driver to enable
it to connect you to a database usually as a specific user. In
addition the ODBC Driver Manager provides APIs to the ODBC Driver to
retrieve data source attributes (e.g., SQLPrivateProfileString).
First we have to cover some background information. If you think you know
this or just want to skip it scroll down to "The Connection Process".
<h2>Data Source Names (DSNs)</h2>
The key to the SQLConnect/SQLDriverConnect APIs is the Data Source
Name (DSN). A DSN is a way of defining various attributes for an ODBC
database connection under one name. Typically a DSN defines the name
of the ODBC Driver to use and other attributes like the database name,
host name, database username/password etc but in the main it only
really has to define the ODBC Driver and all the other attributes are
optional.
In Windows the DSN is stored in a registry key and ODBC Drivers in
another registry key and the ODBC Driver Manager provides the GUI ODBC
Administrator to create/edit DSNs. When ODBC Drivers are installed an
entry is added to the registry key defining ODBC Drivers and another
key defines the DLL which is used to create DSNs for that ODBC Driver.
On non-Windows platforms DSNs are stored in the odbc.ini (or .odbc.ini
file for user DSNs) and ODBC Drivers in the odbcinst.ini file and
although ODBC Driver Managers like unixODBC provide a GUI application
(and the odbcinst command) to create/edit installed ODBC Drivers and
DSNs but most people simply edit the files. Actually, unixODBC works just
like the ODBC Driver Manager in Windows in that when an ODBC Driver is
installed it can add a Setup attribute to the driver entry in the
odbcinst.ini file and then unixODBC can load that shared object to
create/edit DSNs but the GUI part of unixODBC is written in QT and
there are few drivers which provide a QT shared object for
creating/editing DSNs.
<h2>USER/SYSTEM DSNs</h2>
The first thing you need to realise is there are USER and SYSTEM DSNs.
USER DSNs are only available to the logged in user and SYSTEM DSNs are
available to anyone. In reality, in Windows this is achieved by
putting USER/SYSTEM DSNs in different registry keys and in UNIX by
putting them in different files (usually /etc/odbc.ini or
/usr/local/odbc.ini for SYSTEM DSNs and ~/.odbc.ini for USER
DSNs). This in itself is a common source of problems since people
define USER DSNs when logged in a user "fred" then run their
application (e.g., Apache, IAS etc) as user "dave" and wonder why they
get a DSN not found error.
<h2>SQLConnect vs SQLDriverConnect</h2>
The process in the driver manager is pretty much the same whether you
call SQLConnect or SQLDriverConnect (with some exceptions).
The SQLConnect API takes ServerName, UserName and Authentication
strings. Generally these are the DSN name, database username and
database password. Beyond that it is fairly inflexible and even the
ServerName argument has a maximum length which is usually 32
characters.
The SQLDriverConnect API is far more flexible in that it takes
arguments of a WindowHandle, InConnectionString, OutConnectionString,
and DriverCompletion. The InConnectionString is a ';' separated list
of attributes which may include DSN, UID (username), PWD (password),
DRIVER (driver name), FILEDSN (pointer to a file containing a DSN),
SAVEDSN (file to write the completed connection string) and any others
the ODBC Driver cares to define. The OutConnectionString returns a
completed connection string which may be used to connect to the
database again (more later). The WindowHandle allows the ODBC Driver
Manager to use the ODBC Driver Setup DLL/shared object to display a
GUI dialogue to create/edit DSNs (this is another area where unixODBC
and QT have some issues). The DriverCompletion argument allows the
application to say how it would like the ODBC Driver to behave when
the attributes passed in InConnectionString are insufficient (or not)
to connect to a specific database:
SQL_DRIVER_PROMPT
SQL_DRIVER_COMPLETE
SQL_DRIVER_COMPLETE_REQUIRED
SQL_DRIVER_NOPROMPT
(See below for the meanings of these attributes).
For the sake of this discussion I am assuming the ODBC Driver manager
maps calls to SQLConnect to SQLDriverConnect where ServerName,
Username and Authentication arguments are mapped to SQLDriverConnect's
InConnectionString as DSN/UID/PWD attributes in a ';' delimited string
and DriverCompletion is SQL_DRIVER_COMPLETE. i.e., if someone calls:
SQLConnect("mydsnname", "myusername", "mypassword")
it ends is SQLDriverConnect with an InConnectionString of:
DSN=mydsnname;UID=myusername;PWD=mypassword
and DriverCompletion of SQL_DRIVER_COMPLETE.
>From here on the discussion is SQLDriverConnect focussed.
<h2>ODBC defined connection attributes</h2>
The SQLDriverConnect API has an InConnectionString argument which is a
';' delimited string of attributes. ODBC defines some attributes:
DRIVER - the name of the ODBC Driver
There are some extra rules for defining the location and name of the DLL
or shared object for the driver which require the filespec to be enclosed in
{}.
DSN - a data source name
UID - a database username
PWD - UID's password
FILEDSN - the name of a file containing the attributes for the DSN
Few non-Windows drivers support FILEDSN although all Easysoft drivers do.
SAVEFILE - file to save completed connection string
Few non-Windows drivers support SAVEFILE although all Easysoft drivers do.
For DRIVER see "DSN-less Connections".
If a DSN is supplied it is simply a name used to lookup the other
attributes in the DSN (in the registry or odbc.ini file).
If UID/PWD are supplied they override any UID/PWD in the DSN.
FILEDSN defines the name of a file containing all the other attributes
a driver may need to connect to the database. FILEDSN is rarely used
but is useful if you want to distribute a DSN with your application.
<h2>ODBC Driver defined attributes</h2>
An ODBC Driver may define whatever attributes it requires. Often these
are attributes like "database" (database to connect to), "servername"
(server where database resides), port (port to connect to), but there
is no universal standard.
If an ODBC Driver has a GUI setup DLL/shared object to create/edit
DSNs it will allow you to enter the values for these attributes and
store them in the DSN. Otherwise, you just have to consult the ODBC
Driver documentation to find out what attributes it requires/defines.
<h2>DSN-less Connections</h2>
DSN-less connections are obviously those which do not specify a DSN.
That is possible because instead you specify the DRIVER attribute and
optionally all the other attributes the driver needs to connect to the
database (or the driver prompts for the missing attributes). The
DRIVER attribute tells the ODBC Driver Manager which driver to
load and pass the InConnectionString on to. After that it is up to the
ODBC Driver to find any other attributes it needs in the
InConnectionString or by prompting the user for additional attributes
(this depends on the DriverCompletion argument).
<h2>The DriverCompletion argument</h2>
The DriverCompletion argument may be:
SQL_DRIVER_PROMPT
The driver displays a dialog box, using the values from the connection
string and system information (if any) as initial values. When the
user exits the dialog box, the driver connects to the data source. It
also constructs a connection string from the value of the DSN or
DRIVER keyword in InConnectionString and the information returned
from the dialog box. It places this connection string in the
OutConnectionString buffer.
There are very few ODBC Drivers on non-Windows platforms that can
handle this - the Easysoft ODBC-ODBC Bridge is the only one I know of.
SQL_DRIVER_COMPLETE
SQL_DRIVER_COMPLETE_REQUIRED
If the connection string contains enough information, and that
information is correct, the driver connects to the data source and
copies InConnectionString to OutConnectionString. If any information
is missing or incorrect, the driver takes the same actions as it does
when DriverCompletion is SQL_DRIVER_PROMPT, except that if
DriverCompletion is SQL_DRIVER_COMPLETE_REQUIRED, the driver disables
the controls for any information not required to connect to the data
source.
This is the most flexible option on non-Windows platforms or on
Windows if the application is not a GUI as it tells the ODBC driver to
fill in the missing pieces as it can.
SQL_DRIVER_NOPROMPT
If the connection string contains enough information, the driver
connects to the data source and copies InConnectionString to
OutConnectionString. Otherwise, the driver returns SQL_ERROR for
SQLDriverConnect.
This is pretty inflexible in all environments as it means that if
insufficient attributes are found for a connect the SQLDriverConnect
will fail (even if it could find the needed attributes in a DSN).
<h2>DEFAULT DSNs</h2>
ODBC defines a DEFAULT DSN which will be used when no DRIVER or DSN
attribute is defined. The DEFAULT DSN is named "DEFAULT" and works
like any other DSN except the ODBC Driver Manager knows to look for it
if no Driver/DSN attributes are specified.
<h2>How do drivers "complete" a connection string?</h2>
When an ODBC Driver is handed a connection string it parses it into
separate attributes and then determines if it has sufficient
attributes to connect to the database. Depending on what you pass in
the DriverCompletion argument the driver may consult the DSN to obtain
other attributes it needs to connect, throw a dialogue to obtain the
remaining attributes or error. Attributes passed in the
InConnectionString override those in any DSN or dialogue (greyed out
in dialogues if SQL_DRIVER_COMPLETE_REQUIRED).
Assuming the ODBC Driver has insufficient attributes to connect to the
database and a DSN is named (and
SQL_DRIVER_COMPLETE|SQL_DRIVER_COMPLETE_REQUIRED is specified) it can
use the ODBC Driver Manager API SQLGetPrivateProfileString to obtain
any remaining attributes. It sets the DSN name then requests attributes
by name and the driver manager will look them up in the DSN and return
them to the driver. In this way the ODBC Driver does not need to know
where the additional attributes are held, only their name. It uses
this method to "complete" the connection string and on connection the
full OutConnectionString to reconnect to this database will be
returned (applications like MS Access use this to store the full
connection string so it does not need the driver to prompt the next
time you connect).
This works well in Windows but falls down on non-Windows platforms
where some drivers do not use the SQLGetPrivateProfileString API or
store connection information in other files (e.g., freeTDS can store
database and protocol information in the freetds.conf file). Worse,
some drivers do use SQLPrivateProfileString but also have a
driver-specific configuration file outside of ODBC and use different
names for the attributes in each and define different orders for where
they look first.
If you are working on an ODBC Driver or have input to the development
of one then I strongly suggest you encourage the use of
SQLGetPrivateProfileString and linking with an ODBC Driver Manager.
<h2>The Connection Process</h2>
If you have not read the above I hope you know your ODBC API.
This discussion assumes the application is linked with an ODBC
Driver Manager which provides SQLDriverConnect (or SQLDriverConnectW)
and SQLGetPrivateProfileString.
The application calls SQLDriverConnect with a connection string and
DriverCompletion. The connection string needs to define an attribute
which allows the driver manager to locate the ODBC Driver (DRIVER or
DSN). If the connection string names a DRIVER the driver manager
loads it. If the InConnectionString contains a DSN/FILEDSN the driver
manager looks up the DSN to find the driver and loads the driver. If
the driver cannot be found this way you end up up with an error saying
something like "Datasource not found and no DEFAULT defined" (although
see DEFAULT DSNs above).
At this stage the ODBC Driver manager needs to allocate an environment
handle (SQLAllocHandle for SQL_HANDLE_ENV), set the ODBC version
(SQLSetConnectAttr for SQL_ODBC_VERSION) and allocate a connection
handle (SQLAllocHandle for SQL_HANDLE_DBC). In particular the
SQLAllocHandle for an environment can fail at this stage if the ODBC
Driver requires an environment variable to be set (e.g., DB2INSTANCE,
ORACLE_HOME). After this the InConnectionString is passed to the ODBC
Drivers SQLDriverConnect.
>From this point on what happens is down to the ODBC Driver. It parses
the InConnectionString and extracts all attributes and values. It then
needs to ascertain if it has sufficient attributes to connect to the
database although if SQL_DRIVER_PROMPT is defined it must throw a
dialogue anyway. If it is short of attributes to connect it may look
up additional attributes via the FILEDSN/DSN attributes and
SQLPrivateProfileString so long as DriverCompletion allows. As
explained above this is where things deviate for non-Windows as some
drivers do not use SQLPrivateProfileString and/or use a separate file
to define connections to databases. At this stage and depending on
DriverCompletion the ODBC Driver will either complete the missing
attributes (see "How do drivers "complete" a connection string"
above), throw a dialogue or error.
<h2>Perl DBD::ODBC</h2>
Perl's DBD::ODBC module has a long history and attempts to be
compatible with as many ODBC Drivers as possible. This means it aims
to support ODBC 1/2 drivers and more modern ODBC 3 drivers. As a result,
any call to DBI's connect method which does not mention a DRIVER or DSN
attribute first results in a call to SQLConnect. e.g.,
DBI->connect("dbi:ODBC:fred", "uid", "pwd");
results in a call to
SQLConnect(ServerName="fred","uid","pwd)
and only if this fails does it result in:
SQLDriverConnect(InConnectionString="DSN=fred;UID=uid;PWD=pwd")
On the other hand, if the call to DBI's connect method contains a DSN
or DRIVER the call to SQLConnect is bypassed and a direct call to
SQLDriverConnect is made:
DBI->connect("dbi:ODBC:DSN=fred")
results in:
SQLDriverConnect(InConnectionString="DSN=fred");
DBI->connect("dbi:ODBC:DSN=fred;something=somethingelse")
results in:
SQLDriverConnect(InConnectionString="DSN=fred;something=esomethingelse");
DBI->connect("dbi:ODBC:DSN=fred", "uid", "pwd")
results in:
SQLDriverConnect(InConnectionString="DSN=fred;UID=uid;PWD=pwd");
DBI->connect("dbi:ODBC:DRIVER={fred};something=somethingelse")
results in
SQLDriverConnect(InConnectionString="DRIVER={fred};something=somethingelse");
and in addition UID=x and PWD=y will be added it you passed a
username/password
to DBI->Connect.
The upshot of this is that if you are passing a DSN name to
DBI->connect then prefix it with "DSN=" especially if you've added
other attributes or you could waste time calling SQLConnect with too
long a ServerName attribute to SQLConnect which will only fail then
call SQLDriverConnect (wasting time).
In addition, if the connection string passed to DBI->connect contains
unicode strings (the UTF8 flag is set) DBD::ODBC will call
SQLDriverConnectW assuming it was built for unicode (see -u argument
to perl Makefile.pl).
