[R] MySQL

[Wladimir Eremeev]

Dear laurent,

> But there are lots of commands I found in R docs which are "not found",
> such as getTable. Deprecated (I successfully used dbListTables) or do I 
> simply miss a package ?

>From RMySQL\NEWS:
* added (almost) trivial convenience RS-DBI functions getTable(),
  assignTable(), existsTable(), and removeTable().  They all mimic the 
  corresponding R/S get(), assign(), exists(), and remove(), and they 
  all work with a connection object and an sql table name (NOT generic 
  SQL queries); assignTable() assigns a data.frame object (or coerceable) 
  to the database. (These functions provide the basis for "user-defined
  databases in S -- see below.)

> How should I use resultSets ? I am not able to close neither a 
> connection nor a ResultSet. I tried with close(con).
  
> Thanks for any help.

I used the following script:

library(RMySQL);
con <- dbConnect(dbDriver("MySQL"), dbname = "area");

regionarea <- dbGetQuery(con,
                         "select area from region_areas \
                          where region like \"Arctic Ocean\""
                  )$area;

mynn <- dbGetQuery(con,
                   paste("select year(date) as year,",
                         "avg(area1)/",regionarea,"*100 as area1,",
                         "avg(area2)/",regionarea,"*100 as area2",
                         "from ArcticOcean_MYNN",
                         "where month(date)<=3 and empty<465",
                         "group by year(date)",
                         sep=" "
                        )
                   );
dbDisconnect(con);

-- 
Best regards
Wladimir Eremeev                                     mailto:wl at eimb.ru

==========================================================================
Research Scientist                                Leninsky Prospect 33,
Space Monitoring & Ecoinformation Systems Sector, Moscow, Russia, 119071,
Institute of Ecology,                             Phone: (095) 135-9972;
Russian Academy of Sciences                       Fax: (095) 954-5534