Installing and Configuring an ODBC Driver on macOS

May 13th, 2020

This article covers the installation and configuration of Devart ODBC drivers on macOS.

Prerequisites

Before installing the driver, you need to make sure that your system has a driver manager installed. Our drivers for macOS are compatible with the iODBC driver manager. iODBC is a free and open source driver manager widely used on macOS, Linux, and Unix systems.

Most macOS installations do not come with a driver manager as part of the operating system, so you need to install it yourself. You can download the latest version of iODBC from the iODBC website. The driver manager is installed with its own GUI tool for creating a DSN, the iODBC Administrator.

To install iODBC:

  •  Download iODBC and double-click on the mxkozzzz.dmg file. 
  • Double-click on the iODBC-SDK.pkg file and follow the instructions.
iODBC install on macOS

You might get an error “iODBC-SDK.pkg can’t be opened because it is from an unidentified developer”. To resolve the issue, open System Preferences and choose Security & Privacy. Under the General tab, click on the lock icon in the bottom left corner to unlock the settings. You should see that the installer you tried to open was blocked from opening. Click on the Open Anyway button to open the app.

You will be guided through the steps necessary to install the iODBC Driver Manager. After successful installation, you should be able to see the iODBC folder in the Applications folder.

iODBC folder in Applications on macOS

Installing an ODBC Driver on macOS

For the purposes of this article, we will install ODBC Driver for Oracle on macOS. You can find Devart ODBC drivers for macOS and download the needed driver as a .pkg file on our ODBC drivers page. A .pkg file is installed with the Apple Installer. Devart drivers are compatible with 32-bit and 64-bit ODBC-compliant applications.

1. Double-click on the installer file and follow the prompts. You will need to allow opening the file by clicking on the Open Anyway button under the General tab in System Preferences > Security & Privacy.

Allow installation of ODBC driver on macoS

In the Introduction and Read Me steps, click Continue.

Install ODBC driver on macOS

2. After reading the license agreement, click Continue, then Agree to proceed with the installation.

License Agreement of ODBC Driver on macOS

3. In the Registration step, you can either enter your license code in the Activation Key field or select Trial if you would like to first evaluate the software. Click Continue.

Trial of ODBC Driver on macOS

4. In the Installation Type, click on the Install button.

Install ODBC Driver on macOS

5. Once the installation has been successfully completed, you will see the corresponding message.

Successful installation of ODBC driver on macOS

Configuring an ODBC Driver on macOS

To configure the installed ODBC driver on macOS, you can use a sample System DSN (data source name), which is created during the driver installation (for example, Devart_Oracle for Devart ODBC Driver for Oracle), or create your own DSN. There are two major types of DSN: User DSN and System DSN. User DSN is created for a specific user and only the user who created it can see and use the DSN. System DSN can be used by any user in the system who has proper rights.

See the Connection String options in the driver’s documentation for details about the parameters that can be set for each DSN.

The odbc.ini file contains information about DSNs and is stored under the following path:

  • User DSNs: ~/.ODBC.ini
  • System DSNs: /Library/ODBC.ini

To create or modify a DSN, you can either use the GUI iODBC Administrator app or edit the appropriate odbc.ini file.

Creating a DSN Using iODBC Administrator (64-bit)

  1. Run iODBC Administrator64 from Applications > iODBC.
  2. Select User DSN or System DSN tab.
  3. Click on the Add button.
  4. Select the driver (e.g, Devart ODBC Driver for Oracle), then click on OK.
  5. Specify a name for the DSN and, optionally, a description.
  6. Specify connection string parameters and their values:
    • Click on the plus sign to create a Keyword/Value pair.
    • Double-click on the Keyword field and specify the parameter name.
    • Double-click on the Value field and specify the value for the parameter.
  7. Click on Ok to save the DSN.
Sample DSN on macOS

If this is the first time that you are creating a user DSN, the iODBC manager creates the odbc.ini file in the home directory. The odbc.ini file and parameters for a sample system DSN were created during the driver installation, so you can create a new system DSN or specify connection information in the existing DSN.

Creating a DSN by Adding Connection Information to the odbc.ini File

You can configure the odbc.ini file using vi or a similar text editor, such as nano. For this article, vi is used to edit odbc.ini. To create a DSN, open Terminal and run the command sudo vi /Library/ODBC/odbc.ini for a system DSN or sudo vi ~/.odbc.ini for a user DSN. This will either open the odbc.ini file that already exists in the corresponding directory or create a new file if you are creating the first DSN for the driver. To enter the insert mode, type i.

Enter [ODBC Data Sources] at the beginning of the file. Parameters in the odbc.ini file are specified in the form of parameter_name = parameter_value.

To create a DSN and specify connection information, insert the following information:

  • Under [ODBC Data Sources], specify the DSN name and the driver name (e.g. Devart ODBC Driver for Oracle), in the form of dsn_name = driver_name.
  • Under the DSN declaration, specify the DSN name, in the form of [dsn_name].
  • Under [dsn_name], specify connection parameters and any additional parameters, such as the server address, database name, user credentials, character set, etc., in the form of parameter_name = parameter_value.
Editing odbc.ini on macOS

To save the changes to the odbc.ini file, press the Esc key, then press Shift + : , type wq and press Enter.

Testing ODBC Connection to the Data Source

To test the ODBC data source connection, you can use the GUI iODBC Administrator64 app or the iODBC command line utilities iodbctest / iodbctestw (Applications > iODBC > iODBCTest.command / iODBCTestUniCode.command).

To test the connection in Administrator64, select the DSN and click on Test.

Successful ODBC connection on macOS

The GUI Administrator app only tests connection to the server, whereas iodbctest and iodbctestw allow you to issue SQL commands and retrieve results. iodbctest retrieves all results in ASCII mode, while odbctestw retrieves all results in Unicode mode.

Run the utility and, when prompted for the ODBC connection string, enter the required connection parameters (the driver name, server address, database name, user credentials etc.). If you set the connection parameters and their values in the DSN, simply specify the DSN name in the form of dsn=dsn_name.

The connection string takes parameters in the form of parameter_name=value, with each parameter delimited by a semi-colon (;), no spaces. Once connected, you can issue a SQL command to retrieve data from the data source.

Test ODBC connection on macOS

To download free full-featured 30-day trial versions of any Devart ODBC Driver, select the necessary driver on the driver listing, and navigate to the download page.

Leave a Comment