MonetDB connect to GO.db within R code that is run in-database

Question

I am trying to run some R code in database. Most of it is going pretty well, but I seem to have stumbled on a bug. I cannot load the following package, which is a dependency for some of my code. WGCNA

I have been chasing it down, and it seems to be due to an error when trying to load GO.db.

I get the following error:

Error in .local(conn, statement, ...) : 
  Unable to execute statement 'SELECT * FROM tmp_test();'.
Server says '!Error running R expression. Error message: Error in as.data.frame((function() { : '.

Digging a bit further it seems to be due to the following statement:

dbconn <- dbFileConnect(dbfile)

Error can be reproduced using:

functionDef <- paste(
"CREATE FUNCTION tmp_test() ",
"RETURNS TABLE(output STRING)",
"LANGUAGE R ",
"{", 
"library(AnnotationDbi)",
"datacache <- new.env(hash=TRUE, parent=emptyenv())",
"pkgname <- 'GO.db'",
"libname <- .libPaths()[1]",
"dbfile <- system.file('extdata', 'GO.sqlite', package=pkgname, lib.loc=libname)",
"assign('dbfile', dbfile, envir=datacache)",
"dbconn <- dbFileConnect(dbfile)",
"};", sep = "\n")

dbGetQuery(conn, functionDef)

dbGetQuery(conn, "SELECT * FROM tmp_test();")

By the way, installing GO.db from within MonetDB works just fine. And can be done using the following R code:

source("https://bioconductor.org/biocLite.R")
biocLite("GO.db")

Hints on how to resolve this are greatly appreciated.

As to what datacache is supposed to do here, this was part of my debugging efforts. The code is part of zzz.R in the GO.db package. Another way to get this error is trying to load the GO.db package.

I tried the code from Hannes Mühleisen and I get the following result: I restarted monetdbd first.

dbGetQuery(conn, "SELECT * FROM tmp_test()") QQ: 'SELECT * FROM tmp_test()' Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT * FROM tmp_test()'. Server says '!Error running R expression. Error message: Error in dbConnect(SQLite(), dbname = dbfile, cache_size = 64000, synchronous = "off", : '.

Then I just tried again and got this error:

dbGetQuery(conn, "SELECT * FROM tmp_test()") QQ: 'SELECT * FROM tmp_test()' Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT * FROM tmp_test()'. Server says '!Error running R expression. Error message: Error in as.data.frame((function() { : '.

After I restart monetdbd I can reproduce this cycle.

Hannes Mühleisen Hannes Mühleisen · Accepted Answer · 2016-05-19T08:16:58

I got this to work as follows: First, I simplified the example code, removing the apparently unused datacache and using system.file instead of manually searching for the file name. Second, your function needs to actually return what you defined in the schema, in this case a STRING.

functionDef <- "CREATE FUNCTION tmp_test() RETURNS TABLE(output STRING) LANGUAGE R {
  library(AnnotationDbi)
  dbfile <- system.file('extdata/GO.sqlite', package='GO.db')
  dbconn <- dbFileConnect(dbfile)
  return('Hello, World')
}"

Assuming the GO.db and AnnotationDbi packages are installed, this works fine for me:

> dbGetQuery(conn, "SELECT * FROM tmp_test()")
        output
1 Hello, World

MonetDB connect to GO.db within R code that is run in-database

1 Answers