Issue with Driver not found when using DBI

General
#1

Recently ran into a problem when using DBI to connect:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Data source name not found and no default driver specified

Here is the ODBC info:
odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Any help would be greatly appreciated. I am not experienced with RSTUDIO

#2

Hello @spelunkee ,

please include the R-code with library statements that you used.
A google search on r dbi odbc connection string gave various results mentioning the package odbc.
Have you installed and used that package?

#3

ODBC is isntalled, here is the command used:

con <- dbConnect(odbc::odbc(), "xxxxx", UID = 'uid', PWD = 'pwd')
Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Data source name not found and no default driver specified

output from this query to show installed drivers:
odbc::odbcListDrivers()
1 PostgreSQL Description ODBC for PostgreSQL
2 PostgreSQL Driver /usr/lib/psqlodbcw.so
3 PostgreSQL Setup /usr/lib/libodbcpsqlS.so
4 PostgreSQL Driver64 /usr/lib64/psqlodbcw.so
5 PostgreSQL Setup64 /usr/lib64/libodbcpsqlS.so
6 PostgreSQL FileUsage 1
7 MySQL Description ODBC for MySQL
8 MySQL Driver /usr/lib/libmyodbc5.so
9 MySQL Setup /usr/lib/libodbcmyS.so
10 MySQL Driver64 /usr/lib64/libmyodbc5.so
11 MySQL Setup64 /usr/lib64/libodbcmyS.so
12 MySQL FileUsage 1
13 FreeTDS Description Free Sybase & MS SQL Driver
14 FreeTDS Driver /usr/lib/libtdsodbc.so
15 FreeTDS Setup /usr/lib/libtdsS.so
16 FreeTDS Driver64 /usr/lib64/libtdsodbc.so
17 FreeTDS Setup64 /usr/lib64/libtdsS.so
18 FreeTDS Port 1433
19 MariaDB Description ODBC for MariaDB
20 MariaDB Driver /usr/lib/libmaodbc.so
21 MariaDB Driver64 /usr/lib64/libmaodbc.so
22 MariaDB FileUsage 1

Output from rpm -qa | grep oracle
oracle-instantclient-odbc-21.4.0.0.0-1.x86_64
pcp-pmda-oracle-5.3.1-5.el8.x86_64
oracle-instantclient-basic-21.4.0.0.0-1.x86_64

it looks like the drivers are installed, I'm definitely missing something.

#4

The error message says Data source name not found .
Are you sure that xxxxx is a valid ODBC resource ?
Do you have a utility on your Operating System to check if that is the case?

#5

I used XXXX to mask the actual name of the datasource as well as removed pwd, etc... They are correct though and are used by other R | R-STUDIO | SHINY servers in my env.

It seems to me that the install cant see the drivers for some reason.

#6

Three suggestions:

  • explicitly use library(DBI) and library(odbc) before the call to dbConnect.
    But maybe you already did this, but did not show all the relevant code :wink:
  • try to connect to another ODBC resource and see if this also fails
  • remove the DBI and odbc packages and reinstall them.
    Equivalent to switch off and on of the computer. It should not help, but sometimes it does.

Good luck!

#7

Hello!
I was able to remedy the issue. Looks like (as a result of odbc_update_ini.sh being absent) odbc.ini and odbcinst.ini were misconfigured and looking for an older (11g) Oracle Instant Client. I verified the drivers version (12c) and its location (/ora1/app/oracle/product/12.2.0/dbhome_1/lib/libsqora.so.12.1) then proceeded to update both files to reflect the new version and location.

I think we can call that a solution! Thank you so much for your help Han