---+ iODBC FAQ
%TOC%
This document is a list of Frequently Asked Questions concerning the iODBC
project. It is currently maintained by iodbc@openlinksw.com; please
feel free to send nominations for FAQ-worthy topics to this address.
---++ About iODBC.org
---+++ What does iODBC stand for?
iODBC is an acronym for "Independent Open Database Connectivity". See
[[About][About iODBC]] for more.
---+++ Under what license is iODBC available?
iODBC is dual-licensed under both the
[[http://www.opensource.org/licenses/bsd-license.php][BSD]] and
[[http://www.opensource.org/licenses/lgpl-license.php][LGPL]] licenses.
Please see the [[License][License Page]] for more details.
---+++How do I get support for iODBC
Support for iODBC is available through
several mailing-lists hosted by
SourceForge.net, where the wider community (and OpenLink employees)
contribute to the best of their ability. The previous forum has been
archived but may contain useful reference material. Commercial support
may also be contracted from OpenLink Software.
---+++What is the relationship between OpenLink Software and iODBC?
[[http://www.openlinksw.com/][OpenLink Software]] is a software
company specializing in data access, management, and integration technologies
including ODBC, JDBC, ADO.NET, OLE DB, XMLA, SQL, SPARQL, and others.
Products include
[[http://uda.openlinksw.com/][UDA, a suite of data access drivers/providers/connectors]]
and [[http://virtuoso.openlinksw.com/][Virtuoso Universal Server]] a multi-model RDBMS.
iODBC is an open-source project (released under the LGPL or BSD licenses)
hosted by OpenLink Software for the benefit of the wider community, to
implement and improve a free and Open ODBC Driver Manager component for
Linux, macOS, Unix-like, VMS, and other platforms.
iODBC copyright is co-owned by Ke Jin and OpenLink Software. OpenLink are
the sole project custodian and sponsor.
Advertising: OpenLink may benefit from the publicity of hosting iODBC.org,
with its links to various vendors of ODBC drivers for assorted platforms
and RDBMS, but we don't set out to abuse the open-source project for
commercial gain. There are plenty of free and open-source
database drivers for free databases (MySQL, PostgreSQL, Firebird, and
others). The OpenLink philosophy is to facilitate OS- and
database-independent connectivity through ODBC, to which end having an
open-source driver-manager component is advantageous.
Resources: Various OpenLink employees work on actively developing and
maintaining the authoritative CVS sources of the iODBC project,
administering the server on which the site runs, maintaining the
www.iodbc.org website, responding to posts on the public forums, etc.
Executive summary: Think nice .com hosts free .org and you'll not be far
from the truth.
---+++What happened to the Forum Archive (wwwboard)?
After a long innings, wwwboard proved too susceptible to spam and other
undesirable phenomena, and the underlying wwwboard implementation was
unpleasant to maintain, becoming corrupt with increasing frequency. As
such, it has been replaced with the mailing-lists, hosted by SourceForge.
In the process, the old wwwboard has been left in stasis; a small cleanup
operation was mounted to remove off-topic and dubious / offensive / illegal
material and links, but not extending as far as censorship: if someone said
iODBC or OpenLink suck, you'll see it represented as-stated there.
Since then, the wwwboard service has succumbed to bitrot. Old postings may be reviewed via
[[https://web.archive.org/web/20010302094845/http://195.153.164.12:80/wwwboard/wwwboard.html][archive.org]].
---++Technical
---+++What's an odbc.ini and what do I put in it?
An odbc.ini is the main configuration file in which all your DSNs and much
of the ODBC configuration parameters are stored. iODBC has a search-path
for finding such a configuration file: first, the environment variable
ODBCINI is inspected to see if it points at a suitable file, or ~/.odbc.ini
(analagous to User DSNs on Windows) then /etc/odbc.ini ("system-wide") are
fall-back locations.
The odbc.ini file comprises 3 parts: a set of ODBC options, a list of DSNs,
and then the datasource definitions themselves, thus:
[ODBC Data Sources]
PostgreSQL native localhost = PostgreSQL native driver
PostgreSQL OpenLink localhost = PostgreSQL over OpenLink multi-tier
[PostgreSQL native localhost]
Driver = /usr/lib/postgresql/lib/libodbcpsql.so
Host = localhost
Server = localhost
ServerName = localhost
Database = tim
UserName = tim
UID = tim
Port = 5432
[PostgreSQL OpenLink localhost]
Description = PostgreSQL, over openlink MT
Driver = /opt/openlink/lib/oplodbc.so
ServerType = PostgreSQL95
Host = localhost
Database = tim
Username = tim
LastUser = tim
User = tim
FetchBufferSize = 99
[ODBC]
;Trace = 1
;TraceFile = /tmp/odbctrace.log
;Debug = 1
;DebugFile = /tmp/odbcdebug.log
The list of ODBC Data Sources correlate with the file odbcinst.ini (or
environment variable ODBCINSTINI): for every value used as a description of
the driver, there should be a corresponding section in odbcinst.ini listing
both the Driver and Setup library (to assist with graphical configuration
using iodbcadm-gtk, and also used to display an input box in the event of
insufficient options being presented at connection-time).
The list of attributes is driver-dependent, as the above shows: the native
postgresql driver calls them different things from OpenLink UDA Enterprise Edition, so
you have to check your driver's documentation.
With iODBC, assuming you downloaded/installed/compiled it, you have a
GTK-based GUI for configuring your ODBC DSNs, too: run iodbcadm-gtk.
---+++Just get me connected!
This is the succinct overview:
Grab the source. Unpack it. Run
./configure && nice make && sudo make install
Install an ODBC driver for your backend database.
Configure odbc.ini to use this driver. Export the environment variable
ODBCINI to point to that odbc.ini. Run iodbctest and
attempt to connect. Voila.
---+++I've got this linux box with PHP and Apache...
...and you want to connect to SQL Server or MS Access from it. The overview
of the architecture is as follows: apache invokes php to handle scripts,
php is linked against libiodbc in order to handle the odbc_* functions in
your script, libiodbc loads either a native SQL Server driver for linux or
some generic ODBC-proxy for MS Access, and you're done.
OpenLink produce an odbc agent that facilitates the latter sort of
connection.
The [[http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/IODBCPHPHOWTO][iODBC+PHP+Apache HOWTO]]
document on this site will guide you through a
working set of configure options for libiodbc, apache, and php in order.
---+++What's a libiodbc and what goes in my Driver= lines in odbc.ini?
The ODBC architecture separates the application, the driver-manager and the
driver components, each one calling the next in a chain.
The libiodbc.so library only implements a driver manager; you need a
separate driver for each kind of backend database to which you connect, but
your application will at least see one consistent interface across all of
them. If you attempt to use Driver=libiodbc.so in the odbc.ini, it will
fail.
The Driver= parameter should be a full name to a shared-library
implementing the driver for the backend database to which you're
connecting:
[pglocal]
Driver = /usr/local/lib/psqlodbc.so
Port = 5432
ServerName = localhost
Database = me
UserName = me
Password =
[OpenLink]
Description = PostgreSQL, over openlink multi-tier
Driver = /opt/openlink/bin/oplodbc.so
ServerType = PostgreSQL95
Host = otherbox
Database = me
Username = me
FetchBufferSize = 99
More recent versions of iODBC permit use of { } for quotations and
symbolic names for the driver (referencing the odbcinst.ini file), such as
Driver = {SQL Server}
Older versions of iODBC implemented neither of these; the Driver=
parameter had to be a full path directly to the shared library.
---++Common Error Messages
---+++[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQLSTATE=IM002
There are several reasons why this message could occur. The best solution
is to trace through what's happening: your application has been linked
against libiodbc, which has tried to find an odbc.ini file one way or
another - either through the ODBCINI environment variable or the fall-back
paths (typically /etc/odbc.ini, depending on how it was compiled). You
should check that such a file exists in a suitable location, and that it is
accessible (particularly if your application runs with different user
privileges - such as apache/php running as a www-data user).
Additionally, it could be that iODBC has found a suitable odbc.ini file,
but none of the file(s) found contain the DSN you've requested. Check the
syntax of your request - is the ODBC connect-string correct, and does the
DSN you're requesting exist? See also the section of this FAQ
entitled [[#What%27s%20an%20odbc.ini%20and%20what%20do%20I%20put%20in%20it%3F][What's an odbc.ini and what do I put in it?]]
---+++[iODBC] [Driver Manager]Specified driver could not be loaded
As in the previous error case, there are a few reasons why this could
occur, and thinking through the architecture helps. Your application has
loaded libiodbc successfully, and it has found an odbc.ini file (or
equivalent through the ODBCINI environment variable), and it has found a
DSN within that odbc.ini that matches the name requested in your
connection.
However, the driver manager has had problems loading the library specified
in the Driver= line of that DSN definition. Either it doesn't exist, or
its permissions are insufficient to allow your application to load it (it
must be readable and executable, and the directories leading down to it
must be executable), or maybe the file is not a dynamic library -- it could
be a static library (a *.a file, except on AIX) -- or is otherwise corrupted.
These are all things to check, or you may be best off reinstalling the
driver if all the permissions check out.
Note also the prior point: the Driver= parameter must lead to
a shared-library (*.so) implementing an ODBC driver for the database to
which you're connecting.
---++Driver-Specific Errors
---+++MyODBC: [TCX][MyODBC]No DSN entered (0) SQLSTATE=S1000
This error is raised whenever you attempt to connect to MyODBC using the
SQLDriverConnect() function with the SQL_DRIVER_PROMPT option. Normally,
SQL_DRIVER_PROMPT means the driver should invoke a graphical dialog to
request missing information (DSN=, or username and password if not
included); however, MyODBC has no graphical library and does not
distinguish the case that a graphical prompt is unavailable from when there
is insufficient information, so you always see this error message.
iodbctest and iodbctestw from versions 3.52.1 and 3.52.2 both request
connections using SQL_DRIVER_PROMPT, causing this problem with MyODBC.
The workaround is to edit iodbctest.c such that the SQLDriverConnect() and
SQLDriverConnectW() calls use SQL_DRIVER_COMPLETE instead. This still
allows drivers with graphical setup routines to ask for additional
information, but works better with drivers that have no graphical routines,
such as MyODBC. See the following diff:
Index: iodbctest.c
===================================================================
RCS file: /opldev/opensource/iODBC/samples/iodbctest.c,v
retrieving revision 1.22
retrieving revision 1.21
diff -u -u -r1.22 -r1.21
--- iodbctest.c 2005/07/19 10:19:01 1.22
+++ iodbctest.c 2005/02/15 17:08:13 1.21
@@ -297,12 +297,12 @@
#ifdef UNICODE
strcpy_A2W (wdataSource, (char *) dataSource);
status = SQLDriverConnectW (hdbc, 0, (SQLWCHAR *) wdataSource, SQL_NTS,
- (SQLWCHAR *) outdsn, NUMTCHAR (outdsn), &buflen, SQL_DRIVER_COMPLETE);
+ (SQLWCHAR *) outdsn, NUMTCHAR (outdsn), &buflen, SQL_DRIVER_PROMPT);
if (status != SQL_SUCCESS)
ODBC_Errors ("SQLDriverConnectW");
#else
status = SQLDriverConnect (hdbc, 0, (SQLCHAR *) dataSource, SQL_NTS,
- (SQLCHAR *) outdsn, NUMTCHAR (outdsn), &buflen, SQL_DRIVER_COMPLETE);
+ (SQLCHAR *) outdsn, NUMTCHAR (outdsn), &buflen, SQL_DRIVER_PROMPT);
if (status != SQL_SUCCESS)
ODBC_Errors ("SQLDriverConnect");
#endif
This change is included in the iODBC 3.52.3 and later.
---++Segfault with Perl DBD::ODBC
Certain platforms (most notably Debian GNU/Linux 5.0.4 ("Lenny", currently
stable)) ship with version 1.13-5 of Perl's DBD::ODBC module. On 64-bit OS,
this has a bug that causes a segfault. The solution is to upgrade to at least
1.17 (may be found in "Squeeze", currently the Testing distribution).
---++Tracing Application Behavior
In the above odbc.ini snippet, we saw commented-out lines concerning
tracing. These lines would enable us to analyze what ODBC calls the driver
manager sees an application make, with details including parameters, return
states, etc.
To enable this, uncomment the lines in your odbc.ini:
[ODBC]
Trace = 1
TraceFile = /tmp/odbctrace.log
Debug = 1
DebugFile = /tmp/odbcdebug.log
In iODBC version 3.51.0 and above, the TraceFile option now understands
a handful of variables:
$P Process ID
$U User ID under which the process is currently running
$T Timestamp in YYYYMMDDHHMMSS format
$H Home directory of the user that started the process
Also, from version 3.52.7 onwards, TraceFile now takes an option
$S Sequence number
These are case-insensitive. Hence you could specify --
TraceFile=/tmp/iodbc-$U-$P-$T.log
-- and it would include your userid, the application process-id, and the timestamp
when ODBC was invoked, in the trace log filename.
---+++Tracing Application Behavior
As of release 3.52.5, iODBC has support for File DSNs - where the
connection parameters are stored in a separate file per DSN, under a
standard directory.
To configure these, either use the FileDSN tab alongside System and User
DSNs in the iODBC graphical administrator (iodbcadm-gtk or the macOS
GUI), or edit files in /etc/ODBCDataSources/ for yourself.
Note that this directory must have suitable permissions for those you wish
to be able to create, edit, and remove files to do so. For example, 1775
might be suitable - the owner and members of the group owning the directory
can create, remove, and edit their own DSNs, but not those owned by a
different user.
To use a File DSN, replace the DSN= parameter in your ODBC connection
string with FileDSN=, for example
iodbctest FileDSN=localvirtuoso;UID=dba;PWD=secret