rJava fails while invoking RJDBC library from OpenCPU API

Question

Goal : To use RJDBC with OpenCPU AJAX Post call.

Use case : I need to connect with Apache phoenix database and get some data. And on that data I am supposed to do analytics. So to connect with Phoenix i am using rjdbc(phoenix doesn't support any other option to connect).

Environment : public.opencpu.org

Here is what I am trying:

html

<textarea>
library(RJDBC)
.. do some data fetching and then r statistics
</textarea>
<br />
<button id="submitbutton" >Execute</button>
<br />
<br />
<div id="output">
</div>

javascript

<script type="text/javascript">
  //When Document is Ready
    $(function () {
        var baseurl = "https://public.opencpu.org";
        //because identity is in base
        ocpu.seturl(baseurl+"/ocpu/library/base/R");
        //actual handler
        $("#submitbutton").on("click", function(){
            //arguments
            var mysnippet = new ocpu.Snippet($("#input").val());
            //perform the request
            var req = ocpu.call("identity", {
                "x" : mysnippet
            }, function(session){
                session.getStdout(function(outtxt){
                        $("#output-txt").text(outtxt);   
                });
            });   
            //if R returns an error, alert the error message
            req.fail(function(){
                alert("Server error: " + req.responseText);
                $("#output").hide();
            });      
            req.always(function(){
                $("button").removeAttr("disabled");  
            });
        });    
    });

</script>

scenario

when i click execute button, gets the user entered value from text area ( here library(RJDBC) and some db operations) and sends a ajax post and responds back with the below error

google chrome console log :

POST https://public.opencpu.org/ocpu/library/base/R/identity 400 (Bad Request) 
OpenCPU error HTTP 400
package 'rJava' could not be loaded

Hope this gives better clarity.

Need to confirm whether rJava works with OpenCPU, if so any possible work around to fix this?

Answer 1

The core of my solution looks like the below. The code is taken from an R6 class that has some logging. For the sake of documenting what is happening I've left in the private$log commands, but if running a version of this code you'd want to leave it out. Similarly, there are calls there to functions that you don't really need to get the job done and I pack up the host information in a list rather than pass individual variables for the passwords etc etc. In short, the code I provide here is not minimally replicable.

However, the code I provide at this gist ought to be somewhat replicable (once you provide the database connection, the classPath and driverClass). That gist has been subject to about 0 clean-up, so there is a ton of code in there that is strictly speaking superfluous to getting the job done. None of this code is pretty, elegant, etc - generally I'm ashamed of it, but in the spirit of sharing, I put it up anyway.

Explanatory code segment:

        jvmNode <- makeCluster(1, methods=FALSE, port = sample(11000:11999,1), rscript_args="--vanilla")
          connected <- isTRUE(try(clusterEvalQ(jvmNode, 2)==2, silent=TRUE))
        private$log$finest("forkable node started")
        clusterEvalQ(jvmNode,{library(DBI);library(rJava);library(RJDBC)})

        clusterEvalQ(jvmNode, systemRAMFree <- function() {
  #in GB, platform dependent 
  as.numeric(system('FREE_KB=$(($(echo `sed -n \'2p;3p;4p\' <  /proc/meminfo | sed "s/ \\+/ /g" | cut -d\' \' -f 2 ` | sed "s/ /+/g")));echo $FREE_KB', intern=TRUE))/1024/1024
})       
        clusterEvalQ(jvmNode, options(java.parameters = paste0("-Xmx",systemRAMFree(),"g")))

        hostInformation <- private$hostInformation
        classPath <- "~/redshift/redshiftJDBC41.jar"
        driverClass <- private$driverClass
        java.parameters <- getOption("java.parameters")
        toExportList <- list(statement,driverClass, classPath, hostInformation, java.parameters)
        names(toExportList) <- c("statement","driverClass","classPath", "hostInformation", "java.parameters")
        toExport <- as.environment(toExportList)
        clusterExport(jvmNode, ls(toExport), envir=toExport)
        clusterEvalQ(jvmNode, options(java.parameters = java.parameters))

        clusterEvalQ(jvmNode, rJava::.jinit())
        private$log$finest("JVM Started")
        clusterEvalQ(jvmNode, drv <- RJDBC::JDBC(driverClass, classPath))
        private$log$finest("Driver obtained")

        clusterEvalQ(jvmNode, conn <- dbConnect(drv, 
                                       url = hostInformation$host, 
                                       user = hostInformation$user, 
                                       password = hostInformation$password))
        private$log$debug(logPrefix("query:", statement))
        result <- try(clusterEvalQ(jvmNode, dbGetQuery(conn, statement))[[1]])
        clusterEvalQ(jvmNode, dbDisconnect(conn))
        stopCluster(jvmNode)