1

Im extracting some data from a table using sql select statment in R, 

query <- "select * from MyTable where TimeCol='6/29/2012 21:05' ";
result <- fn$sqldf(query);

The above code gives correct results, but when the time value is saved in variable, it doesn't works 

mytime <- "6/29/2012 21:05";

query <- "select * from MyTable where TimeCol = $mytime";     # OR

query <- "select * from MyTable where TimeCol = $[mytime]";   # OR

query <- "select * from MyTable where TimeCol = '$[mytime]' ";

result <- fn$sqldf(query);

None of the above three lines is working

View(result) it gives the error: invalid 'x' argument

RM Faheem
  • 68
  • 11
  • Perhaps have a look at [this answer](http://stackoverflow.com/a/2184318/1281189) – BenBarnes Oct 23 '12 at 11:34
  • 1
    The string "it doesn't works [sic]" is not a built-in error message in R. –  Oct 23 '12 at 11:43
  • @BenBarnes im using sqldf query in R to access R dataframes, the link you referred uses dbGetQuery, but it doesn't works for sqldf – RM Faheem Oct 23 '12 at 12:13
  • @Jack Maney i've written the error above 'invalid 'x' argument' means sqldf query doesn't returning any thing – RM Faheem Oct 23 '12 at 12:15

2 Answers2

1

$[] and $() are not valid syntax and the quotes that were around the time string in the first instance of query in the post are missing in the subsequent instances so a correct version would be:

library(sqldf)

mytime <- "6/29/2012 21:05"
MyTable <- data.frame(TimeCol = mytime)
query <- "select * from MyTable where TimeCol = '$mytime' "  

fn$sqldf(query)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • Ah, OK. I didn't read between enough lines. It wasn't just incorporating variables into SQL statements, but using the specific functionality of gsubfn in conjunction with sqldf. Downvote understood (the downvote on the Q wasn't from me, though). – BenBarnes Oct 23 '12 at 16:08
0

Although the answer I linked to in a comment uses a different function to query the data.frame, the principle is the same: paste the variable to the rest of your select string, ensuring quotes are included when necessary (using shQuote), then pass that character string to your sql querying function of choice.

query <- paste0("select * from MyTable where TimeCol = ", shQuote(mytime))

result <- fn$sqldf(query)

The semicolons at the ends of your lines probably aren't necessary.

As Joran mentions in a comment, sprintf could also be used (perhaps with a gain in readability in case there are many variable components in your query string):

sprintf("select * from MyTable where %s = '%s'", "TimeCol", mytime)
# [1] "select * from MyTable where TimeCol = '6/29/2012 21:05'"
BenBarnes
  • 19,114
  • 6
  • 56
  • 74