Want to share your content on R bloggers? click here if you have a blog, or here if you don’t.
In a quite old after on this blog I wrote about an Oracle database connection from R on Windows to ODBC when you have old Oracle clients (32-bit version) installed in an enterprise installation.
It still works on R 32 bit. But now new R releases only come in 64-bit version. Difficult to stay with an R 4.1 (latest 32bit) when we are now on R 4.5…
Are there alternatives in such a situation?
An alternative with JDBC
Java to the rescue?
With JDBC clients and jar files, DBeaver can connect to all types of databases, so R should be able to do that too, either in 32b or 64b and on the most recent versions only with JDBC drivers?
Yes, it works too, see OHDSIs DatabaseConnector package.
I’ll post an example of usage here.
Sys.setenv("DATABASECONNECTOR_JAR_FOLDER" = "~/drivers_dbeaver/oracle")
library(DatabaseConnector)
library(dplyr)
library(dbplyr)
conn <- connect(
createConnectionDetails(
dbms="oracle",
connectionString = "jdbc:oracle:thin:@db-server.fr:1521/APPNAME",
user = "resu",
password = "drowssap"
)
)
# SQL
querySql(conn,"SELECT COUNT(*) FROM appname.table_a")
# dplyr / dbplyr
tbl(conn, in_schema('APPNAME', 'TABLE_A')) %>% count()
disconnect(conn)This package seems very interesting and is not limited to Windows and Oracle support, see here for more information:
https://github.com/OHDSI/DatabaseConnector/
Related
#dplyr #Oracle #database #DatabaseConnector #JDBC #Windows #bloggers