R+ODBC

I just established a connection from R to Oracle / MSSQL using ODBC.

First, I installed R

sudo yum install R

Then, ODBC

sudo yum install unixODBC-devel freetds-libs

I set up my mirror in .Rprofile

options(repos=structure(c(CRAN="http://cran.example.com/")))

I install ODBC Library in R

R -s <<EOF
install.packages("odbc")
EOF

I configure my odbc driver to Oracle Instant Client 21c in ~/.odbcinst.ini

[OracleODBC]
Description = Oracle ODBC driver
Driver = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1

I configure my database in ~/.odbc.ini

[DB01]
Driver = OracleODBC
ServerName = DB01

Quick check (info: here I use Kerberos without password)

$ isql DB01
Connected!
SQL> quit()

Let’s do it R

library("odbc")
odbc_con <- dbConnect(odbc(), dsn="DB01")
dbGetQuery(odbc_con, 'select sysdate from dual');
     SYSDATE
   1 2022-11-25 16:08:03
dbDisconnect(odbc_con)

Done 🙂

Recommend lecture for SQL Server : R package: odbc (renenyffenegger.ch)