[R] Forming SQL Query at run-time

[Jerome Asselin]

On Thu, 16 Nov 2006 20:21:29 -0800
  "Rahul Thathoo" <rahul.thathoo at gmail.com> wrote:
> 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)
> 
> 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")
> 
> RS-DBI driver: (could not run statement: You have an 
>error in your SQL
> syntax; check the manual that corresponds to your MySQL 
>server version
> for the right syntax to use near '[11481] AND 
>B.comparison = 250 AND
> A.id = B.id' at line 1)
> 
> And then later I need to do this in a loop, so the array 
>index in
> my.matrix will also be another variable. How do I do it? 
>Anyhelp would
> be appreciated.
> 
> Thanks
> Rahul
> 

Use paste().

myquery <- paste("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", sep="")

rs <- dbSendQuery(con2, myquery)

HTH,
Jerome

-- 
Jerome Asselin, M.Sc., Agent de recherche, RHCE
CHUM -- Centre de recherche
3875 rue St-Urbain, 3e etage // Montreal QC  H2W 1V1
Tel.: 514-890-8000 Poste 15914; Fax: 514-412-7106