The iODBC driver manager controls access to ODBC drivers. Two configuration files are used: odbcinst.ini, which describes the drivers (e.g. the FreeTDS ODBC driver, or the ODBC-ODBC bridge client driver), and odbc.ini, which describes the data sources (the actual database instances you want to connect to).

Details are in the detailed overview below.

Further information is available at www.iodbc.org.

The unixODBC Driver Manager

The unixODBC driver is very similar to the iODBC driver manager above, and is the driver manager used in the binary builds of all the SFU 3.5 database tools.  It is also the client for Easysoft's ODBC-ODBC Bridge.  With the ODBC-ODBC Bridge you can access any database which has a Windows ODBC driver. One license of Easysoft’s ODBC-ODBC Bridge, which incorporates the unixODBC Driver Manager, gives SFU/Interix users a universal ODBC solution.

As an example, say the SFU / Interix application needs access to a remote Oracle database on a UNIX or Linux box. Oracle does not provide OCI support for Interix. However, there are Oracle drivers available for Windows so any Windows machine can act as the client and the gateway. Easysoft’s ODBC-ODBC Bridge Server (a commercial product) is installed on the gateway and the ODBC-ODBC Client (unixODBC) on the end user’s machine. In fact, the client and the gateway can be the same physical server. When you need to access another database (say MS SQL Server, MS Access, Excel) you simply configure the appropriate datasource on the gateway. No action is required on the UNIX system.  See diagram and pricing.

The format of the odbc.ini file is almost identical to that of the iODBC driver manager. Details of these configuration files is given below.

Although unixODBC contains a graphical administrator tool, this is not built by default, as it requires libgtk in order to operate.  (GTK+ is available in the Tool Warehouse.)

Further information is available at www.unixodbc.org and www.easysoft.com.

FreeTDS

The Tabular Data Stream (TDS) protocol is used to communicate with either Sybase or Microsoft SQL Server databases.

FreeTDS is an Open Source implementation of this protocol, and contains several Application Program Interfaces that use it.

dblib

An implementation of Microsoft's db-lib API. While the core functions are reasonably complete and work well, the cursor additional APIs have not yet been implemented.  [We are considering a project to build the cursor library for dblib.  Let us know if this is important to you. tools@interopsystems.com]

ctlib

An implementation of Sybase's Open Client library. Although this library was originally designed for Sybase, it works well with SQL Server.

odbc

An ODBC driver that can operate under either unixODBC or iODBC to communicate with either a Sybase or SQL Server database.

There are several different versions of the TDS protocol, and when setting up the configuration file it is important to understand these differences:

Version	Server
8.0	Microsoft SQL Server 2000
7.0	Microsoft SQL Server 7.0
5.0	Sybase Adaptive Server Anywhere 12.0 and 12.5
4.2	Ancient versions of either SQL Server or Sybase

The configuration file /usr/local/etc/freetds.conf should contain entries for your databases. Use the entries there as templates for each server type.

Further information is available at www.freetds.org.

Perl

Perl is especially complex to build correctly under SFU 3.5. There are several pitfalls an unwary or inexperienced developer can fall into.

The best approach is to use the config.sh and Policy.sh supplied files and edit these as appropriate. Then run $ ./Configure -der

to rebuild the makefiles etc. Then gmake to build a new binary.

Almost all the tests run by gmake test now run correctly. However, there are a number of special cases for SFU (for example, the privileged user does not have UID 1 in SFU).

