CODEWITHSUNDEEP
rsqldf
Knowledge Seeker
Knowledge Seeker

Reputation: 566

Warning: Error in result_create: no such column: tmp

I'm running the database query by using sqldf in Shiny in R. But getting error.

ui.R:

  observeEvent (input$uploadForTest_1, {
    inFile=input$uploadForTest_1
    inFileName=input$uploadForTest_1$name
    file <-"tss.txt"
    tmp = paste("audio/street", inFileName, sep = "/")
    res <- read.csv.sql(file,header=FALSE,sql = "select * from file where V1=tmp",sep="\t")
    print(res)
  })

I'm successfully running the following query:

res <- read.csv.sql(file,header=FALSE,sql = "select * from file where V1='audio/street/b098.wav'",sep="\t")

But, if I run the query that is mentioned in ui.R it is giving me error that tmp column doesn't exists:

Warning: Error in result_create: no such column: tmp 86:

I dont want to use string in my query. I want to use variable name. Because I don't want to hard code string in query. Can I use variable name in query instead of string. If yes, then how can I do this? I didn't find solution to my problem in Internet. Thanks.

Upvotes: 0

Views: 315

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269431

Preface read.csv.sql with fn$, and use '$tmp' in the SQL statement.

fn$read.csv.sql(file, sql = "select * from file where V1 = '$tmp'", 
  header = FALSE, sep = "\t")

See ?fn and the gsubfn vignette for more info. Note that sqldf automatically loads the gsubfn package so it will already be available.

Upvotes: 2

Roman Luštrik
Roman Luštrik

Reputation: 70623

You could use sprintf. Another option would be to paste together a string, but I find sprintf far more elegant for this task.

> tmp <- "audio/street/somefile.txt"
> tmp <- "audio/street/somefile.txt"
> "select * from file where V1=tmp"
[1] "select * from file where V1=tmp"

> sprintf("select * from file where V1='%s'", tmp)
[1] "select * from file where V1='audio/street/somefile.txt'"

Upvotes: 1

Related Questions