Article
Timothy Leavitt · Oct 12, 2022 1m read

Loading data into InterSystems IRIS from R using RJDBC

I just wrote up a quick sample to help a colleague load data into IRIS from R using RJDBC, and figured it's worth sharing here for future reference.

Ultimately it was pretty simple, aside from IRIS not liking "." in column names; the workaround is to just rename the columns. Someone better at R than me could probably provide some generic approach. smiley

# Need a valid value for JAVA_HOME prior to calling library(RJDBC)
Sys.setenv(JAVA_HOME="C:\\Java\\jdk-8.0.322.6-hotspot\\jre")
library(RJDBC)
library(dplyr)

# Connect to IRIS – needs path to InterSystems JDBC JAR in your installation
drv <- JDBC("com.intersystems.jdbc.IRISDriver", "C:\\InterSystems\\IRIS\\dev\\java\\lib\\1.8\\intersystems-jdbc-3.3.0.jar","\"")
conn <- dbConnect(drv, "jdbc:IRIS://localhost:1972/USER", "IRIS Username", "IRIS Password")
dbListTables(conn)

# For maximum confusion, load the iris dataset :)
data(iris)

# IRIS doesn't like "." in column names, so rename. (Probably could code this generically but I’m not that good at R.)
iris <- iris %>% rename(sepal_length = Sepal.Length, sepal_width = Sepal.Width, petal_length = Petal.Length, petal_width = Petal.Width)

# dbWriteTable/dbGetQuery/dbReadTable just work
dbWriteTable(conn, "iris", iris, overwrite = TRUE)
dbGetQuery(conn, "select count(*) from iris")
d <- dbReadTable(conn, "iris")
4
0 135
Discussion (2)2
Log in or sign up to continue

A generic way to rename the columns would be as follows (I believe with this you no longer need to load the dplyr package):

names(iris) <- gsub("\\.", "_", names(iris))