Developers

Notes for macOS Catalina and Big Sur
Please refer to the download and installation instructions on the Microsoft ODBC Driver Installation page. To install using Homebrew, enter the following commands from the terminal window:

The Homebrew script installs all files needed to make a connection using the ODBC driver, including:

The ODBC driver is registered with the Unix ODBC Driver Manager using the odbcinst.ini file which we will place in ~/Library/ODBC/odbcinst.ini. You can create this file yourself or copy the file from Homebrew's "Cellar" folder. You can locate the file using the terminal command:

brew list msodbcsql17 --verbose

then copy the file to your ~/Library/ODBC folder. The default contents of this file are as follows:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib

Gatekeeper Mitigation
macOS Gatekeeper prevents codesigned applications from loading non-signed extensions- such as libodbc.dylib. It is therefore necessary to copy or place a symbolic link to the ODBC library from the Omnis.app/Contents/Frameworks folder.

Example 1: Copy the ODBC libary directly into the Frameworks folder:

cd "Omnis Studio 10.2 30204.app/Contents/Frameworks"
cp /usr/local/Cellar/unixodbc/2.3.9_1/lib/libodbc.2.dylib .
ln -s libodbc.2.dylib libodbc.dylib

Example 2: Create a symbolic link inside the Frameworks folder:

cd "Omnis Studio 10.2 30204.app/Contents/Frameworks"
ln -s /usr/local/Cellar/unixodbc/2.3.9_1/lib/libodbc.2.dylib libodbc.dylib

Setting up an ODBC Datasource
Prior to macOS Big Sur, 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.

To register the ODBC datasource manually, use a text editor and edit the odbc.ini file directly (either /Library/ODBC/odbc.ini or ~/Library/ODBC/odbc.ini). Example::

[ODBC Data Sources]
ntms2017 = ODBC Driver 17 for SQL Server

[ntms2017]
Driver = /usr/local/lib/libmsodbcsql.17.dylib
Description = SQL Server 17 on SERVER-PC
UID = henry
PWD = password
Database = test
Server = 192.168.100.15

   

Using the Omnis Studio ODBC DAM, it is necessary to change the session object $mode property to 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

Troubleshooting
If you receive a logon error similar to "Driver manager could not be loaded", this indicaates a problem locating or loading the ODBC driver manager library. Make sure that you either copied the libodbc.2.dylib into Omnis' Frameworks folder and/or make sure that the symbolic link libodbc.dylib exists and points to libodbc.2.dylib. Remember to set the ODBC DAM's $mode session property to kOdbcModeUnix in order to use the Microsoft ODBC Driver.

Error message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
Locate your Omnis writable files folder (e.g. /Users/myUser/Library/Application Support/Omnis/Omnis Studio 30204/) and edit the Studio/config.json file. Under the "macos" section, make sure that the ODBCINI key value matches the location of your odbc.ini file. If you defined a User DSN as opposed to the System DSN, you may need to change the location, or add an ODBCSYSINI entry to differentiate User and System DSNs, for example:

"odbcdam.ini": "ODBCINI=/Users/myUser/Library/ODBC/odbc.ini", "ODBCSYSINI": "/Library/ODBC/odbc.ini", …

Notes for Linux
Please refer to the download and installation instructions on the Microsoft ODBC Driver Installation page.
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.