[R-SIG-Finance] RMySQL - setDefaults requires clear text name/password?

[Mark Knecht]

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