1. CData Virtuality
  2. How-To Guides
  3. Connecting Frontends and Applications

Articles in this section

Connecting R via JDBC

Avatar
Permanently deleted user
  • Updated
Follow

Install R with JDBC capabilities

Ubuntu Linux

apt-get install openjdk-7-jdk r-base r-cran-rjava r-cran-dbi
R CMD javareconf
R
install.packages('RJDBC')

Oracle Java does not work with the precompiled Ubuntu packages. If you want to use Oracle Java and rJava, dbi should be compiled directly with install.packages

Windows

In windows install the RJDBC package with the GUI. Please ensure to have java installed before.

Please be sure, that the jvm.dll is found by your R installation. It can be done by adding

C:\Program Files\Java\jdk1.6.0_25\jre\bin\server for jdk

and

C:\Program Files\Java\jre6\jre\bin\client for jre

to your PATH

For further Questions see http://stackoverflow.com/questions/7019912/using-the-rjava-package-on-win7-64-bit-with-r.

Querying from R

To Connect Datavirtuality place the JDBC driver somewhere. 

Assuming the Datavirtuality JDBC Driver is located on in /home/user/jdbc/datavirtuality-jdbc.jar

require(RJDBC)
drv <- JDBC("com.datavirtuality.dv.jdbc.Driver", "/home/user/jdbc/datavirtuality-jdbc.jar")
conn <- dbConnect(drv, "jdbc:datavirtuality:datavirtuality@mm://dvhost:31000", "admin", "admin")

Examples:

dbListTables(conn)
dbGetQuery(conn, 'select foo from schema.bar')

Parse timestamp to be able to use it in R plots

strptime(datecolumn, "%Y-%m-%j %H:%M:00.0")

Related articles

Comments

4 comments

Sort by Date Votes
  • Avatar
    Javier Gutierrez

    Hi Julian,

    For me it only works adding this to the string:

    "jdbc:datavirtuality:datavirtuality@mms://dvhost:31000;SHOWPLAN=ON"

    Does it also work for you without the 's'? 

    0
    Comment actions Permalink
  • Avatar
    Martin Lutzemann
    • Edited

    Hey Javier,

    you can use a JDBC connection with and without SSL-security and this is where the mm/mms part from the connection url comes from. You have

    jdbc:datavirtuality:datavirtuality@mm://localhost:31000; for unsecured connections and

    jdbc:datavirtuality:datavirtuality@mms://localhost:31001; for SSL-secured connections.

    Using mms but not the SSL port (default 31001) should result in an error saying that the SSL message was not recognized. Basically, the same combination of Port and SSL setting that works for the Studio connection will work for the JDBC connection in R as well.

    Cheers

    1
    Comment actions Permalink
  • Avatar
    Chris B.

    Hi Julian,

    RJDBC stopped working in both linux and windows environments. The code was working fine but now I got the error when trying to open a connection to a database.

    R Version 3.6.1
    RJDBC 0.2-7.1
    rJava 0.9-11

    Error in .jcall(p, "Ljava/lang/Object;", "setProperty", "password", password) :
    method setProperty with signature (Ljava/lang/String;D)Ljava/lang/Object; not found

    Any ideas?

     

    0
    Comment actions Permalink
  • Avatar
    Carlos Klapp
    • Edited

    I tested connectivity with the following script and it is working correctly. I'm using the 2.4.16 version of the JDBC driver on Windows.

    require(RJDBC)
    drv <- JDBC("com.datavirtuality.dv.jdbc.Driver", "C:\\_DV_bin_linux\\jdbc\\datavirtuality\\datavirtuality-jdbc.2.4.16.jar")
    conn <- dbConnect(drv, "jdbc:datavirtuality:datavirtuality@mm://cwk.vm:31000", "admin", "admin")
    dbListTables(conn)
    dbGetQuery(conn, 'SELECT * FROM "mssql_advworks_2019.AdventureWorks2019.HumanResources.Department" LIMIT 500;;')
    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk