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

Fanestil

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. – Leaseholder

Ahhh, thank you!. Based on MS's instructions here: learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/… 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] ... – Fanestil

I thought as much, glad that helped. Now you're stuck with configuring your odbc.ini to finish the job, is that right? – Leaseholder

That's correct, odbc.ini is still empty. – Fanestil

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.) – Leaseholder

Answer 1

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

Tucky