database connectivity

Accessing SQL Server and Other Databases from SFU 3.5

Microsoft Windows Services For UNIX 3.5 has no built-in libraries or interfaces for accessing relational databases stored on other platforms. Nor is it possible to access a Microsoft SQL Server database on the same system, running in the Windows subsystem.

However, there are several packages that can ease this problem. Our Database Tools area contains recent builds of the following (along with source files and a Readme.Interix with each source file):

• iODBC - another popular Open Source ODBC driver manager.  To download, visit the Tool Warehouse page.  This page now requires a login name and password.  If you are not already a member of our UNIX Tools Community, you can join here.

• unixODBC - a popular Open Source ODBC driver manager.  To download, visit the Tool Warehouse page.  This also serves as the ODBC-ODBC bridge client from Easysoft, allowing access to any ODBC driver in Windows. See Easysoft ODBC-ODBC Bridge.

• FreeTDS - an Open Source implementation of the Tabular Data Stream protocol used to access SQL Server and Sybase databases, including dblib, ctlib and an ODBC driver. To download, visit the Tool Warehouse page.

• Perl - the latest stable release (5.8.3) of the most popular scripting language, complete with the DBI database interface module, DBD::ODBC for connecting via ODBC, and DBD::Sybase to connect to any Sybase or SQL Server using FreeTDS.  To download, visit the Tool Warehouse page.

All of these applications and libraries are built with support for threads enabled by default.

Note: Special thanks to our database consulting partner, MSB Associates, for porting these tools, and thanks to Microsoft for providing the funding.  This is a new page within our UNIX Tools Community so in keeping with the "community" spirit please post your comments, questions, examples and bugs in the new area of the UNIX Tools Forum.  If you require one-on-one assistance, please refer to our Services page for our telephone and email support packages.

ODBC Connectivity

Two ODBC driver managers are included in the database tools area: unixODBC and iODBC. These are both equivalent, and can basically be interchanged. However, where other tools in the Interop DB tools warehouse are linked against an ODBC driver manager, it is the unixODBC manager that is used in the binary package.

Both driver managers can be installed simultaneously ... one will be in /usr/local/unixODBC, the other in /usr/local/iodbc.

When building applications, ensure that LD_LIBRARY_PATH includes the preferred manager (i.e. ensure that it includes either /usr/local/unixODBC/lib or /usr/local/iodbc/lib). Also, pass the relevant include directory to the build process (e.g. -I/usr/local/unixODBC/include).

Almost all current Open Source packages that use ODBC include a switch to select the relevant driver manager at build time, e.g. CFLAGS=-D_ALL_SOURCE ./configure --with-unixodbc=/usr/local/unixODBC

The iODBC Driver Manager

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. ]

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:

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