In a very few cases, gmake test (or prove -b -v t/*.t) will hang after all the tests have completed successfully. It is believed that this is an issue with end-of-file signaling on pipes in Interix, and is still under investigation.

FreeTDS Configuration

The FreeTDS binary package installs into /usr/local/freetds. A configuration file, etc/freetds.conf describes the databases it can connect to (NOTE the relationship between the name in this file and that in the ODBC configuration files; the ODBC file Servername describes an entry in this file). FreeTDS supports a number of different ways of setting up the configuration files; the method described here is strongly recommended.  It is known as the "ODBC-Combined" method. It is described in more detail at http://www.freetds.org/userguide/odbcombo.htm. The configuration file can be overridden with the environment variable FREETDSCONF.

A sample configuration file:

    #
    # The freetds.conf file is a replacement for the original interfaces
    # file developed by Sybase.  You may use either this or the interfaces
    # file, but not both.
    #
    # FreeTDS will search for a conf file in the following order:
    #
    #     1) check if a file was set programatically via dbsetifile() and
    #        is in .conf format, if so use that,
    #
    #     2) look in ~/.freetds.conf
    #
    #     3) look in @sysconfdir@/freetds.conf
    #
    # If FreeTDS has found no suitable conf file it will then search for
    # an an interfaces file in the following order:
    #
    #     1) check if a file was set programatically via dbsetifile() and
    #        is in interfaces format, if so use that,
    #
    #     2) look in ~/.interfaces
    #
    #     3) look in $SYBASE/interfaces (where $SYBASE is an environment
    #        variable)
    #
    # Only hostname, port number, and protocol version can be specified
    # using the interfaces format.
    #
    # The conf file format follows a modified Samba style layout.  There
    # is a [global] section which will affect all database servers and
    # basic program behavior, and a section headed with the database
    # server's name which will have settings which override the global
    # ones.
    #

    # Global settings, any value here may be overridden by a database
    # server specific section
    [global]
	    # TDS protocol version
	    tds version = 4.2

	    initial block size = 512

	    swap broken dates = no

	    swap broken money = no

	    # Database server login method, if both server and domain
	    # logins are enabled, domain login is tried first if a domain
	    # is specified, and if that fails the server login will be used.
	    try server login = yes
	    try domain login = no

	    # The default authentication domain, can be overridden by
	    # specifying a username with a domain prefix, e.g. DOMAIN\username
    ;	nt domain = WORKGROUP

	    # If the server responds with different domain try that one?
	    cross domain login = no

	    # Whether to write a TDSDUMP file for diagnostic purposes
	    # (setting this to /tmp is insecure on a multi-user system)
    ;	dump file = /tmp/freetds.log
    ;	debug level = 10

	    # Command and connection timeouts
    ;	timeout = 10;
    ;	connect timeout = 10;
	   
    # This is a Sybase hosted database server, if you are directly on the
    # net you can use it to test.
    [JDBC]
	    host = 192.138.151.39
	    port = 4444
	    tds version = 5.0

    # A typical Microsoft SQL Server 7.0 configuration	
    ;[MyServer70]
    ;	host = ntmachine.domain.com
    ;	port = 1433
    ;	tds version = 7.0

    # A typical Microsoft SQL Server 7.0 configuration using domain logins
    ;[MyServer70]
    ;	host = ntmachine.domain.com
    ;	port = 1433
    ;	tds version = 7.0
    ;	try domain logins = yes
    ;	try server logins = no
    ;	nt domain = MYDOMAIN

    # A typical Microsoft SQL Server 6.x configuration	
    ;[MyServer65]
    ;	host = ntmachine.domain.com
    ;	port = 1433
    ;	tds version = 4.2

    [thames]
	    host = 10.88.176.148
	    port = 1433
	    tds version = 8.0

The iODBC Driver Manager Detailed Overview

Copyright (C) 1995 by Ke Jin kejin@empress.com
Copyright (C) 1996-2004 by OpenLink Software iodbc@openlinksw.com
All Rights Reserved.

Introduction

Welcome to the iODBC driver manager maintained by OpenLink Software (http://www.openlinksw.com/). This kit will provide you with everything you need in order to develop ODBC-compliant applications under Interix without having to pay royalties to other parties.

This kit consists of a number of parts:

• The iODBC driver manager. This is a complete implementation of an ODBC driver manager, released under either the GNU Library General Public License or the BSD License. We fully comply with these licenses by giving you this product in source form (as well as the binary form). You can download the latest version of the driver manager from http://www.iodbc.org/

• A simple example, iodbctest.c, which gives you a command-line interface to SQL. You can fit this to your purposes, but at the very least this is useful for verification of your ODBC installation.

You can use either part stand-alone, if you wish.

An ODBC driver is still needed to affect your connection architecture. You may build a driver with the iODBC components or obtain an ODBC driver from a commercial vendor. OpenLink Software produces cross-platform commercial drivers as well as maintaining the iODBC distribution: evaluation copies may be obtained via download from http://www.openlinksw.com/. Any ODBC-compliant driver will work with the iODBC Driver Manager.

See also the iODBC website, http://www.iodbc.org/, for more pointers to various ODBC drivers.

Installation of run-time distribution

There is both a source and binary distribution available from the Interop Systems database tools area. <<<<

If you are building from the source, first read the README.interix file.

Ensure that LD_LIBRARY_PATH includes /usr/local/iodbc/lib.

The subdirectory "interix" holds copies of the configuration files edited for compilation with gcc under SFU/Interix 3.5. Simply copy these files over the distribution version, and run

	CFLAGS=-D_ALL_SOURCE ./configure --prefix=/usr/local/iodbc
	gmake
	gmake install

Contribution of changes, patches and updates.

While not mandated by the BSD license, any patches you make to the iODBC may be contributed back into the iODBC project at your discretion. Contributions will benefit the Open Source and Data Access community as a whole. Submissions may be made at http://www.iodbc.org.

Patches that are SFU specific can be directed to tools@interopsystems.com.

iODBC driver manager platform availability

The iODBC driver manager has been ported to following Unix platforms:

OS	Version	Platform
BSDi BSD/OS	2.x	?
DEC Unix(OSF/1)	3.x - 5.x	DEC Alpha
DG/UX	5.x	Aviion
FreeBSD	2.x - 5.x	x86
HP/UX	9.x - 11.x	HP9000 s700/s800
HP/UX	9.x	HP9000 s300/s400
IBM AIX	3.x - 5.x	IBM RS6000, PowerPC
Linux ELF	1.x, 2.x	x86, Itanium, PowerPC
Mac OS X	10.x	PowerPC
Max/OS SVR4	1.x	Concurrent Maxion 9200 MP
NCR SVR4	3.x	NCR 3435
OpenVMS	6.x	DEC Alpha
SCO OpenServer	5.x	x86
SGI Irix SVR4	5.x, 6.x	IP12 MIPS, IP22 MIPS
SunOS	4.1.x	Sun Sparc
Sun Solaris	2.x	Sun Sparc, PCx86
UnixWare SVR4.2	1.x, 2.x	x86
Windows NT	4.x	x86
Microsoft SFU	3.5	x86

As the iODBC driver manager uses autoconf/automake/libtool it should be portable to most modern UNIX platforms out of the box. However if you do need to make changes to the code or the configuration files, we would appreciate you share your changes with the rest of the internet community by mailing your patches to iodbc@openlinksw.com. so we can include them for the next build.

Porting of iODBC driver manager to some non-UNIX operating systems such as Windows family(3.x, 95, NT), OS/2 and Mac Classic is supported but has not been compiled and tested recently. Of course, you need to supply a make/build file and a short LibMain for creating the iodbc.dll.

How to build iODBC driver manager:

Microsoft SFU users should follow the directions in README.interix. In particular, ensure that the environment variableCFLAGS=-D_ALL_SOURCE is set.

Mac OS X users should read the separate README.MACOSX document for more detail of porting on this platform. Otherwise:

1. Run configure to adjust to target platform

2. Run eagmake

3. Run gmake install

The configure program will examine your system for various compiler flags, system options etc. In some cases extra flags need to be added for the C compiler to work properly.

e.g. on HP systems you may need:

	$ CFLAGS="-Ae -O" ./configure --prefix=/usr/local ..........<

File Hierarchy

Note that the path of the system wide odbc.ini file is calculated as follows (based on flags to ./configure):

no --prefix	default is /etc/odbc.ini
--prefix=/usr	/etc/odbc.ini
--prefix=/xxx/yyy	/xxx/yyy/etc/odbc.ini
--sysconfdir=/xxx/yyy	/xxx/yyy/odbc.ini
--with-iodbc-inidir=/xxx/yyy	/xxx/yyy/odbc.ini

If the `--with-layout=' option is set, then the prefix and sysconfdir parameters will be changed accordingly. Currently, this parameter understands values of `gentoo', `redhat', `gnu', `debian' or `opt' (with everything going into /opt/iodbc/). If both are specified, a --prefix argument will overrule a --with-layout.

The binary package was built with

  $ ./configure --prefix=/usr/local/iodbc 
--enable-pthreads --with-iodbc-inidir=/usr/local/iodbc/etc

Further Information Sources:

• http://www.iodbc.org/ iODBC project home page. Binaries, source, documentation

• http://sourceforge.net/projects/iodbc Source, CVS tree, mailing lists, forums, bug reports

• http://www.openlinksw.com/ OpenLink Software. Free trials and support for OpenLink's ODBC drivers.

• http://www.microsoft.com/data/odbc/ Microsoft's ODBC pages.

__________________________________________________

ODBC Configuration for unixODBC and iODBC

Both the unixODBC and iODBC driver managers use two configuration files to control their operation. The first of these, odbcinst.ini describes the ODBC drivers installed on the system. It resides in the etc directory of the Driver Manager, and should be maintained by the System Administrator.

The other file, odbc.ini describes the data sources, or database instances, to which applications will be connecting. As for MS Windows, there can be two sorts of datasource, User and System.

User datasources are found in the file ~/.odbc, while system datasources are in etc/odbc.ini under the driver manager tree.

For each driver manager, the environment variable ODBCINI overrides the search for a dsn, and describes a file to be used in place of either ~/.odbc.ini or etc/odbc.ini.

It is a good idea to make symbolic links from the etc/*.ini files to the /etc directory for other programs that want to examine these files.

The relevant odbc.ini describes data sources. A data source is a section (enclosed in square parenthesis), and the attributes for a data source are given within this section. The most important attribute for each datasource is the Driver attribute. This must point to the shared library for the ODBC driver associated with the data source, or to an entry in the etc/odbcinst.ini file.

As an example, the OpenLink ODBC drivers have a number of attributes which can be set for a data source. Here is a description (with ODBC connect string tags between parenthesis):

Host

The hostname where the database resides (HOST).

ServerType

The type of server (see oplrqb.ini on the server, SVT).

ServerOptions

Server-specific extra options. See OpenLink server documentation for agents which can use this.

Database

The database to use (DATABASE).

Options

Connect options for the database (OPTIONS).

UserName

The name of the user (a password cannot be specified in the UDBCINI file, UID/PWD).

ReadOnly

A Yes/No value in order to make the connection read-only (READONLY=Y/N).

FetchBufferSize

The number of records that are transferred in a single call to the server. Default is 5; maximum is 99, minimum is 1 (FBS=value).

Protocol

The protocol to use. Leave set to ``TCP'' for this release.

Apart from these data source-specific settings, you may add a section called [Communications], which you may use to tune our driver further:

ReceiveTimeout

The time the client application will wait for the database agent to finish the request (default is 60 seconds).

BrokerTimeout

The time the client application will wait for the request broker to accept of reject a database connection (default is 30 seconds).

SendSize

RPC send buffer size. A value of 0 (the default) will cause the application to use system-dependent defaults.

ReceiveSize

RPC receive buffer size. A value of 0 (the default) will cause the application to use system-dependent defaults.

DebugFile

If set, the name of a file to which debugging output from the driver should be directed.

odbc.ini

Driver managers and drivers use odbc.ini file or connection string when establishing a data source connection. On Windows, odbc.ini is located in Windows directory.

On UNIX, both the driver managers look for the odbc.ini file in the following sequence:

1. check environment variable ODBCINI

2. check $HOME/.odbc.ini (iODBC only, not unixODBC)

3. check home in /etc/passwd and try .odbc.ini in there

4. system-wide odbc.ini (settable at configuration time)

Item 1 is the easiest as most drivers will also look at this variable.

The format of odbc.ini( or ~/.odbc.ini ) is defined as:

	odbc.ini	  ::= data_source_list

	data_source_list  ::= /* empty */
			   | data_source '\n' data_source_list

	data_source	  ::= '[' data_source_name ']' '\n' data_source_desc

	data_source_name  ::= 'default' | [A-Za-z]*[A-Za-z0-9_]*

	data_source_desc  ::= /* empty */
			   | attrib_desc '\n' data_source_desc

	addrib_desc	  ::= Attrib '=' attrib_value

	Attrib		  ::= 'Driver' | 'PID' | 'UID' | driver_def_attrib

	driver_def_attrib ::= [A-Za-z]*[A-Za-z0-9_]*

An example of an odbcinst.ini file:

    [ODBC]
    Trace		= Yes
    TraceFile	= /tmp/sql.log

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

    [TDS]
    Description	= FreeTDS ODBC Driver
    Driver	 	= /usr/local/lib/libtdsodbc.so
    FileUsage	= 1

An example of an odbc.ini file:

    ;  
    ;  odbc.ini
    ;
    [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

    [orasvr]
    Driver		= OOB
    Description	= Oracle server on MSBIT
    TARGETDSN	= orasvr
    SERVER		= localhost
    TARGETUSER	= scott
    TARGETAUTH	= tiger
    LOGONUSER	= nick
    LOGONAUTH	= xxxx
    TRANSPORT	= tcpip
    PORT		= 8888

    [sqlsvr]
    Driver		= OOB
    Description	= SQL server on THAMES
    TARGETDSN	= sqlsvr
    SERVER		= localhost
    TARGETUSER	= sa
    TARGETAUTH	= sa
    LOGONUSER	= nick
    LOGONAUTH	= xxxx
    TRANSPORT	= tcpip
    PORT		= 8888

    [thames]
    Driver	= TDS
    Description	= SQL server via FreeTDS
    Servername	= thames

Tracing

Both the unixODBC and iODBC drivers manager trace the driver's ODBC call invoked by the driver manager. Default tracing file is ./odbc.log for iODBC, or /tmp/odbc.log for unixODBC. Tracing option (i.e. on/off or optional tracing file name) can be set in odbc.ini (iODBC) or odbcinst.ini (unixODBC) file under the [ODBC] heading as:

	ODBC]
	TraceFile = optional_trace_file
	Trace = ON | On | on | 1 | OFF | Off | off | 0

For iODBC only, if optional_trace_file is stderr or stdout, i.e.

       
       TraceFile = stderr

or

	TraceFile = stdout

The tracing message will go to the terminal screen (if it is available).