[BioC] RMySQL, for loop problem...

[Sean Davis]

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