unixODBC installed but odbcinst.ini and odbc.ini are empty

Question

I'm trying to setup a CentOS 8 RStudio-Server to connect to MS SQL server using odbc. I think I've installed the unixODBC driver, the odbcinst -j command shows below: But the ini files are empty and R odbc isn't able to connect to the db. Hoping someone can provide some hints on how to troubleshoot this. Thank you in advance.

$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/user/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

There is no way the authors of `unixodbc` or the package maintainers can know what to put in there, as `unixodbc.ini` depends on drivers (different packages) you have installed on this system, and `odbc.ini` identifies databases specific to your system. Have you installed specific ODBC drivers for any particular DBMS? For example, `libsqliteodbc`, `msodbcsql17`, `odbc-postgresql`, `tdsodbc`. — r2evans, Feb 20 '20 at 00:59
Ahhh, thank you!. Based on MS's instructions here: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15 I thought adding the MS repo would've automatically installed msodbcsql17 when I installed the unixODBC. But when I installed msodbcsql17, it actually installed and now the odbcinst.ini is populated with [ODBC Driver 17 for SQL Server] ... — stuck, Feb 20 '20 at 04:15
I thought as much, glad that helped. Now you're stuck with configuring your `odbc.ini` to finish the job, is that right? — r2evans, Feb 20 '20 at 05:18
That's completely in your camp, unfortunately, since we don't know your database setup. But has some good examples of `odbc.ini`. You likely just need to start with `[somename_does_not_matter]`, then `Driver=ODBC Driver 17 for SQL Server`, then `Database=`, `Server=`, `Port=` per your DBMS. That might be all you need. On the flip side, there is no need to use a system-wide `odbc.ini`, you can specify all connection parameters normally found there within your `DBI::dbConnect` directly. (There is utility to the system-wide `odbc.ini`, I'm not arguing either way.) — r2evans, Feb 20 '20 at 16:16

score -1 · Answer 1 · edited Jan 10 '22 at 12:01

I had same problem I could resolved it by installing proper ODBC driver.

Here is my config:

/etc/odbcinst.ini:

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
UsageCount=1

/etc/odbc.ini:

[voip]
Description = support team
Driver      = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
Server      = 172.16.x.x
User        = dbuser
Password    = dbpass
Port        = 3306
Database    = asterisk

unixODBC installed but odbcinst.ini and odbc.ini are empty

1 Answers1