Friday, July 12, 2024
HomeHow ToConfiguring an ODBC Driver Manager on Windows, macOS, and Linux

Configuring an ODBC Driver Manager on Windows, macOS, and Linux

This article explains how to install and configure the ODBC Driver Manager on Windows, macOS, and Linux. Also, it shows how to configure the ODBC data source name.


What is an ODBC Driver Manager?

To explain what this driver manager is, take a look at the ODBC architecture in Figure 1.

ODBC architecture highlighting ODBC driver manager.
Figure 1. ODBC architecture.

The ODBC driver manager manages communication between apps and ODBC drivers. To put it simply, this is what happens when your application starts connecting to your database:

  • First, the app connects to a certain database using a connection string or an ODBC data source name.
  • Then, the driver manager loads the correct ODBC driver based on the connection string.
  • When the driver is loaded, the manager calls an SQLConnect or SQLDriverConnect function in the ODBC driver.
  • The connect function in the driver establishes a connection to the database. The security credentials are passed from the connection string used by the application.
  • Finally, the database responds by returning connection success or failure.

The application can now submit SQL statements through the driver manager. Then, they are submitted from the driver manager to the ODBC driver. And finally, into the database. The database will then return the results. 

So, the ODBC driver manager performs the following:

  • Provides an easy way to do common database tasks from your app. This is done uniformly regardless of the database product
  • Loads and unloads ODBC drivers.
  • Perform basic error handling.
  • Calls ODBC driver functions.

How to Install ODBC Driver Manager

You can start using the ODBC driver manager after installing and configuring it. Let’s consider how to install the ODBC driver manager depending on the operating system.

Installing and Configuring an ODBC Driver Manager on Windows

ODBC driver manager for Windows already exists in modern Windows machines. Starting with Windows XP and Windows Server 2003, the driver manager for Windows is preinstalled. You don’t need to download and install it unless you’re using an older Windows operating system.

The driver manager for Windows is also pre-configured. So, all you need to do is to install ODBC drivers and configure data sources.

To connect to the ODBC data source, we use the ODBC Data Source Administrator. Here, we can see our ODBC driver manager in the About tab.

Figure 2. The ODBC driver manager (odbc32.dll) in Windows.

That’s the driver manager in Windows in a file called odbc32.dll. We can also see installed ODBC drivers in this app as shown in Figure 3 below.

Figure 3. The list of ODBC drivers is displayed in the ODBC Data Source Administrator.

This list has a counterpart in the Windows registry as seen in Figure 4 below.

ODBC data source configurations are stored in the registry under ODBC.INI. Installed drivers are under ODBCINST.INI
Figure 4. The list of ODBC drivers and DSNs are stored in the Windows registry.

If you install another driver, an entry is added to the registry under ODBCINST.INI.

And we use these drivers to set up data source names or DSNs. When you configure a DSN, it is also stored in the Windows registry as shown in Figure 4. You can see this under the section ODBC.INI.

Note that there’s a separate odbcinst.ini and odbc.ini text files in C:\Windows. But for convenience, it is better to use the ODBC Data Source Administrator.

Installing and Configuring an ODBC Driver Manager on Linux

ODBC driver manager for Linux is unlike in Windows. You need to install it on your computer. In this case, we’re going to use the unixODBC driver manager and install it on the Ubuntu 20.04 LTS. So, Debian packages will be used to install ODBC drivers later.

What we need:

  • The Terminal for installing unixODBC.
  • Your favorite text editor for editing configuration files.
  • Working installation of MySQL, PostgreSQL, and SQL Server.
  • Devart ODBC drivers for MySQL, PostgreSQL, and SQL Server.

Now, don’t get intimidated by the Terminal. Many use this because if no one likes it, it won’t exist on all platforms. And sometimes, typing stuff is easier than clicking buttons.

So, let’s begin.

6 Painless Steps to Install and Configure an ODBC Driver Manager on Linux

Open the Terminal

There are a few ways to open the Terminal in Ubuntu.

The one is by clicking the Show Applications icon at the lower right corner of the screen. Then, search for Terminal, click its icon or press Enter when it appears in the search results.

Another is by pressing Ctrl-Alt-T. This option is easier and faster to do.

Download and Install UnixODBC

Download unixODBC. To install it, run the following command in the Terminal:

sudo apt install unixodbc

Wait until the installation completes.

Along with the installation of the driver manager comes a tester for DSNs called isql. Another is a tool called odbcinst that will help you with configurations. You will see these in action later. UnixODBC stores a list of drivers installed in a text file called odbcinst.ini. And all DSN configurations are stored in another file called odbc.ini. There’s no registry in Linux. So, this is the counterpart of the Windows registry entries you saw in Figure 4 earlier.

