Connecting R to CCI Database

Need help connecting R to Oracle? You’ve come to the right place!

Congrats! Using R is great.
Follow these steps below and you’ll be working with CCI data in clean data frames in no time. If you have any questions, feel free to reach out to zalexander@nycourts.gov. I’d be happy to help at any point.

Step 1: Go to the console and run "install.packages(‘RJDBC’)
Step 2: Run the code chunk below:

options(java.parameters = "-Xmx8g")
memory.limit(size=10000000000024)

## [1] 1e+13

require(RJDBC)

After running this chunk, if you get the following output, you’ll have to do some extra work:

Fixing rJava

To fix this, please do the following:

Go to this website: https://www.oracle.com/java/technologies/javase-jdk14-downloads.html and download the “Windows x64 Compressed Archive” (jdk-14.0.1_windowns-x64_bin.zip) file. The versioning may change from 14.0.1 to something else, no worries if that’s the case, it should still work!
Once it’s downloaded, extract the zip folder and save it to your Desktop.
Then, run the following command in your console (however, change the file path to point to where your jdk folder lives):
Re-run the code chunk above, it should be working at this point.

Connect to JCA SQL Database

Step 3: Download the Oracle Jar Driver
- Head over to this website here: https://www.oracle.com/database/technologies/jdbcdriver-ucp-downloads.html
- Download the ojdbc6.jar file
- When it’s finished downloading you’ll need to unzip the download and copy/paste the odjbc6.jar file to a folder on your local drive

I put the file on my desktop, in a folder called “Oraclejar”, so the file path for me is: + C:/Users/zalexander/Desktop/Oraclejar/ojdbc6.jar

Step 4: Change the classPath argument below to the file path where your ‘ojdbc6.jar’ file lives.
Step 5: Run the code chunk below.

jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="C:/Users/zalexander/Desktop/Oraclejar/ojdbc6.jar")

Connect to DB

Step 6: Uncomment the line below, add CCI’s database username and password into the dbConnect() function as arguments. After the jdbc:oracle… string, it’ll be a comma and then “XXXX (username) XXXX” and then another comma “XXXX (password) XXXX”.

# jdbcConnection =dbConnect(jdbcDriver, "jdbc:oracle:thin:@//10.1.16.75:1521/PROD2")

You should now be connected to CCI’s database! The cool thing about the RJDBC package is that it has custom query functions built in. To read in a .sql query file, you can use this one below as an example. You can change/adjust your sql script to pull in the variables you’d like, just make sure the file is pointing to the right place. Once your query is saved as a variable, you can fun the final chunk.

Import SQL Query

Step 7: Uncomment the line below, change the file path for the my_query variable to point to the .sql file you want to use for your query.

# my_query <- 'R:/Research_Demonstration Projects/Felony ATI downtown/Data_Pulls_SQL/R_db_pull_sql.sql'
# my_query <- paste(readLines(my_query), collapse='\n')

Step 8: Uncomment the line below, run this chunk, wait a few seconds (or minutes, depending on the sql query), and you should see a new dataframe with your populated data from the database!

Save as Dataframe

# df <- dbGetQuery(jdbcConnection, my_query)