How to use a variable name in a SQL 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.

Isn't dbGetQuery an R function? It looks like an R question, but you've got both tags. — pavium
– pavium, Commented Feb 2, 2010 at 7: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
– John, Commented Feb 2, 2010 at 7:21

Robert · Accepted Answer · 2010-02-02 13:29:39Z

8

This should work:

foo = 23;

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

dbGetQuery(con, sqlStatement;)

answered Feb 2, 2010 at 13:29

Robert

8486 silver badges9 bronze badges

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

3 Comments

Eduardo Leoni Over a year ago

I'd use paste("select surname from names WHERE age =",shQuote(foo),sep="")

John Over a year ago

thanks, this also works without the ' ' what are those for Robert? sqlStatement <- paste("select surname from names WHERE age =",foo,sep="")

Robert Over a year ago

@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 ".

Community · Accepted Answer · 2017-05-23 10:27:14Z

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.

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Feb 2, 2010 at 14:02

Shane

100k35 gold badges229 silver badges220 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:27:14Z

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

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered May 13, 2015 at 16:09

David Marx

8,6183 gold badges51 silver badges70 bronze badges

Comments

Sarfraz · Accepted Answer · 2010-02-02 07:04:55Z

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

answered Feb 2, 2010 at 7:04

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

3 Comments

John Over a year ago

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.

Sarfraz Over a year ago

what do dot character mean '.foo.'?

John Over a year ago

I don't know what the dots mean, I was trying a few different things from other languages I found using different formats.

LuI · Accepted Answer · 2010-02-02 07:12:55Z

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

answered Feb 2, 2010 at 7:12

LuI

491 bronze badge

Collectives™ on Stack Overflow

How to use a variable name in a SQL statement?

5 Answers 5

3 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related