After installation, it’s still unusable until you install ODBC drivers.

Install ODBC Drivers

Open your favorite browser to download 3 Debian packages for the next ODBC drivers:

  • MySQL. Select the ODBC Driver for MySQL 4.2 DEB x64. This is the 64-bit driver for MySQL.
  • PostgreSQL. Select the ODBC Driver for PostgreSQL 4.2 DEB x64. This is the 64-bit driver for PostgreSQL.
  • SQL Server. Select the ODBC Driver for SQL Server 4.2 DEB x64. This is the 64-bit driver for SQL Server.

Note the folder where you downloaded the packages.

Then, go to your download folder and double-click one of the DEB packages. You will see something like this in Figure 5.

PostgreSQL ODBC driver package installer from Devart.
Figure 5. Sample ODBC driver installer. The sample displays the PostgreSQL ODBC driver installer.

To install the driver, click the Install button and wait until installation finishes. The Install button changes to the Remove one if the installation is successfully completed. Then, install t he remaining two.

Note that you will install a trial version. But it’s worth every penny if you wish to buy a license.

Verify Installation

Next, verify the installation of the unixODBC and the ODBC drivers.

To verify the installed unixODBC, run the Terminal again. Then, type the following command:

odbcinst -j

Then, you will see a similar configuration as in Figure 7 below.

ODBC Driver Manager configuration using odbcinst command line tool.
Figure 6. unixODBC configuration shown using the odbcinst command-line tool.

In Figure 6, you can also see the version of unixODBC and the path of the ODBC INI files for the drivers and data sources. Like in Windows, there’s a System, User, and File data source. You need the path of these files when you will configure data sources later. The configuration file paths are pre-configured upon installation of unixODBC. We chose not to change the path of these configuration files.

To review a list of all drivers installed earlier, run the following command in the Terminal.

odbcinst -q -d

See the result in Figure 7.

ODBC drivers installed and verified using odbcinst command-line tool.
Figure 7. Installed ODBC drivers verified using odbcinst command-line tool.

The drivers listed in Figure 8 also appear in the odbcinst.ini file. To check that out, view the file in your favorite text editor in Ubuntu. In our case, is gedit. Type it in the Terminal using the full path and file you saw in Figure 6 earlier.

Here’s the command for that.

gedit /etc/odbcinst.ini

See a screenshot of the output in Figure 8.

Odbcinst.ini file where ODBC drivers installed are listed.
Figure 8. Odbcinst.ini file where installed drivers are listed.

In Figure 8, you can see the names of the drivers we installed earlier and a list of the driver library files. All these components are inserted into the file during installation. You don’t need to configure them manually.

At this point, the unixODBC driver manager is installed and configured. And since ODBC drivers are also installed, it’s time to test connectivity to data sources.

Configure Data Source Names

Upon installation of the Devart ODBC drivers, 3 data source templates were added to the odbc.ini file. Check out Figure 9 to see what it looks like.

ODBC.ini file after installing ODBC drivers. 3 templates were added for MySQL, PostgreSQL, and MS SQL.
Figure 9. Odbc.ini after the installation of the 3 Devart ODBC drivers.

The blank data source configurations in Figure 6 are also a good starting point to configure data sources.

Configuring Data Sources using a Text Editor

To configure data sources, you need to fill up the blanks. Of course, you also need to have working installations of MySQL, PostgreSQL, and SQL Server. Figure 10 shows the configuration that we did with the gedit text editor.

Modified ODBC.INI file with data source configurations for 3 database products.
Figure 10. Modified odbc.ini with data source configurations.

Let’s dissect each of the options.

  • Data Source Name (DSN) – This is the first entry in configuring a data source. In Figure 10, the DSNs are MySQL-Test-DSNPostgreSQL-Test-DSN, and MSSQL-Test-DSN.
  • Driver – The driver entry defines what ODBC driver to use by the DSN.
  • Data Source – This is the database server or where the database is installed. It can be a name or an IP address. (I used IP addresses in Figure 10 so I hid them)
  • Database or Initial Catalog – The name of the database you want to access. The term ‘Initial Catalog’ is for SQL Server. The rest use the term ‘Database’.
  • User ID and Password – The security credentials to access the database.
  • Port – This is the port number used by each database product. In Figure 8, the port values are the default port numbers used by each database product.
  • Schema – Specifies what schema the PostgreSQL database belongs to.

