3

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.

Matthew Lundberg
  • 42,009
  • 6
  • 90
  • 112
John
  • 5,139
  • 19
  • 57
  • 62
  • Isn't `dbGetQuery` an R function? It looks like an R question, but you've got *both* tags. – pavium Feb 02 '10 at 07:06
  • Yes, you are right, I remove the mysql tag. I think the correct query is something like this, where the var goes a t the end: dbGetQuery(con, "select od from plate_data WHERE col = '%s'", foo) But this doesn't work either.. – John Feb 02 '10 at 07:21

5 Answers5

8

This should work:

foo = 23;

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

dbGetQuery(con, sqlStatement;)
Robert
  • 838
  • 6
  • 8
  • I'd use paste("select surname from names WHERE age =",shQuote(foo),sep="") – Eduardo Leoni Feb 02 '10 at 22:00
  • thanks, this also works without the ' ' what are those for Robert? sqlStatement <- paste("select surname from names WHERE age =",foo,sep="") – John Feb 03 '10 at 04:59
  • @John. You're correct. The paste function already appends " for you so you don't need '"'. In some instances, when working with strings that have " in them, you can use single ' to wrap the string. This is very useful when dealing with the XML package where strings may have many nested ". – Robert Feb 03 '10 at 14:18
2

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.

Community
  • 1
  • 1
Shane
  • 98,550
  • 35
  • 224
  • 217
1

The accepted answer gives bad advice which leaves your application vulnerable to SQL injection. You should always use bind variables instead of concatenating values directly into your query. Use the dbGetPreparedQUery method as described in this answer: Bind variables in R DBI

Community
  • 1
  • 1
David Marx
  • 8,172
  • 3
  • 45
  • 66
0

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.'")
Sarfraz
  • 377,238
  • 77
  • 533
  • 578
  • Thanks, but it still returns "data frame with 0 columns and 0 rows" where as if I put 23 instead of foo it works fine. – John Feb 02 '10 at 07:08
  • what do dot character mean '.foo.'? – Sarfraz Feb 02 '10 at 07:10
  • I don't know what the dots mean, I was trying a few different things from other languages I found using different formats. – John Feb 02 '10 at 07:15
0

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 '&'.

LuI
  • 49
  • 1