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 -- And then there was a man who drowned in a stream 4.5 feet in depth. MORAL OF THE STORY: Always measure the standard deviation first! ----- Rahul Thathoo http://cs.stanford.edu/~thathoo http://blogs.siliconindia.com/rahul Cell: 001 650 391 5584
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
"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