[R] File to MYSQL

[Jerome Asselin]

On Mon, 2011-05-16 at 17:01 -0300, Nilza BARROS wrote:
> Hi, Jerome 
> 
> I was trying to use RMYSQL
> 
> for (i in length(Query)) { 
> rs1<-dbSendQuery(con,Query[i])
> } 
> 
> But although the Query have several  lines the command above just feed
> my database with the first one.
> 
> > Query
>  [1] "INSERT INTO OBS
> (date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO) VALUES (2011051312,26,NULL,20.6,19.4,1014.8,2.91,220,0.00,6,836490);"    
>  [2] "INSERT INTO OBS
> (date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO) VALUES (2011051312,17,NULL,15.5,15.7,912.8,9.91,180,0.00,8,836920);"     
>  [3] "INSERT INTO OBS
> (date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO) VALUES (2011051312,21.6,NULL,18.1,19.7,1003,1.94,140,0.00,2,836950);" 
> 
> Thanks,

I am not aware of an effective method for submitting multiple queries in
a single string. Splitting the queries in multiple strings generates
unnecessary overhead. More than once before I wanted to run multiple
queries and wished I had a way to do this effectively.

I believe RODBC's sqlUpdate() with append=TRUE option might allow you to
insert multiple rows at once into an existing table. I don't know
whether RMySQL has an equivalent.

I've preferred RODBC for many years (instead of RMySQL) since RODBC's
interface is not limited to MySQL.

Jerome