CODEWITHSUNDEEP
mysqlsqlrfunctiondataframe
adhg
adhg

Reputation: 10863

apply a function on a data frame return incorrect result

I'm having a problem applying a function on a data frame where the function has some sql code. Consider the following dummy data frame: 

df <- read.table(header=T, text='
  ID
 20
 21
 22
')

when I'm trying to do this (after I establish a connection to the db and it works):

df<-transform(df, name="") #create another column name
df$name<-my_name(df$ID) #apply the function my_name on df$ID 

my_name<-function(id)
{   
  query = "select first_name from my_table WHERE id= "
  query = paste(query, id, sep="")

  rs = dbSendQuery(my_db, query) #my_db was already established
  fname<- fetch(rs)[1,1]

  return (fname)
}

I get the same result all over for the same ID 20 (the first in the dataframe).

Why? (..and for the record 21 is Mike and 22 is Peter)

incorrect result:

  ID  name
1 20  John
2 21  John
3 22  John

Upvotes: 0

Views: 42

Answers (2)

dayne
dayne

Reputation: 7784

Try:

df$name <- sapply(df$ID, my_name)

You are sending the whole ID field to the function every time, and it looks like the function is only returning the first value.

You could also modify your SQL query to use IN, then you could send multiple IDs, and get back the proper result set all in one connection.

I have used the following sytax many times:

query <- "select first_name from my_table WHERE id IN (%s)"
query <- sprintf(query, paste0("\"", id, "\"", collapse = ","))

Upvotes: 1

joran
joran

Reputation: 173567

If you modify your function to just stop after building query and return that, you'll see:

my_name(df$ID)
[1] "select first_name from my_table WHERE id= 20" "select first_name from my_table WHERE id= 21"
[3] "select first_name from my_table WHERE id= 22"

that things are definitely not right. You've created a character vector with three separate queries. dbSendQuery isn't vectorized (nor would you really want it to be), so it's just running the first one.

More generally, I think the SQL you are attempting to use is wrong. You probably want a query that looks more like:

select first_name from my_table WHERE id IN (20,21,22)

Note the IN clause. This requires doing something more like this:

query <- "select first_name from my_table WHERE id in "
query <- paste(query, paste0("(",paste(id,collapse = ","),")"), sep="")

Upvotes: 1

Related Questions