[R] Interpolating variables within (RODBC library) SQL statements for MySQL

[John Fitzgerald]

Hi everyone,

I am desperately looking for a method to interpolate strings within an SQL
statement as follows:
I get a lot of rows out of a database (in my example POSITION_to_ZIPCODE
Database with holds records for German ZIP Code <--> Gauss-Krueger
Coordinate System ) and want this to be selected and computed individually
row by row as follows:

library(RODBC)
channel <- odbcConnect("database")
pos_to_zip <- sqlQuery(channel, "select YPOS, XPOS FROM
POSITION_to_ZIPCODE;")
my_row <- pos_to_zip[1,]       # get the first element (change with next
ones afterwards)
ypos <-my_row[1]               # get the first y-position xpos <-my_row[2] #
get the first X-Position

So far, so good: SQL Select works and everybody is fine, but this was just
the preparation for the next step: Select all data out of the database with
e.g. ypos = 1 and xpos = 182...

rawdata <- sqlQuery(channel, "select YPOS, XPOS, YEAR, MONTH, RANGE, APFEL,
TEMPMIN, TEMPAVG, TEMPMAX, PRECIPITATION, SUNDURATION from DATA where
YPOS='ypos' AND XPOS='xpos' AND MONTH BETWEEN 1 AND 12 AND RANGE = 1;")

The problem is that I want to use variables (xpos, ypos) to be replaced by
the numbers (much more of them) so the SQL String would be:

rawdata <- sqlQuery(channel, "select YPOS, XPOS, YEAR, MONTH, RANGE, APFEL,
TEMPMIN, TEMPAVG, TEMPMAX, PRECIPITATION, SUNDURATION from DATA where YPOS=1
AND XPOS=182 AND MONTH BETWEEN 1 AND 12 AND RANGE = 1;")

Since I can't use any $xpos oder $ypos (like in Perl) within R, I'm stuck.
I've tried some "xpos" and some 'ypos', some $xpos and some "$xpos" but
those would not work either. 

So how can I set and retrieve variables in an SQL String in R?

 

Kind regards,

John Fitzgerald