0

Consider the following minimal example:

DB1<-data.frame(x=c(1,2,3))

out1<- sqldf("SELECT x FROM DB1") 


test <- function (db,column){
  fn$sqldf("SELECT column FROM db") 
}

out2<-test(DB1,x)

out1 is fine but when I want to generalise this as a function then I get the error: object 'x' not found.

Removing fn$ didn't seem to help. I can see that the issue may be that the object db is a data frame whereas x is a string, although it needs to be an argument within the definition.

Geoff
  • 925
  • 4
  • 14
  • 36
  • letters inside strings are not arguments. You can `paste()` together a query string, e.g., `db <- "DB1"`, `"col <- x"` and `query <- paste("SELECT", col, "FROM", db)`. – Gregor Thomas Aug 22 '22 at 13:22
  • 1
    And I have no idea where you got `fn` from or what you expect it to do here. – Gregor Thomas Aug 22 '22 at 13:23
  • @GregorThomas I believe the `fn$` stuff come from the [gsubfn](https://cran.r-project.org/package=gsubfn) package. – MrFlick Aug 22 '22 at 13:24
  • See also the base function `sprintf` or the `glue` package for potentially more friendly ways of creating query strings. – Gregor Thomas Aug 22 '22 at 13:25
  • @GregorThomas The fn$ came from a comment on another question made by (who I believe to be) the package author https://stackoverflow.com/q/38421343/1154853 – Geoff Aug 22 '22 at 17:52
  • Ah, I see. MrFlick is right, it's from the `gsubfn` package [which is loaded by sqldf](https://github.com/ggrothendieck/sqldf#example-5-insert-variables). – Gregor Thomas Aug 22 '22 at 18:01

3 Answers3

2

Use $column in the sql string and pass the column as a character string. The [...] is in case the column name has a dot in it which is accepted by R but must be escaped to use it in SQL.

(Despite the fact that tidyverse does it it is generally not a good idea to use nonstandard evaluation and using it will make your software harder to write and to use. That means we should pass "x" instead of x. A key advantage is that then it becomes easy to store the variable name itself in a variable, as opposed to hard coding it, which may be desirable when writing functions which should be general.)

library(sqldf)

test2 <- function (db, column) {
  fn$sqldf("SELECT [$column] FROM db") 
}

DB1 <- data.frame(x = c(1, 2, 3))
test2(DB1, "x")
##   x
## 1 1
## 2 2
## 3 3

If you did want to use NSE anyways, despite my warning, then it would be like this.

library(sqldf)

test3 <- function (db, column) {
  column <- deparse(substitute(column))
  fn$sqldf("SELECT [$column] FROM db") 
}

DB1 <- data.frame(x = c(1, 2, 3))
test3(DB1, x)
##   x
## 1 1
## 2 2
## 3 3
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
1

You need to call the function using strings, and paste those strings together to get a correct query:

library(sqldf)
DB1<-data.frame(x=c(1,2,3))
out1<- sqldf("SELECT x FROM DB1") 

test <- function (db,column){
  sqldf(paste("SELECT", column, "FROM", db)) 
}

out2<-test("DB1", "x")
VvdL
  • 2,799
  • 1
  • 3
  • 14
1

You can use match.call to get the function call. If you remove the first element which is the function, you have a list of quoted arguments which you can convert to character strings with as.character. Once you have this list of named character values you can use that as the environment for glue and surround the parts you want substituted from this environment with {}.

library(sqldf)
#> Loading required package: gsubfn
#> Loading required package: proto
#> Loading required package: RSQLite
#> Warning: package 'RSQLite' was built under R version 4.1.2
library(glue)
#> Warning: package 'glue' was built under R version 4.1.2

DB1 <- data.frame(x = c(1, 2, 3))

test <- function(db, column) {
  to_sub <- lapply(match.call()[-1], as.character)
  sqldf(glue("SELECT {column} FROM {db} where {column} < 3", .envir = to_sub))
}

test(DB1, x)
#>   x
#> 1 1
#> 2 2

Created on 2022-08-22 by the reprex package (v2.0.1.9000)

IceCreamToucan
  • 28,083
  • 2
  • 22
  • 38