thr3ads.net - R help - [R] Interpolating variables within (RODBC library) SQL statements for MySQL [May 2009]

If this information is useful, please help other people find it:
Share via:

John Fitzgerald

2009-May-25 20:26 UTC

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

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

jim holtman

2009-May-25 20:55 UTC

head link

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

?paste

e.g.,

sql <- paste("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;",
sep="")

On Mon, May 25, 2009 at 4:26 PM, John Fitzgerald <
john.fitzgerald@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@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<http://www.r-project.org/posting-guide.html>
> and provide commented, minimal, self-contained, reproducible code.
>


-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?

	[[alternative HTML version deleted]]

Gabor Grothendieck

2009-May-25 21:38 UTC

head link

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

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

Stefan Uhmann

2009-May-27 11:43 UTC

head link

[R] file.move?

Dear list,

I want to move some files that should keep their time stamps, which is 
not the case if I use file.copy in combination with file.remove. 
file.move would be nice, is there a package providing such a function?

Regards,
Stefan

Apparently Analagous Threads

Search for more possibly parallel threads

R help - May 2009 - Interpolating variables within (RODBC library) SQL statements for MySQL

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

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

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

[R] file.move?

Apparently Analagous Threads