Imagine you’re working in a UNIX-based environment, managing multiple databases like MariaDB, PostgreSQL, or Oracle, and you have to connect their data to different applications. Without UNIX ODBC drivers, you’d have to write custom code for each database, which would quickly turn into a nightmare — each database has its own way of handling queries, connections, and authentication. Luckily, ODBC drivers can save the day by acting as “translators”. As you use ODBC connectivity, you can write your code once, and the ODBC driver will handle the specifics of ”talking” to each database.
And, since in UNIX systems various databases like MariaDB, PostgreSQL, Oracle, and Microsoft SQL Server are often used, ODBC drivers streamline accessing and managing data from these platforms.
In this article, we’re going to explain in detail how you can install and connect ODBC drivers in RPM-based systems. Keep reading to learn the process.
Table of contents
- Understanding ODBC drivers
- Step-by-step guide to installing ODBC drivers
- How to connect ODBC drivers on Linux and UNIX
- Conclusion
Understanding ODBC drivers
ODBC drivers play a vital role in enabling seamless database interactions, doing all the heavy lifting when it comes to “translating” requests from the application into something the database “understands.”
For instance, you need your app to retrieve customer data from a SQL Server database. This app sends the request through the UNIX ODBC driver, which then communicates with the SQL Server, gets the data, and sends it back to the app.
This approach makes this app “database-agnostic,” meaning it doesn’t need to care about the specific database — it just works, thanks to ODBC.
Step-by-step guide to installing ODBC drivers
In complex UNIX environments where multiple databases are used, ODBC drivers ensure that all DBMSs can be connected to your app smoothly. Whether you’re managing MariaDB, Oracle, PostgreSQL, Microsoft SQL Server, or any other database on a UNIX system, UNIX ODBC drivers are essential for efficient and reliable database connectivity.
Connecting applications to databases in an RPM-based ODBC environment like Fedora or Solaris can be a powerful way to unlock the full potential of your data. But for that, you’ll need a smooth, reliable method to bridge the gap between your system and various databases.
Prerequisites
Before installing UNIX ODBC drivers on Fedora Linux or a similar RPM-based OS, it’s important to ensure that your system meets the necessary requirements.
System updates
Ensure your system is up-to-date with the latest security patches and software packages.
Root or sudo privileges
You will need root or sudo privileges to install and configure system-wide UNIX ODBC drivers and modify configuration files like /etc/odbc.ini and /etc/odbcinst.ini.
Development tools
You might need to compile certain drivers from the source, and for that, you will need to install development tools like gcc, make, and autoconf.
These tools might already be installed on your Fedora. But in case they aren’t, and if for some reason your requirements are not fulfilled by a default dnf-based installation, you may need to carry out some additional steps:
- Download the source tarball.
- Unzip and extract this archive.
- Compile the source with ./configure –prefix=/usr/local.
- Use the make command to compile the source code as per the ./configure command.
- Perform the installation using sudo make install command.
Install UNIX ODBC driver manager
The UNIX ODBC list driver package provides the ODBC driver manager for your OS, which acts as a “middle man,” letting you use ODBC drivers on UNIX, as it takes your requests and maps them to an appropriate RDBMS-specific ODBC driver.
Instead of dealing with each database uniquely, UNIX ODBC list driver lets you use one standardized approach to manage all your connections. It also comes with handy tools to install and configure these connections, which makes it a go-to solution for anyone working with databases on a UNIX-like system.
In this step-by-step guide, we’ll walk you through the installation process of UNIX ODBC drivers on Fedora Linux, but you can use these recommendations for other RPM-based systems. We’ll also explain what you have to do to let your system access multiple databases seamlessly.
Step 1. Update system packages
Make sure your system is updated. If not, it’s time to do it.
Step 2. Install unixODBC
The UNIX ODBC package provides the ODBC driver manager on Fedora. This is the core component you need in order to use interface applications with database-specific ODBC drivers.
Once all the dependencies are sorted out, dnf will do all the heavy lifting for you.
Step 3. Verify the installation
After installation, confirm that the ODBC driver manager is installed by checking its version. Also, configure file details for /etc/odbc.ini and /etc/odbcinst.ini, which are the key configuration files.
Download and install the ODBC driver
Next, you must install UNIX ODBC drivers for the specific databases you’re working with. For educational purposes, we’ll overview the process of installation of the Devart ODBC driver for PostgreSQL. However, you will be able to install any other driver using the same approach.
Download the ODBC driver
In this tutorial, we’re going to use the Devart ODBC driver for PostgreSQL that is available with a free trial. Based on your architecture, you can choose either x86 or x64 version.
To start the installation, use the yum command as shown below.
Verify that Devart’s UNIX ODBC driver for PostgreSQL is installed in the appropriate folder.
Configure the ODBC Driver
After downloading and installing the UNIX ODBC driver for PostgreSQL, you’ll need to configure the /etc/odbcinst.ini and /etc/odbc.ini files. These files tell the ODBC system which drivers are installed and how to use them to connect to specific databases.
1. Configure /etc/odbcinst.ini file
The /etc/odbcinst.ini file is like a system directory for all the ODBC drivers installed on your UNIX or Linux machine. If the /etc/odbc.ini file tells applications where to connect, odbcinst.ini tells them how — by listing the drivers used for those connections. This file holds information about driver names, shared library paths, and any driver-specific settings. Think of it as the “behind-the-scene” file that ensures your apps “know” which drivers to load when “talking” to databases. Keeping this file properly configured helps prevent connection errors and ensures everything will run smoothly.
Follow the steps below to configure your freshly installed driver.
1. Open the file in a text editor using the following command:
sudo nano /etc/odbcinst.ini
2. Add the necessary driver details for each of the databases’ section
2. Configure odbc.ini file
The /etc/odbc.ini file is like a directory for your system’s database connections. It “tells” applications how to connect to different databases. Think of it as a guidebook that holds essential details like the Data Source Name (DSN), driver type, host IP or server name, port, database name, and login credentials. When multiple apps need access to a database, they can simply refer to this file for the connection info, saving you the hassle of re-entering details repeatedly. It works alongside /etc/odbcinst.ini we just configured. Keeping this file up-to-date ensures your applications stay connected to the right data sources without issues.
1. Open the file using the following command:
sudo nano /etc/odbc.ini
2. Add the necessary connection details.
Ensure you’ve edited the /var/lib/pgsql/15/data/pg_hba.conf file and updated the Unix domain socket entry as shown below. Here, we’ve used the user postgres as an example, but feel free to replace it with the username of your setup.
By updating this entry, you’re setting the rules for users to access the database locally, adding an extra layer of security. Once done, don’t forget to restart the PostgreSQL service for the changes to take effect.
How to connect ODBC drivers on Linux and UNIX
Once you’ve configured the UNIX ODBC drivers, the next step is to establish the connection between your application or a client and the database. Follow the steps below to proceed.
Test and troubleshoot your DSN configuration
A Data Source Name (DSN) is a configuration that stores details required to connect to a specific database, such as the database name, server address, and driver. It acts as an identifier for the database connection, simplifying access by avoiding the need to re-enter connection details every time.
The isql command-line tool, provided by the UNIX ODBC package, allows you to test and troubleshoot your DSN configuration. With isql, you can easily verify that your DSN was set up correctly by running SQL queries directly from the terminal.
Follow the steps described below to test a PostgreSQL UNIX ODBC connection.
1. Use the following command.
isql -v DEVART_POSTGRESQL
As you can see, the driver works. To test it further, let’s use a database to which we’ll connect and update it with more data. In this example, we’re going to use the popular DVDRental sample database for PostgreSQL, which is a pre-built dataset designed to help you get hands-on with SQL queries and database concepts. It simulates a DVD rental store, with tables for customers, films, payments, staff, and more — giving you realistic data to practice with.
2. Let’s insert around 10 rows and run the SELECT query like the one shown below.
3. Use the SQL>Select first_name,last_name, salary from employees to perform the selection.
You’ve just successfully executed DDL (Data Definition Language) and DML (Data Manipulation Language) commands using isql, powered by UNIX ODBC list driver. This means we were able to connect seamlessly to a PostgreSQL database running in a Linux or ODBC UNIX environment and perform operations like creating tables, inserting data, and more.
Whether you’re operating in a mixed ODBC UNIX environment or just need to run SQL across platforms, you’ve now unlocked the magic of UNIX ODBC to connect, query, and manipulate data effortlessly.
Conclusion
To wrap things up, we’ve gone through the process of installing and configuring the UNIX ODBC drivers on Fedora Linux step by step. Check ODBC drivers for Linux and UNIX systems from Devart to connect to multiple databases and environments seamlessly!
