RMySQL - setDefaults requires clear text name/password?

On Fri, Dec 20, 2013 at 11:03 AM, Mark Knecht <markknecht at gmail.com> wrote:

I'm using RMySQL saving data from Yahoo. It's working nicely, but as
best I can tell the setDefaults function seems to require me to put
the MySQL account name and password in clear text in my R program
which I don't like. I.e. - the one commented out below works, the one
currently not commented out fails later when using
getSymbols(,src="MySQL")

As best I can tell everything else I'm using - most of which I think
comes from the DBI package - is using /home/mark/.my.cnf to get these
values. I can connect to the database, read and write it, etc. It's
only the getSymbols command I'm having trouble with.

Is there a way around this that keeps the password out of my files and
out of the file.Rout file when I run in batch mode?

Thanks,
Mark


if (UseMySQL){
   if (MyAdjust) { dbName = paste0(dbName, "Adjust")}
   dbc = dbConnect(MySQL(), dbname=dbName)
#  setDefaults(getSymbols.MySQL, user="mark", password="TmpPasswd",
dbname=dbName)
   setDefaults(getSymbols.MySQL, dbname=dbName)
}

Answering myself, it appears the answer is no unless there's some
system-level magic out there. I don't see any way for this function as
currently written to pick up the values I'd like it to be able to read
from the .my.cnf file.

- Mark

getSymbols.MySQL

function (Symbols, env, return.class = "xts", db.fields = c("date",
     "o", "h", "l", "c", "v", "a"), field.names = NULL, user = NULL,
     password = NULL, dbname = NULL, host = "localhost", port = 3306,
     ...)
{
     importDefaults("getSymbols.MySQL")
     this.env <- environment()
     for (var in names(list(...))) {
         assign(var, list(...)[[var]], this.env)
     }
     if (missing(verbose))
         verbose <- FALSE
     if (missing(auto.assign))
         auto.assign <- TRUE
     if ("package:DBI" %in% search() || require("DBI", quietly = TRUE)) {
         if ("package:RMySQL" %in% search() || require("RMySQL",
             quietly = TRUE)) {
         }
         else {
             warning(paste("package:", dQuote("RMySQL"), "cannot be loaded"))
         }
     }
     else {
         stop(paste("package:", dQuote("DBI"), "cannot be loaded."))
     }
     if (is.null(user) || is.null(password) || is.null(dbname)) {
         stop(paste("At least one connection argument (", sQuote("user"),
             sQuote("password"), sQuote("dbname"), ") is not set"))
     }
     con <- dbConnect("MySQL", user = user, password = password,
         dbname = dbname, host = host, port = port)
     db.Symbols <- dbListTables(con)
     if (length(Symbols) != sum(Symbols %in% db.Symbols)) {
         missing.db.symbol <- Symbols[!Symbols %in% db.Symbols]
         warning(paste("could not load symbol(s): ", paste(missing.db.symbol,
             collapse = ", ")))
         Symbols <- Symbols[Symbols %in% db.Symbols]
     }
     for (i in 1:length(Symbols)) {
         if (verbose) {
             cat(paste("Loading ", Symbols[[i]], paste(rep(".",
                 10 - nchar(Symbols[[i]])), collapse = ""), sep = ""))
         }
         query <- paste("SELECT ", paste(db.fields, collapse = ","),
             " FROM ", Symbols[[i]], " ORDER BY date")
         rs <- dbSendQuery(con, query)
         fr <- fetch(rs, n = -1)
         fr <- xts(as.matrix(fr[, -1]), order.by = as.Date(fr[,
             1], origin = "1970-01-01"), src = dbname, updated = Sys.time())
         colnames(fr) <- paste(Symbols[[i]], c("Open", "High",
             "Low", "Close", "Volume", "Adjusted"), sep = ".")
         fr <- convert.time.series(fr = fr, return.class = return.class)
         if (auto.assign)
             assign(Symbols[[i]], fr, env)
         if (verbose)
             cat("done\n")
     }
     dbDisconnect(con)
     if (auto.assign)
         return(Symbols)
     return(fr)
}
<environment: namespace:quantmod>