Connecting with ODBC

ODBC allows you to connect to any database that supports the ODBC standard.

Connecting with ODBC involves four steps, which we’ll go through in detail below.

  1. Download a connector for the database you’re trying to connect to.
  2. If you’re on macOS or Linux, you’ll need unixodbc.
  3. Configure ODBC.
  4. Tell PopSQL which ODBC connection to use

Step 1: Download a connector

Step 2: Get unixodbc

If you’re a macOS user:

# If you use Homebrew (more common):
brew install unixodbc

# If you use MacPorts:
sudo port unixodbc

If you’re a Linux user:

sudo apt-get update
sudo apt-get install unixodbc

If you’re a Windows user, nothing to do here, go to the next step.

Step 3: Configure ODBC

For Windows users, visit this article.

Locate the configuration files

Now we can locate the ODBC configuration files with the following command odbcinst -j:

$ odbcinst -j
unixODBC 2.3.5
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/popsql/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

/usr/local/etc/odbcinst.ini is a configuration file to define the ODBC drivers available.

/usr/local/etc/odbc.ini is a configuration file to define the ODBC data source name (DSN) available.

Declare a new driver

Let’s open /usr/local/etc/odbcinst.ini and add the following section:

[MyODBC Driver]
Description = MyODBC Driver
Driver      = /usr/local/lib/libmyodbcdriver.dylib

The most important line is the Driver one. You will need to specify the driver library previously installed. On macOS, libraries are ending with .dylib and on linux with .so.

Declare a new data source name (DSN)

Let’s open /usr/local/etc/odbc.ini and add the following section:

[MyODBC Connection]
Driver      = MyODBC Driver
ServerName  = localhost
Port        = 9999
Database    = test
Username    = popsql

The most important line is the Driver one. You will need to specify the driver name we specified in the previous step.

All other attributes as ServerName, Port, Database, Username are optional and depends on the connector. I implemented them in my custom libraries but your your connector might use other name or allows even more attributes as configuring ssl for example. Please refer to their documentation.

You can also specify those attributes later on in the connection string.

Verify your config is good

To make sure our configuration worked, we can use a tool called isql

isql syntax is: isql DSN [UID [PWD]] [options]

If you did not specify any username or password in the previous step you can specify them here.

If everything is working as expected the command output should looks like:

$ isql -v "MyODBC Connection"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

Step 4: Tell PopSQL which ODBC connection to use

Now you can tell PopSQL which connection to use based on what you wrote in odbc.ini.

Screenshot of pasting the connection string in PopSQL