To verify if these data sources are recognized by unixODBC, run this command in the Terminal.

odbcinst -q -s

And check the output in Figure 11.

3 data sources were verified using odbcinst.
Figure 11. Verifying data source names using odbcinst.

So, all 3 DSNs were recognized by unixODBC.

Testing the DSN Using the isql Command-Line Tool

To test the DSN, you need the isql command-line tool that comes with unixODBC. The syntax is:

isql <DSN>

Let’s try this with the MySQL DSN. In the Terminal, run the command isql MySQL-Test-DSN. Once you’re connected, you will see a response. Then, you can try to run a simple query. See all these in Figure 12 below.

Testing the MySQL DSN using isql.
Figure 12. Using the isql command-line tool to test a DSN and run a simple query.

That’s it. It’s easy.

You can also try it with PostgreSQL and SQL Server.

So, if data sources are working, then your Linux ODBC driver manager is complete.

Installing and Configuring an ODBC Driver Manager on macOS

Now let’s consider how to install and configure an ODBC driver manager on macOS.

5 Trouble-free Steps to Install and Configure ODBC Driver Manager on macOS

It’s a cakewalk to install an ODBC driver manager on macOS using iODBC. Try the steps below.

The workstation used in the following examples uses Mac OS X Big Sur.

Download iODBC Driver Manager

Get the latest iODBC Driver Manager. The version used in the following examples is 3.52.15.

You need to download the file mxkozzzz.dmg. It’s a disk image file containing the package installer for iODBC.

Install iODBC Driver Manager

Then, double-click the disk image file mxkozzzz.dmg. A window will open. Then, you will see the iODBC-SDK.pkg package file. Open it to start the installation.

NOTE: Mac OS X will not allow you to open the package initially. You will see something similar to Figure 13 below.

Installing iODBC Driver Manager is not allowed in MacOS X initially. It's blocked for security reasons.
Figure 13. iODBC installation blocked in Mac OS X Big Sur.

To overcome this, right-click the iODBC-SDK.pkg. A context menu will appear. Then, select Open With. Then, select Installer (default). But you will still see the blocking message in Figure 18. But you now have an option to open it anyway. So, click Open.

Then, follow the on-screen instructions until the installation is complete.

Install ODBC Drivers

You need to configure data sources to test the driver manager. But first, you need to install ODBC drivers. In our example, we will consider the installation of ODBC Driver for PostgreSQL.

Note, that to download the package installers you need a Devart account.

After the installation is completed, open the installer package of the ODBC driver for PostgreSQL by right-clicking it and clicking Open. A warning will appear to ask you if you want to install the package. So, click Allow to install the package. Then, follow the on-screen instructions. Agree with the Software license and proceed with the installation. Do the same with the ODBC driver for MySQL.

Devart ODBC driver for PostgreSQL package installer for Mac OS X.
Figure 14. ODBC driver package installer in Mac OS X.

By the end of the installation, you will see a successful installation message.

Successful installation of the PostgreSQL ODBC driver.
Figure 15. ODBC driver successful installation message on Mac OS X.

Configuring Data Sources

The best part of using iODBC compared to unixODBC is the GUI interface. It’s almost the same as in Windows.

And that’s not all.

System DSN templates were included along with the installation of ODBC drivers. See Figure 21 below.

The iODBC Driver Manager showing System DSN. Two DSN templates were included in the installation of the MySQL and PostgreSQL drivers from Devart.
Figure 16. iODBC Driver Manager and the System DSN. Two DSN templates were included in the installation of Devart ODBC drivers.

So, you can start with those templates to configure a MySQL connection. To do that, select the DEVART_MYSQL DSN and click Configure. You will see that the User ID, Password, and the rest are blank. Only the MySQL default port (3306) is filled-up. Add your credentials and test them against the sakila sample database.

A modified DEVART_MYSQL System DSN with credentials.
Figure 17. A modified DEVART_MYSQL System DSN.

Then, you can do the same thing for the DEVART_POSTGRESQL.

At this point, you are ready to test for database connectivity.

Test Connectivity in iODBC Data Source Administrator

To test the connection to the MySQL data source, select the DEVART_MYSQL System DSN. Then, click Test. You will be asked for a Username and Password allowed in the MySQL database. If your credentials are correct, a success message will appear.

Success message appears after testing DEVART_MYSQL System DSN.
Figure 18. Success message after testing DEVART_MYSQL System DSN.

Then, do the same thing for DEVART_POSTGRESQL System DSN.

At this point, the iODBC Driver Manager is installed and configured correctly.