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

[Spencer Graves]

  Hi, Dirk, et al.:


       Thanks for the quick reply.


       What are the strengths and weaknesses of using RODBC vs. RMySQL?


       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.


       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
>