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

[Dirk Eddelbuettel]

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