Error in using variables in SQL statements [duplicate]

Question

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

The string "it doesn't works [sic]" is not a built-in error message in R. — user554546
– user554546, Commented Oct 23, 2012 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
– RM Faheem, Commented Oct 23, 2012 at 12:13
@Jack Maney i've written the error above 'invalid 'x' argument' means sqldf query doesn't returning any thing — RM Faheem
– RM Faheem, Commented Oct 23, 2012 at 12:15

G. Grothendieck · Accepted Answer · 2012-10-23 15:27:18Z

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)

edited Oct 23, 2012 at 15:27

answered Oct 23, 2012 at 15:19

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BenBarnes Over a year ago

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 · Accepted Answer · 2012-10-23 12:48:44Z

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'"

edited Oct 23, 2012 at 12:48

answered Oct 23, 2012 at 12:35

BenBarnes

19.5k6 gold badges60 silver badges75 bronze badges

2 Comments

joran Over a year ago

sprintf is another useful option for this sort of thing.

BenBarnes Over a year ago

@joran, good point. Added as alternative.

Collectives™ on Stack Overflow

Error in using variables in SQL statements [duplicate]

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related