Omnis Technical Note TNSQ0036 August 2018

Connecting Natively to Microsoft SQL Server

for Omnis Studio 10.0 or above
by Gary Ashford, Omnis Engineering

Introduction
The Sybase DAM in Studio 10 has been modified to work with an open-source version of the LIBCT client library, developed by FreeTDS. Primarily intended for use on the macOS platform, the FreeTDS client library makes connection to Sybase ASE and Sybase ASA servers possible in the absence of Sybase Open Client which is no longer available for macOS.
By exploiting their common heritage, the FreeTDS LIBCT library also makes it possible to connect natively to Microsoft SQLServer; a connection which has previously been possible only using the ODBC or JDBC DAMs.

Downloading and compiling the FreeTDS source code (optional)
The FreeTDS project is provided under the GNU LGPL license and as such, may be freely modified and distributed by companies and individuals provided that the license terms are met. You can download the "latest stable release" directly from www.freetds.org to produce your own libct library (named ct.dll on Windows, libct.dylib on macOS or libct.so on Linux). Or you can download the pre-compiled libraries at the end of this technote. Please note that we have made one or two modifications to the default source code in order to resolve some compatibility issues and these are documented in the readme file below. To configure and build the FreeTDS libraries on your build machine, please consult the FreeTDS website and the build instructions supplied with the source code. In short however, building the Free TDS libraries is achieved by typing:

./configure
./make

and the resulting libct library (and symlink) are generated inside the /src/ctlib/.libs folder, e.g.

libct.dylib -> libct.4.dylib
libct.4.dylib (377676 bytes)

Installing the FreeTDS components
The libct file(s) should be placed in a location on your machine's standard library search path so that the dynamic loader can find it when the DAM tries to load the client library. On the Mac, we recommend copying the libct.x.dylib file and its symlink into /Users/localUser/lib, creating this folder if necessary. On Linux, the file(s) can also be placed in a location specified by the $LD_LIBRARY_PATH environment variable. When starting Omnis using the ./omnisX64 shell script, this means that the libct files can be placed in the same folder as the Omnis executable. Likewise on Windows, the ct.dll can be placed inside the Omnis folder at the same level as omnis.exe.

Other files required by FreeTDS are:

  • .freetds.conf file (equivalent to the Sybase interfaces file)
    On the Mac, .freetds.conf should be placed inside the /Users/localUser folder. Because .freetds.conf will be a hidden file on the Mac, you may wish to create an additional symlink for this file, linking freetds.conf → .freetds.conf.

    LIBCT also looks for the freetds.conf file in a file named by the environment variable FREETDSCONF. On Linux, this can also be set/exported inside the omnisX64 batch script. Windows example:

    FREETDSCONF=c:\freetds.conf

    .freetds.conf is optional if you require only default SQL Server options. See below.

  • locales.conf file (equivalent to the Sybase locales.dat file)
    On Mac and Linux, locales.conf should be placed inside the /usr/local/etc folder (a location specified by the FreeTDS source code). The Windows LIBCT library also looks for locales.conf in the root of drive C: , i.e. C:\locales.conf.

    locales.conf is optional if you only require the default "en_US" locale.

Installation Summary

libct (& symlink).freetds.conf (& symlink) - optionallocales.conf - optional
Windows{Omnis folder}\ct.dll%FREETDSCONF%C:\locales.conf
macOS˜/lib/libct.dylib → libct.4.dylib˜/freetds.conf → .freetds.conf/usr/local/etc/locales.conf
Linux{Omnis folder}/libct.so → libct.4.so$FREETDSCONF/usr/local/etc/locales.conf

Default freetds.conf and locales.conf files are supplied with the FreeTDS source bundle, and are also available for download at the end of this technote.

Configuring Data Sources
You can configure one or more data sources by editing the .freetds.conf file. To connect to Microsoft SQL Server, create or adapt a data source that looks similar to:

# SQL Server 12 Example
[ntms2012]
host = 192.168.1.99
port = 1433
tds version = 7.0

Other connection attributes may be specified and these are listed in the FreeTDS documentation. If you do not require named data sources and are using default settings for port, etc. it is also possible to logon without a freetds.conf file, using the IP-address as the the logon $hostname instead.

Configuring the Locale
The default locales.conf file supports 4 locales, although more can be created if required. Their purpose is to associate a language name, character set and datetime format with your client connection(s). The (modified) LIBCT library uses the LANG environment variable to specify the locale name. On macOS, you can use the Omnis.app/Contents/MacOS/xcomp/ini/sybasedam.ini file to set the value of LANG. (Note that the default SYBASE and SYBASE_OCS entries in this file can be ignored/removed.) Example sybasedam.ini contents:

LANG=en_US

On Windows, this can be set as a system environment variable. On Linux, it can be set/exported inside the omnisX64 batch script.

Logging on to SQL Server
Once the FreeTDS client library has been installed, your .freetds.conf & locales.conf files have been setup, and your LANG and FREETDSCONF environment variables are set, you are ready to open Omnis Studio and connect to SQL Server. Specify the data source name (if you are using .freetds.conf) or the server's IP address as the hostname. Note that connecting using the system administrator account (sa) avoids potential problems with qualified table names and permissions issues.
Hint: Checking Options->Include System Objects ensures that you will see tables belonging to all users.

Downloads

Pre-compiled FreeTDS libct library Win64
Win32
macOS Linux
Sample freetds.conf filefreetds.conf
Sample locales.conf fileslocales.conf
Source code modificationsreadme.txt

References
FreeTDS website: www.freetds.org
GNU Lesser General Public License: Go to page

See also

How to create symbolic links on macOS and Linux: Go to page
How to set environment variables on Windows: Go to page

 

Search Omnis Developer

 

Hit enter to search

X