Omnis Technical Note TNSQ0038August 2019

Using the Microsoft ODBC Driver for SQL Server on Linux and macOS

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

Introduction
This technote relates to the "Microsoft ODBC Driver 17 for SQL Server" available for Linux and macOS, released in 2018.
Use of this driver requires the Unix ODBC Driver Manager (libodbc.so / libodbc.dylib). While this library may already be present on Linux operating systems, for macOS it can be extracted and installed from the download package referred to below.
The remainder of this technote assumes a macOS installation, although there are notes for Linux users below.

Downloading and Installing the Driver
Please refer to the download and installation instructions on the Microsoft ODBC Driver Installation page.
For macOS, you may elect to open the Homebrew script and download the relevent tarball file directly: msodbcsql-17.2.0.1.tar.gz

Whether you run the Homebrew script or extract the files manually, you should end up with the driver file:
/usr/local/lib/libmsodbcsql.17.dylib

and the required resource file:
/usr/local/share/msodbcsql17/resources/en_US/msodbcsqlr17.rll

The driver should be registered with the Unix ODBC Driver Manager, i.e. in /Library/ODBC/odbcinst.ini

Setting up an ODBC Datasource
You can find the macOS ODBC Manager in /Applications/utilities. From the Drivers tab, you can register the ODBC driver manually if required (press Add then locate the driver file as shown).
To add an ODBC User DSN, select the Users tab and press Add. Enter the key-value pairs in the dialog as shown, amending your connection details as required.

Connecting from Omnis Studio
Before starting Omnis, navigate to Omnis Studio 10.0.1.app->Contents/MacOS/xcomp/ini and edit your odbcdam.ini file.
If you require only User-DSNs, the ODBCINI entry should be set to your local user's odbc.ini file, e.g.

ODBCINI=/Users/myUser/Library/ODBC/odbc.ini

If you want to differentiate between User and System DSNs, you can additionally create an ODBCSYSINI entry, e.g.

ODBCINI=/Users/myUser/Library/ODBC/odbc.ini
ODBCSYSINI=/Library/ODBC/odbc.ini

One of the entries should point to the odbc.ini file you edited in the previous step.

Using the Omnis Studio ODBC DAM, it is necessary to change the session object $mode property to kODBCModeUnix.

Do sessionObj.$mode.$assign(kODBCModeUnix)

This should be done before logging on and tells the ODBC DAM to unload the default driver manager and load the Unix ODBC Driver manager (libodbc.dylib on macOS).
Using the SQL Browser session editor, this property can be set via the Advanced tab as shown:

The logon hostname should match the name of the ODBC DSN created above. Add your username, password and optional database details, and save the session template.
You should then be able to open the ODBC session and connect to SQL Server:

Or, using notation:

Do sessionObj.$mode.$assign(kODBCModeUnix)
Do sessionObj.$database.$assign('test')
Do sessionObj.$logon('ntms2017','henry','password','session1') Returns #F

Notes for Linux
From the Microsoft website, follow the instructions to download and install the msodbcsql17 ODBC driver, which typically installs to:
/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1

and the dependent resource file will be here:
/opt/microsoft/msodbcsql17/share/resources/en_US/msodbcsqlr17.rll

The driver package should also install the dependent Unix driver manager package which installs to:
/usr/lib/x86_64-linux-gnu/odbc/libodbc.so.2

The Linux ODBC DAM, looks for a driver manager named "libodbc.so" so it is necessary to create the following symbolic link:

cd /usr/local/lib
sudo ln -s /usr/lib/x86_64-linux-gnu/odbc/libodbc.so.2 libodbc.so

You can locate your odbc.ini file by setting the ODBCINI environment variable inside the omnisX64 startup script, e.g.

export LD_LIBRAY_PATH="/home/users/myuser/studio-install-folder":$LD_LIBRARY_PATH
export ODBCINI=/home/users/myuser/odbc.ini
"/home/users/myuser/studio-install-folder/omnis" $1 $2 $3 $4 $5 $6 $7 $8

Alternatively, you can rely on the standard file location used for Linux User DSNs: ~/.odbc.ini (note the leading "dot") Hint: since .odbc.ini is a hidden file, you can create a symbolic link to this file as follows:

cd ~
ln -s .odbc.ini odbc.ini

To create a suitable ODBC User DSN, you should edit your odbc.ini file and add entries similar to:

[ODBC Data Sources]
ntms2017 = Microsoft ODBC Driver for SQL Server
 
[ntms2017]
Driver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
Description = SQL Server 17 running on SERVER-PC
UID = henry
PWD = password
Database = test
Server = 192.168.100.15

If your odbc.ini file already contains ODBC DSNs, the new DSN name should appear at the bottom of the [ODBC Data Sources] section. The corresponding [section] can then appear at the bottom of the file and the section name should match the name of the ODBC Data Source entry, as shown.

Note: Creation of a odbcinst.ini file is not necessary on Linux, since the driver file is named explicitly by the ODBC User DSN. Should you wish to create one however, Linux normally looks for this file in /usr/local/etc/odbcinst.ini :

[Microsoft ODBC Driver for SQL Server]
Driver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
Setup = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1

Note also, that because the Linux connection is not dependent on the $mode session property, connection is also possible in earlier versions of Omnis Studio.

References
Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS

 

Search Omnis Developer

 

Hit enter to search

X