Omnis Technical Note TNSQ0036 Updated September 2021

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

Optional OpenSSL Support
You can additionally compile the libct library with OpenSSL support. The following example assumes that OpenSSL has been obtained using Homebrew and that it resides in the "Cellar" folder:

./configure --with-openssl=/usr/local/Cellar/openssl@1.1/1.1.1k
make
cd src/ctlib/.libs
otool -L libct.4.dylib

/usr/local/lib/libct.4.dylib (compatibility version 5.0.0, current version 5.0.0)
/usr/local/opt/openssl@1.1/lib/libssl.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
/usr/local/opt/openssl@1.1/lib/libcrypto.1.1.dylib (compatibility version 1.1.0, current version 1.1.0)
/usr/lib/libiconv.2.dylib (compatibility version 7.0.0, current version 7.0.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.100.5)

To make it easier to locate the OpenSSL libraries, use install_name_tool to modify the paths as follows:

install_name_tool -change /usr/local/opt/openssl@1.1/lib/libssl.1.1.dylib @loader_path/libssl.1.1.dylib libct.4.dylib
install_name_tool -change /usr/local/opt/openssl@1.1/lib/libcrypto.1.1.dylib @loader_path/libcrypto.1.1.dylib libct.4.dylib

It is now possible to place all files into Omnis Studio's Frameworks folder

cp libct.dylib /Applications/Omnis\ Studio\ 31315.app/Contents/Frameworks (copies the symlink)
cp libct.4.dylib /Applications/Omnis\ Studio\ 31315.app/Contents/Frameworks
cp /usr/local/opt/openssl@1.1/lib/libssl.1.1.dylib /Applications/Omnis\ Studio\ 31315.app/Contents/Frameworks
cp /usr/local/opt/openssl@1.1/lib/libcrypto.1.1.dylib /Applications/Omnis\ Studio\ 31315.app/Contents/Frameworks

Please note: OpenSSL does not use the LGPL. Please consult the OpenSSL license before distributing binaries compiled with this option.

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. Due to code signing restructions on the Mac, libct.x.dylib file and its symlink should be copied into the:

Omnis Studio 10.2 xxxxx.app/Contents/Frameworks

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

  • Setting FREETDSCONF on macOS
    Please note that you can set environment variables for each DAM inside the Studio config.json file. Edit this file (for example; ~/Library/Application Support/Omnis/Omnis Studio 31315/studio/config.json), locate the "macOS" section and append to the sybasedam.ini entry as follows:

    "macOS": {
      "allowStopInRuntime": true,
      "macOSbuttonNewTextDrawingStyle": true,
      "macOStreeOutlineStyle": true,
      "menuTrackingSuppressTimers": true,
      "monitorDockKeyEvents": true,
      "odbcdam.ini": "ODBCINI=/Library/ODBC/odbc.ini",
      "oracle8dam.ini": "TNS_ADMIN=/instantclient_12_2, NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1",
      sybasedam.ini": "LANG=en_US, FREETDSCONF=/Users/myUser/freetds.conf",
      "stackSize": 1048576,
      "useDictation": true,
      "useToolbarStyleExpanded": false
    }, ...

    .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

WindowsmacOSLinux
libct (& symlink){Omnis folder}\ct.dllOmnis.app/.../Frameworks/libct.dylib → libct.4.dylib{Omnis folder}/libct.so → libct.4.so
.freetds.conf (& symlink) - optional%FREETDSCONF%˜/freetds.conf → .freetds.conf$FREETDSCONF
locales.conf - optionalC:\locales.conf/usr/local/etc/locales.conf/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 Temporarily unavailable
Sample freetds.conf filefreetds.conf
Sample locales.conf fileslocales.conf
License and ReadmeGNU_LGPL.PDF     readme.txt

References
FreeTDS website: www.freetds.org
GNU Lesser General Public License: www.gnu.org

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 Resources

 

Hit enter to search

X