Help with setting up SQL ODBC database connection in IRIS container

To set up an ODBC connection from your IRIS container to an external database, you need two layers configured:

1. an OS-level ODBC DSN inside the container, and
2. an InterSystems IRIS SQL Gateway connection that uses that DSN. [1]

For the IRIS side, the SQL Gateway ODBC connection must use a DSN; it does not support connections without one. [1]

A practical setup based on the retrieved material is:

• install unixODBC and the target database’s ODBC driver in the container,
• create odbcinst.ini and odbc.ini,
• set ODBCINI to the full path of the ODBC initialization file,
• then create the SQL Gateway connection in the Management Portal using that DSN. [2][1]

Your docker-compose.yml approach of setting ODBCINI=/etc/odbc.ini is consistent with the documented Linux requirement to set ODBCINI to the full path, including filename, of the ODBC initialization file. [3]

For Linux, the ODBC initialization file is typically odbc.ini, and the documented pattern looks like this: [3]

[ODBC Data Sources]
MyDSN = MyDSN

[MyDSN]
Driver = /path/to/driver.so
Host = <host>
Port = <port>
Namespace = <namespace>
UID = <username>
Password = <password>
Protocol = TCP
Query Timeout = 1
Static Cursors = 0
Trace = off
TraceFile = logfile.log

If you are configuring a generic external database DSN, the exact DSN fields depend on that database’s ODBC driver, but IRIS expects that DSN to already exist before you define the SQL Gateway connection. [1]

In the Management Portal, define the IRIS SQL Gateway connection like this:

1. Go to System Administration > Configuration > Connectivity > SQL Gateway Connections.
2. Click Create New Connection.
3. Set Type of connection = ODBC.
4. Enter a Connection Name.
5. In Select an existing DSN, choose the DSN you created in odbc.ini.
6. Enter User and Password if needed.
7. Optionally click Test Connection.
8. Click Save. [1]

A key troubleshooting point from the Linux container example is that ODBC installation steps may create blank /etc/odbc.ini and /etc/odbcinst.ini files, which can break things. In that example, they explicitly removed those blank files and recreated links to the real config files. [2]

Note: The reason for the above is, step 3 & 4 would normally create 2 blank (hence invalid) ODBC files in \etc\ directory. Unlike Windows installation, these blanks ini files cause troubles, hence we need to delete them first, then simply re-create a link to the real ini files provided in a mapped Docker volume: /tf/odbcinst.ini, and /tf/odbc.ini [2]

That same example says these files must exist and be valid for Linux ODBC to work:

• odbcinst.ini
• odbc.ini
• the actual driver shared library file referenced by odbcinst.ini or odbc.ini [2]

It also shows a useful verification step:

odbcinst -j

This lets you confirm which files unixODBC is actually using for drivers and system data sources. In the example output, it reports the paths for odbcinst.ini and odbc.ini. [2]

So the main things to check in your container are:

• ODBCINI points to the real odbc.ini file. [3]
• /etc/odbc.ini and /etc/odbcinst.ini are not blank or invalid placeholder files. [2]
• The driver entry in odbcinst.ini points to a real installed shared library. [2]
• The DSN exists in odbc.ini. [1]
• After that, you still need to create the SQL Gateway connection in IRIS Management Portal; having only the OS files is not enough. [1]

[!NOTE]

For OS-specific instruc [1]

The retrieved material does not include a complete PostgreSQL-specific odbc.ini/odbcinst.ini example for IRIS SQL Gateway, so I can only confirm the required structure and process, not the exact PostgreSQL DSN syntax for your driver. [2][1]

If you want, paste your current odbc.ini and odbcinst.ini contents, and I can help you compare them against the required DSN/SQL Gateway structure shown here. [2][1]

Sources: