使用R变量进行SQL查询 [英] Use R variables to an SQL query

问题描述

很快，我正在使用JDBC，并且试图编写一个查询，该查询将从SQL开发人员数据库表中返回一些值.

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)

问题在于Xlat和Xlon是R中的变量，并且它们的值经常更改，因此我无法真正将它们硬传递给查询.显然，Z.LAT和Z.LON对应于GRID表.

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.

问题是:可以在查询中这样使用R变量吗?

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.

赞赏任何建议.谢谢

对此的另一种方法是从我的表中SELECT *，然后与fun一起玩"以获取我的值.有什么想法或做法吗?

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?

注意: jdbcConnection正在请求远程连接.

Note: jdbcConnection is asking for a remote connection.

推荐答案

您在寻找这个吗?

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

我假设您的变量是字符.如果上述代码在网络服务器后面运行，则可以使用URL编码和转义选项来避免代码注入...例如

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

编辑:关于此:

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

由于您是通过SQL引擎执行查询的，因此，与R相比，它更是一个SQL问题.就像@Vivek所说的那样，您可以在sqldf中执行此操作，但是我想您的数据位于远程数据库中，因此在这种情况下它无济于事.

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.

所有SQL版本都具有like，因此只需在查询中使用它即可.如果我误解了您的问题，请告诉我.

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,"'")

这篇关于使用R变量进行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！