How to use a variable name in a mySQL statement?

Question

I'm using R to call a mySQL statement, where I define the variable outside the statement e.g.

foo = 23;
dbGetQuery(con, "select surname from names WHERE age = '.foo.' ;")

But this returns an empty set, I've googled around and tried'.&foo.' ".foo." '".&&foo."' and many different combinations, but none of them work, I think this should be a mysql question rather than an R specific problem I'm having, but not sure. Normally variables have $values but not in R.

Answer 1

Adding the semi-colon at the end of query sometimes creates problem. Try changing your query from:

dbGetQuery(con, "select surname from names WHERE age = '.foo.' ;")

to:

dbGetQuery(con, "select surname from names WHERE age = '.foo.'")

Answer 2

AFAIK the command has to be a string, so you should append the single components. Not being familiar with R I cant help you out HOW to do that. In MS-VBA the string concatenation operator is '&'.

Answer 3

This should work:

foo = 23;

sqlStatement <- paste("select surname from names WHERE age =",foo,'"',sep="")

dbGetQuery(con, sqlStatement;)

Answer 4

You may want to look at the answers to this question: Can I gracefully include formatted SQL strings in an R script?.

The simplest solution is to use the paste command as Robert suggested.