Question: RMySQL, for loop problem...
0
12.4 years ago by
Alessandro Bruselles100 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...) -- _________________________________________ Alessandro Bruselles University of Rome "Tor Vergata"** <mailto:a.bruselles at="" gmail.com=""> • 1.6k views ADD COMMENTlink modified 12.3 years ago by Peter Sørensen (HAG)60 • written 12.4 years ago by Alessandro Bruselles100 Answer: RMySQL, for loop problem... 0 12.4 years ago by 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
0
12.4 years ago by
Sean Davis21k
United States
Sean Davis21k wrote:
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 ADD COMMENTlink written 12.4 years ago by Sean Davis21k Answer: RMySQL, for loop problem... 0 12.4 years ago by Hervé Pagès ♦♦ 14k United States Hervé Pagès ♦♦ 14k wrote: 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...) >
An embedded and charset-unspecified text was scrubbed... Name: not available Url: https://stat.ethz.ch/pipermail/bioconductor/attachments/20070618/ c062b211/attachment.pl
Alessandro Bruselles wrote: > 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? Alessandro, You will probably want to post the code that you tried and what errors you received. The way you are trying is the correct way of performing the update, but there are a few details to get right before it will work. Sean
Try to printout your command before execute the query: for (i in 1:nrow(geni_FClow)){ Sqlcmd <- paste("update GO_DC5_vs_50PC3_apoptosis set FC_low = ", geni_FClow$FC_low[i], " where geni like ", geni_FClow$geni[i], sep = ""); Sqlcmd; #dbSendQuery(con, Sqlcmd) } You might need to add the \" before and after geni_FClow$geni[i]. Wenwu Cui, PhD National Center for Biotechnology Information National Institutes of Health -----Original Message----- From: Alessandro Bruselles [mailto:a.bruselles@gmail.com] Sent: Monday, June 18, 2007 9:20 AM Cc: Bioconductor at stat.math.ethz.ch Subject: Re: [BioC] RMySQL, for loop problem... This is the object I want to read from: > geni_FClow geni FC_low 1 AF039390 -1.704 2 AF039390 -1.704 3 AF116456 1.501 4 AJ250915 1.656 5 AJ250915 1.656 6 AF326592 1.835 This is the command I'm trying to use (as Cui, Wenwu suggested) > Sqlcmd <- paste("update GO_DC5_vs_50PC3_apoptosis set FC_low = ", geni_FClow$FC_low[i], " where geni like ", geni_FClow$geni[i], sep = "") > for (i in 1:nrow(geni_FClow)){ + dbSendQuery(con, Sqlcmd) + } and this is the error I get: Errore in mysqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not run statement: Unknown column 'AF039390' in 'where clause') > is.data.frame(geni_FClow) [1] TRUE Thanks Alessandro Sean Davis ha scritto: > Alessandro Bruselles wrote: > >> 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? >> > > Alessandro, > > You will probably want to post the code that you tried and what errors > you received. The way you are trying is the correct way of performing > the update, but there are a few details to get right before it will work. > > Sean > > [[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 ADD REPLYlink written 12.3 years ago by Cui, Wenwu NIH/NLM/NCBI [C]30 Alessandro Bruselles wrote: > This is the object I want to read from: > > geni_FClow > geni FC_low > 1 AF039390 -1.704 > 2 AF039390 -1.704 > 3 AF116456 1.501 > 4 AJ250915 1.656 > 5 AJ250915 1.656 > 6 AF326592 1.835 > > This is the command I'm trying to use (as Cui, Wenwu suggested) > > Sqlcmd <- paste("update GO_DC5_vs_50PC3_apoptosis set FC_low = ", > geni_FClow$FC_low[i], " where geni like ", geni_FClow$geni[i], sep = "") > > for (i in 1:nrow(geni_FClow)){ > + dbSendQuery(con, Sqlcmd) > + } > > and this is the error I get: > Errore in mysqlExecStatement(conn, statement, ...) : > RS-DBI driver: (could not run statement: Unknown column > 'AF039390' in 'where clause') Hi, Alessandro. First, you need to put the Sqlcmd definition INSIDE the loop. Second, remember that you are writing SQL using text manipulators. Unlike php and other languages that can do quoting in SQL for you, you need to do it explicitly in R. To avoid problems (or at least catch them as easily as possible), I usually do the SQL construction part without the database, take the constructed SQL as a string, paste an example of the output into a mysql console or some other interface to make sure that is works and does what I expect, and then do add the database part. So, something like this will work for you, hopefully: for (i in 1:nrow(geni_FClow)) { Sqlcmd <- paste("update GO_DC5_vs_50PC3_apoptosis set FC_low = ", geni_FClow$FC_low[i], " where geni like '", geni_FClow$geni[i],"'", sep = "") dbSendQuery(con,Sqlcmd) } Note the extra ' (single quote) added around text. Sean ADD REPLYlink written 12.3 years ago by Sean Davis21k This latter solution from Sean finally worked... Thanks to all _________________________________________ Alessandro Bruselles University of Rome "Tor Vergata" ** Sean Davis ha scritto: > Alessandro Bruselles wrote: > >> This is the object I want to read from: >> > geni_FClow >> geni FC_low >> 1 AF039390 -1.704 >> 2 AF039390 -1.704 >> 3 AF116456 1.501 >> 4 AJ250915 1.656 >> 5 AJ250915 1.656 >> 6 AF326592 1.835 >> >> This is the command I'm trying to use (as Cui, Wenwu suggested) >> > Sqlcmd <- paste("update GO_DC5_vs_50PC3_apoptosis set FC_low = ", >> geni_FClow$FC_low[i], " where geni like ", geni_FClow$geni[i], sep = "") >> > for (i in 1:nrow(geni_FClow)){ >> + dbSendQuery(con, Sqlcmd) >> + } >> >> and this is the error I get: >> Errore in mysqlExecStatement(conn, statement, ...) : >> RS-DBI driver: (could not run statement: Unknown column >> 'AF039390' in 'where clause') >> > > Hi, Alessandro. First, you need to put the Sqlcmd definition INSIDE the > loop. Second, remember that you are writing SQL using text > manipulators. Unlike php and other languages that can do quoting in SQL > for you, you need to do it explicitly in R. To avoid problems (or at > least catch them as easily as possible), I usually do the SQL > construction part without the database, take the constructed SQL as a > string, paste an example of the output into a mysql console or some > other interface to make sure that is works and does what I expect, and > then do add the database part. So, something like this will work for > you, hopefully: > > for (i in 1:nrow(geni_FClow)) { > Sqlcmd <- paste("update GO_DC5_vs_50PC3_apoptosis set FC_low = ", > geni_FClow$FC_low[i], " where geni like '", geni_FClow$geni[i],"'", > sep = "") > dbSendQuery(con,Sqlcmd) > } > > Note the extra ' (single quote) added around text. > > Sean > > ADD REPLYlink written 12.3 years ago by Alessandro Bruselles100 Answer: RMySQL, for loop problem... 0 12.3 years ago by Peter Sørensen (HAG)60 wrote: 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