Pass R variable to a sql statement

Question

Is there any way to pass a defined variable in R to the SQL statement within the sqldf package?

i have to run the code below and I passed the 'v' variable to sql select statement as '$v'

 for (i in 1:50){
          v <- i+ 450
          temp <- sqldf("select count(V1) from file_new where V1='$v' ")
        }

Although it runs, it returns wrong result. [The result should be 1000 but this code returns 0].

Hence, I think it doesn't pass the variable value.

See Example 5 on the sqldf home page: https://code.google.com/p/sqldf/#Example_5._Insert_Variables — G. Grothendieck, Apr 04 '14 at 11:53

score 2 · Accepted Answer · 2014-04-04T11:56:31.343

2

If v is an integer then you don't want to enclose the $v with single quotes - that makes it a string value. Try without the single quotes.

temp <- fn$sqldf("select count(V1) from file_new where V1=$v ")

edited Apr 04 '14 at 11:56

answered Apr 04 '14 at 11:49

Yes, it is an integer. I tried it before but i got an error of not recognizing the $ symbol! and now, I ran it one more time and i got this error: Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (incomplete data binding: expected 1 parameters, got 0) – MASUMEH Apr 04 '14 at 11:52
you need to invoke it with fn$sqldf to get the interpolation – Apr 04 '14 at 11:55

1 Answers1