I'm trying to do a little loop using the RMySQL package to update some fields in my db but I can't find a way; the loop should be this: /for (i in 1:nrow(geni_FClow)){ dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = geni_FClow$FC_low[i] where geni like geni_FClow$geni[i]") }/ but I get the following error: /Errore in mysqlExecStatement(conn, statement, ...) : 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 '[i] where geni like geni_FClow$geni[i]' at line 1)/ while the simple statement: /dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = geni_FClow$FC_low[2] where geni like geni_FClow$geni[2]")/ (using a number instead of the [i]) works! Any help would be appreciated (I know maybe the answer is so simple I cannot see it...) -- _________________________________________ Alessandro Bruselles University of Rome "Tor Vergata"** <mailto:a.bruselles at="" gmail.com="">

Try to do assemble your sql command fist: Sqlcmd <- paste("update GO_DC5_vs_50PC3_apoptosis set FC_low = ", geni_FClow$FC_low[I], " where geni like ", geni_FClow$geni[i], sep = ""); dbGetQuery(con, Sqlcmd); Good luck! Wenwu Cui -----Original Message----- From: Alessandro Bruselles [mailto:a.bruselles@gmail.com] Sent: Friday, June 15, 2007 9:56 AM To: Bioconductor Mailing list Subject: [BioC] RMySQL, for loop problem... I'm trying to do a little loop using the RMySQL package to update some fields in my db but I can't find a way; the loop should be this: /for (i in 1:nrow(geni_FClow)){ dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = geni_FClow$FC_low[i] where geni like geni_FClow$geni[i]") }/ but I get the following error: /Errore in mysqlExecStatement(conn, statement, ...) : 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 '[i] where geni like geni_FClow$geni[i]' at line 1)/ while the simple statement: /dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = geni_FClow$FC_low[2] where geni like geni_FClow$geni[2]")/ (using a number instead of the [i]) works! Any help would be appreciated (I know maybe the answer is so simple I cannot see it...) -- _________________________________________ Alessandro Bruselles University of Rome "Tor Vergata"** <mailto:a.bruselles at="" gmail.com=""> _______________________________________________ Bioconductor mailing list Bioconductor at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/bioconductor Search the archives: http://news.gmane.org/gmane.science.biology.informatics.conductor

Alessandro Bruselles wrote: > I'm trying to do a little loop using the RMySQL package to update some > fields in my db > but I can't find a way; > the loop should be this: > > /for (i in 1:nrow(geni_FClow)){ > dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = > geni_FClow$FC_low[i] where geni like geni_FClow$geni[i]") > }/ > > but I get the following error: > > /Errore in mysqlExecStatement(conn, statement, ...) : > 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 '[i] where geni like > geni_FClow$geni[i]' at line 1)/ > > while the simple statement: > > /dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = > geni_FClow$FC_low[2] where geni like geni_FClow$geni[2]")/ > > (using a number instead of the [i]) > works! > > Any help would be appreciated > (I know maybe the answer is so simple I cannot see it...) > Look at the help for sprintf(). RMySQL does not, the last time I looked, do variable substitution. Sean

Hi Alessandro, Alessandro Bruselles wrote: > I'm trying to do a little loop using the RMySQL package to update some > fields in my db > but I can't find a way; > the loop should be this: > > /for (i in 1:nrow(geni_FClow)){ > dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = > geni_FClow$FC_low[i] where geni like geni_FClow$geni[i]") > }/ R is not PHP ;-) > x <- "'letters[1]' is the first letter of the latin alphabet" > x [1] "'letters[1]' is the first letter of the latin alphabet" No variable substitution in string 'x'. That's why you don't get this: [1] "'a' is the first letter of the latin alphabet" Cheers, H. > > but I get the following error: > > /Errore in mysqlExecStatement(conn, statement, ...) : > 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 '[i] where geni like > geni_FClow$geni[i]' at line 1)/ > > while the simple statement: > > /dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = > geni_FClow$FC_low[2] where geni like geni_FClow$geni[2]")/ > > (using a number instead of the [i]) > works! > > Any help would be appreciated > (I know maybe the answer is so simple I cannot see it...) >

assemble the sql command first as suggested by others and then use the dbSendQuery function. dbSendQuery(con, Sqlcmd) this used to work for me Peter ________________________________ Fra: bioconductor-bounces at stat.math.ethz.ch p? vegne af Alessandro Bruselles Sendt: ma 18-06-2007 12:04 Til: Bioconductor at stat.math.ethz.ch Emne: Re: [BioC] RMySQL, for loop problem... Thanks to all who tried to help me, but none of the suggested solutions worked for me, I still didn't manage to do the loop... How else can I do to iteratively update my db? Thanks _________________________________________ Alessandro Bruselles University of Rome "Tor Vergata" ** Herve Pages ha scritto: > Hi Alessandro, > > Alessandro Bruselles wrote: > >> I'm trying to do a little loop using the RMySQL package to update some >> fields in my db >> but I can't find a way; >> the loop should be this: >> >> /for (i in 1:nrow(geni_FClow)){ >> dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = >> geni_FClow$FC_low[i] where geni like geni_FClow$geni[i]") >> }/ >> > > R is not PHP ;-) > > > x <- "'letters[1]' is the first letter of the latin alphabet" > > x > [1] "'letters[1]' is the first letter of the latin alphabet" > > No variable substitution in string 'x'. That's why you don't get > this: > > [1] "'a' is the first letter of the latin alphabet" > > Cheers, > H. > > >> but I get the following error: >> >> /Errore in mysqlExecStatement(conn, statement, ...) : >> 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 '[i] where geni like >> geni_FClow$geni[i]' at line 1)/ >> >> while the simple statement: >> >> /dbGetQuery(con, "update GO_DC5_vs_50PC3_apoptosis set FC_low = >> geni_FClow$FC_low[2] where geni like geni_FClow$geni[2]")/ >> >> (using a number instead of the [i]) >> works! >> >> Any help would be appreciated >> (I know maybe the answer is so simple I cannot see it...) >> >> > > > [[alternative HTML version deleted]] _______________________________________________ Bioconductor mailing list Bioconductor at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/bioconductor Search the archives: http://news.gmane.org/gmane.science.biology.informatics.conductor