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

[Spencer Graves]

Hello:


	  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
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