2

Shortly, I am using JDBC and I am trying to write a query that will return some values from an SQL developer db table.

So far i have this:

#Query for getting data
sql <- paste("select * 
           FROM GRID Z
           where Z.LAT = Xlat AND Z.LON = Xlon")
fun <- dbGetQuery(jdbcConnection, sql)
attach(fun)

Problem is that Xlat and Xlon are variables in R and their values change frequently so I can't really hard-pass them into the query. Apparently, Z.LAT and Z.LON correspond to GRID table.

Question is: Is it possible to use R variables into a query as such?

I would also like to know if instead of = is there something to match the closest or nearest values.

Appreciate any suggestions. Thanks

Edit: Another approach to this would be to SELECT * from my table, and then 'play' with fun in order to get my values. Any thoughts or practices on that?

Note: jdbcConnection is asking for a remote connection.

Improve this question
4
  • The commenter who has now disappeared is right - Why tag this with SQL Server? Or, for that matter, oracle-sqldeveloper? There is no such thing as a 'SQL developer table': It is clearly a question about Oracle and JDBC so tag it as such and you'll get relevant answer much more quickly on the whole. It also helps anyone who comes after looking for the same sort of question. Commented May 9, 2014 at 11:33
  • @StevePettifer its "sql developer db table". notice the db. And that is why people can edit other people questions, in order to help them do that. he just clicked and went on. that's not legit as far as i know Commented May 9, 2014 at 11:38
  • There's still no such thing as "sql developer db table" because SQL Developer is just and IDE not an RDBMS. I agree he could (and should) have edited or elucidated more but he didn't do anything 'wrong' as such. The more accurate the information you enter (including the version of the RDBMS in question), the better the answer tends to be (and quicker of course). Commented May 9, 2014 at 12:00
  • @StevePettifer i hardly know the differences between the syntax of each and every rdbms and that is normal. i knew however there are some and that's why i precised the sql dev db table. "he didn't do anything 'wrong as such'" doesn't make sense. anyway thanks for your feedback Commented May 9, 2014 at 12:11

4 Answers 4

Reset to default
2

Are you looking for this?

sql <- paste0("select * 
           FROM GRID Z
           where Z.LAT ='", Xlat,"' AND Z.LON = '", Xlon,"'")

I assumed that your variables are character. In case the above is running behind a web server, there are options for URL encode and escape to avoid code injections... like this

EDIT: About this:

I would also like to know if instead of = is there something to match the closest or nearest values.

Since you are executing your query via a SQL engine that is more a SQL question than a R one. Like @Vivek says you can do that in sqldf but I guess your data are in a remote database, so it wouldn't help in this case.

All SQL flavours have like, so just use it in your query. Please tell me if I'm misunderstanding your question.

sql <- paste0("select * 
           FROM GRID Z
           where Z.LAT like '", Xlat,"' AND Z.LON like '", Xlon,"'")
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

yes i am asking for a remote connection. Why sqldf wouldn't work? Nope you got this right. Can you please edit your answer with like? I am not really familiar with sql syntax and all. thanks a ton
sqldf allows you to use sql syntax to R object. Which means that the object MUST be in the R environment, there is no connection to a remote DBMS.
@KapelNick Edited with like
it seems to work fine, however for some reason it doesn't return any values.
@KapelNick it means there is no data probably. The query syntax is correct, but I can tell about the meaningfulness of the query
2

gsubfn's fn$ operator supports a quasi-perl sort of string interpolation like this:

library(gsubfn)

sql0 <- "select * FROM GRID Z where Z.LAT = $Xlat AND Z.LON = $Xlon"
fun <- fn$dbGetQuery(jdbcConnection, sql0)

or like this to allow examination of sql after substitution:

sql0 <- "select * FROM GRID Z where Z.LAT = $Xlat AND Z.LON = $Xlon"
sql <- fn$identity(sql0)
fun <- dbGetQuery(jdbcConnection, sql)

See ?fn and also the examples on the sqldf home page.

Improve this answer

2 Comments

i am mostly interested in finding the nearest value, assuming = won't be applicable.
Regarding your second question see: docs.oracle.com/cd/E17781_01/appdev.112/e18750/xe_locator.htm
1

yes it is certainly, possible see library sqldf. you could use for approximate match Z.LAT like 'Xlat'

data(CO2)

var1<-"Plant"
var2<-"conc"

require(sqldf)
squery2<-paste("select * from CO2 where",var1,"like 'Qn%'","and",var2,"> 500",sep=" ")

> sqldf(squery2)
  Plant   Type  Treatment conc uptake
1   Qn1 Quebec nonchilled  675   39.2
2   Qn1 Quebec nonchilled 1000   39.7
3   Qn2 Quebec nonchilled  675   41.4
4   Qn2 Quebec nonchilled 1000   44.3
5   Qn3 Quebec nonchilled  675   43.9
6   Qn3 Quebec nonchilled 1000   45.5
Improve this answer

2 Comments

according to @Michele sqldf needs to be used with something in local. I am using a remote database, so if it's true i cant really use it. i'll edit my question to reflect that. thanks
the sqldf demo was for illustration, my point was to show the construction of the query, which is same for local or remote database
-1

You can also use this - Suppose,

 Xlat <- 8
 Xlon <- 10

Then,

 #Query for getting data
 sql <- paste("select * 
       FROM GRID Z
       where Z.LAT = $Xlat AND Z.LON = $Xlon")
 fun <- dbGetQuery(jdbcConnection, sql)
 attach(fun)
Improve this answer

6 Comments

Are you sure that would work? It seems PHP syntax to me...
i am afraid that won't work. $ is valid in R but Sql does not recognize it..
The sqldf package doc says it works - code.google.com/p/sqldf See - Example 5. Insert Variables
I tried $ operator therefore i know as a fact it doesn't.
@RHelp the only way to do something similar in R (in reality) is using sprintf. E.g. sprintf("%s %d", "test", 1:3)
|