[R] Forming SQL Query at run-time

[Seth Falcon]

"Rahul Thathoo" <rahul.thathoo at gmail.com> writes:

> Hi.
>
> I am trying to get data from mysql database using a couple of queries.
> I do one query to find out the indexes. Then i need to use these
> indexes in another query, but i keep getting errors.
>
> Here is something:
>
> numb <- dbSendQuery(con2, "select distinct(comparison) from table1")
>
> count <- fetch(numb, -1)
>
> my.matrix <- as.matrix(count)

You've selected one column, why is it a matrix?  You index it as a
vector, so I think you just want:

count <- fetch(numb, -1)[[1]]

fetch returns a data.frame which for the select you gave will have one
column.  Extracting the column gives you the vector.

> rs <- dbSendQuery(con2, "select A.comparison,A.id, A.q_value,
> B.q_value from table1 as A, table1 as B where A.comparison =
> 'my.matrix[11481]' AND B.comparison = 250 AND A.id = B.id")

As the other responder suggested, you need to create a string using
paste.  However, depending on how large length(count) is, you might be
better off doing:

  where A.comparison IN ('1', '2', '3', ..., 'n') AND

Which would be something like:

   "where A.comparison in (", paste("'", count, "'", sep="", collapse=","), ") ..."

You might also see if mysql supports nested selects in which case you
don't need two queries (from R) at all.  

+ seth