[R-sig-DB] adding to a MySQL database from within R?

Dirk Eddelbuettel edd @end|ng |rom deb|@n@org
Tue Oct 12 13:24:18 CEST 2010 

On 11 October 2010 at 20:34, Spencer Graves wrote:
|   Hi, Dirk, et al.:
| 
| 
|        Thanks for the quick reply.
| 
| 
|        What are the strengths and weaknesses of using RODBC vs. RMySQL?

That is also a question which have been debated to death before on the
Intertubes or this list.  Do some searches.

In short, and as I see it, 

  - ODBC is more generic so what you do here may work similarly with other
    software providing ODBC interfaces.  

  - Specific binary interface can be faster, sometimes by a lot.  

  - For R in particular, RMySQL supports the DBI interface so here the
    situation is the inverse of the general solution: you may use what you 
    did with RMySQL again with RSQLite or RPostgreSQL or ...
 
|        I don't have either doing what I want yet, but I think I'm closer 
| with RMySQL than RODBC.  On the other hand, I had the impression that 
| RODBC was better developed than RMySQL and therefore RODBC might be 
| faster and ultimately easier.

Unsure. They are both exceedingly mature packages that have been around well
over half a decade.  From what I understand RMySQL has its headaches with
different MySQL versions as well as Windows builds -- but I never had an
issue.

Dirk
 
| 
|        Thanks again.
|        Spencer Graves
| 
| 
| On 10/11/2010 8:14 PM, Dirk Eddelbuettel wrote:
| > On 11 October 2010 at 20:00, Spencer Graves wrote:
| > |
| > | 	  How can I connect to a MySQL database with RODBC under Vista?  I
| > | tried the following, inspired by Ripley's RODBC vignette:
| > |
| > |
| > |>  MySQLcon<- odbcConnect('fillmoredb', uid='sbg', pwd='********')
| > | Warning messages:
| > | 1: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********) :
| > |    [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
| > | Manager] Data source name not found and no default driver specified
| >             ^^^^^^^^^^^^^^^^^^^^^^^^^^
| >
| > You need to define a DSN named 'fillmoredb'.  Windows being Windows, there
| > are countless tutorials on the net about how to do this using the admin tools.
| >
| > The nice thing is that process is just about the same for every 'source'
| > providing ODBC connectivity, so once you grok this you can re-use the same
| > trick for non-MySQL-stored data.
| >
| > The not-so-nice thing, in my book, is that you still need to install the odbc
| > drivers and beed to set up the dsn on each machine trying to use it.
| >
| > Dirk
| >
| > | 2: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********") :
| > |    ODBC connection failed
| > |>  MySQL.<- odbcConnect('MySQL')
| > | Warning messages:
| > | 1: In odbcDriverConnect("DSN=MySQL") :
| > |    [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
| > | Manager] Data source name not found and no default driver specified
| > | 2: In odbcDriverConnect("DSN=MySQL") : ODBC connection failed
| > |
| > |
| > | 	  Thanks,
| > | 	  Spencer Graves
| > |
| > |
| > |>  sessionInfo()
| > | R version 2.11.1 (2010-05-31)
| > | i386-pc-mingw32
| > |
| > | locale:
| > | [1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United
| > | States.1252    LC_MONETARY=English_United States.1252
| > | [4] LC_NUMERIC=C                           LC_TIME=English_United
| > | States.1252
| > |
| > | attached base packages:
| > | [1] splines   stats     graphics  grDevices utils     datasets  methods
| > |    base
| > |
| > | other attached packages:
| > | [1] RODBC_1.3-2     multcomp_1.2-3  survival_2.35-8 mvtnorm_0.9-92
| > |
| > | loaded via a namespace (and not attached):
| > | [1] tools_2.11.1
| > |
| > | ####
| > | Enter password: ********
| > | Welcome to the MySQL monitor.  Commands end with ; or \g.
| > | Your MySQL connection id is 7
| > | Server version: 5.1.50-community MySQL Community Server (GPL)
| > |
| > | Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
| > | This software comes with ABSOLUTELY NO WARRANTY. This is free software,
| > | and you are welcome to modify and redistribute it under the GPL v2 license
| > |
| > | Type 'help;' or '\h' for help. Type '\c' to clear the current input
| > | statement.
| > |
| > | mysql>
| > |
| > | ########################
| > |   Hi, Sean and Jack Tanner:
| > |
| > |
| > |
| > |        It's hard to RTFM when I don't know which FMTR.  Thanks to you
| > | both for helping me overcome this problem.
| > |
| > |
| > |              1.  Before I submitted this question, I read the help page
| > | for "dbWriteTable".  Unfortunately, I missed the "append" argument.  In
| > | this case, I RTFM without seeing what was there!
| > |
| > |
| > |              2.  Jack's suggestion led me to try "RSiteSearch('MySQL
| > | INSERT')".  This led me to a vignette written by Brian Ripley for the
| > | RODBC package.  I tried other searches without getting an answer.  Also,
| > | I had previously seen a post where Ripley recommended the RODBC package.
| > |   However, my previous searched did not help me figure out how to get
| > | started with it.
| > |
| > |
| > |        Best Wishes,
| > |        Spencer
| > |
| > |
| > | On 10/9/2010 5:47 AM, Sean Davis wrote:
| > |>  On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves<
| > |>  spencer.graves using structuremonitoring.com>   wrote:
| > |>
| > |>>    Hello, All:
| > |>>
| > |>>
| > |>>         How can I add rows to a MySQL database?
| > |>>
| > |>>
| > |>>         I can do it manually within MySQL server 5.1.50-community.  I can
| > |>>  connect to it using the RMySQL package.  When I tried "dbReadTable
| > |>>  {DBI}", I got a warning that "table headers exists:
| > |>>
| > |>>
| > |>>    >   MySQL.<- MySQL()
| > |>>    >   MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB)
| > |>>    >   dbWriteTable(MySQLcon, 'headers', a.data.frame)
| > |>>  Warning message:
| > |>>  In mysqlWriteTable(conn, name, value, ...) :
| > |>>     table headers exists in database: aborting mysqlWriteTable
| > |>>
| > |>>
| > |>>         I know that table "headers" exists:  I want to add to it, not
| > |>>  create the entire table from scratch at once.
| > |>>
| > |>>
| > |>  See the help for dbWriteTable().  In particular, check out the append
| > |>  parameter.
| > |>
| > |>  Sean
| > |>
| > |
| > |
| > | --
| > | Spencer Graves, PE, PhD
| > | President and Chief Operating Officer
| > | Structure Inspection and Monitoring, Inc.
| > | 751 Emerson Ct.
| > | San José, CA 95126
| > | ph:  408-655-4567
| > |
| > | _______________________________________________
| > | R-sig-DB mailing list -- R Special Interest Group
| > | R-sig-DB using stat.math.ethz.ch
| > | https://stat.ethz.ch/mailman/listinfo/r-sig-db
| >

-- 
Dirk Eddelbuettel | edd using debian.org | http://dirk.eddelbuettel.com

More information about the R-sig-DB mailing list