0

I'm trying to more seamlessly integrate my use of R and SQL. The good news is I have the connection from R to db2 working well and have been using the RJDBC package to do so.

I've also been using the dbGetQuery() function that includes the sql query. For very simple queries this is all good.

cr_tb1 <- dbGetQuery(conn, "SELECT * FROM LWFILES001.EVETRNPF LIMIT 100")

However, I want to keep complex sql queries out of the R code and have them saved as a separate .sql that the function can reference.

I've loaded the squr library https://github.com/smbache/squr to facilitate this reference. So, I've tried something like this, but get an error.

library(RJDBC)
library(squr)

drv <- JDBC("com.ibm.as400.access.AS400JDBCDriver",
            "C:/temp/jt400.jar",
            identifier.quote="`")
conn <- dbConnect(drv, "jdbc:as400://lwsi.XXXX.local/XXXXXX", "username", "password")

case_rate_query <- sq_file("SQL//test.sql")

cr_tb1 <- dbGetQuery(conn, case_rate_query)

Here is the error:

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbGetQuery’ for signature ‘"JDBCConnection", "sq"’

Thank You for your help.

mr_puddles
  • 97
  • 7
  • 1
    Have you tried using `rmarkdown` to execute SQL? Also, this question might be of interest to you: https://stackoverflow.com/questions/44853322/how-to-read-the-contents-of-an-sql-file-into-an-r-script-to-run-a-query. – Giovanni Colitti Feb 25 '20 at 18:40
  • 1
    Need to check if that package is compatible with `DBI::dbGetQuery`. Looks like it works off `RODBC` (not part of DBI packages). It does not appear `sq_file` returns a character vector of length 1 which is required of `dbGetQuery` for second param – Parfait Feb 25 '20 at 18:42

1 Answers1

0

Thanks to comments by Giovanni and Parfait I found a solution.

cr_tb1 <- dbGetQuery(conn, statement = read_file('SQL//test.sql'))
mr_puddles
  • 97
  • 7