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

[Gabor Grothendieck]

x <- 1; y <- 2

# 1
paste("x is", x, "y is", y)

# 2
sprintf("x is %d, y is %d", x, y)

# 3
library(gsubfn)
fn$cat("x is $x, y is $y\n")

For the last one see http://gsubfn.googlecode.com
If we preface a function by fn$ then it interpolates
strings subject to some qualifications, e.g.

fn$sqlQuery(ch, "select * from myTable where pos1 = $x and pos2 = $y")


On Mon, May 25, 2009 at 4:26 PM, John Fitzgerald
<john.fitzgerald at internet.de> wrote:
>
> 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
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>