[R] Using SQL "IN" with RJDBC or RMySQL

[David Epstein]

Hello,


The code below works fine up until I try to use the "IN" statement in
the last line. The proper SQL format is:

SELECT * FROM this_table WHERE this_column IN (1,2,3,4,5)

But, I think I may be getting something like:

SELECT * FROM this_table WHERE this_column IN c(1,2,3,4,5)

Which makes no sense in SQL. I think it may be just a matter of string
massaging, but I'm not certain. I've tried to build the query using
sprintf statements with no luck. I could not get RMySQL to do it either.
Any suggestions?


thank you,
-david


library(RJDBC)

drv <- JDBC("com.mysql.jdbc.Driver",
"/home/davideps/Software/extlibs/mysql-connector-java-5.0.7-bin.jar",identifier.quote="`")

conn <- dbConnect(drv, "jdbc:mysql://localhost/civicrm",
"userid","pass")

org_table=dbGetQuery(conn,"SELECT id,organization_name FROM
civicrm_contact WHERE contact_type=?","organization")

dbGetQuery(conn,"SELECT id from civicrm_relationship WHERE contact_id_a
IN ? AND contact_id_b IN ?",org_table$id,org_table$id)