[R] Executing a function correctly

[Marc Schwartz]

On Jul 13, 2011, at 1:38 PM, saskay wrote:

> Hello All,
> I've created a function as follows so as to use it in a loop.
> 
> freq<-function(i)
> {
> library(RODBC)
> paste(i,"<-sqlQuery(conn,","'","select   click_flg, open_flg,", i ," from
> modeling_5')",sep="")
> }
> 
> freq(i="AQI")
> [1] "AQI<-sqlQuery(conn,'select   click_flg, open_flg,AQI from modeling_5')"
> 
> What I was expecting was the result of the sql query "select   click_flg,
> open_flg,AQI from modeling_5" be put into the object "AQI". However it just
> resolves to a string.
> 
> Note: the query - AQI<-sqlQuery(conn,'select   click_flg, open_flg,AQI from
> modeling_5')
> works fine on its own.
> 
> How can make the function execute the resolved expression rather than just
> return the string result of the resolution.
> 
> Many thanks.
> 
> PS: I did search on the internet including this site but could not find a
> suitale solution.


You need to remove the sqlQuery() function call and 'conn' object from the paste() process.

  sqlQuery(conn, paste("select click_flg, open_flg, ", i ," from modeling_5", sep = ""))

The paste() result is then:

i <- "Test"

> paste("select click_flg, open_flg, ", i ," from modeling_5", sep = "")
[1] "select click_flg, open_flg, Test from modeling_5"


You can pass the 'conn' object to the function as another argument and I would also remove the library(RODBC) call outside of the function and/or alternatively, replace it with require(). Thus, something like:


freq <- function(db, i) {
  sqlQuery(db, paste("select click_flg, open_flg, ", i ," from modeling_5", 
                     sep = ""))
}

require(RODBC)

Someplace in here, 'conn' is created using conn <- odbcConnect(...)

# Here is the call to create the data.frame 'AQI'
AQI <- freq(conn, i = "AQI")

# Close the connection, unless you will be using it again.
odbcClose(conn)


This is untested, but "should" work. The key is to only include the actual SQL query text in the paste call.

HTH,

Marc Schwartz