[BioC] RMySQL, for loop problem...

[Alessandro Bruselles]

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
>
>