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

[Paul Gilbert]

I think there is some "system-level magic" that getSymbols.MySQL is
breaking. The MySQL driver will actually find the information in the 
.my.cnf file if it is called without user and password, but the code 
does not try that:


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

I think it will be fixed by (untested)

    if ( is.null(dbname))
        stop('dbname must be specified')
    if (is.null(user) & is.null(password) )
      con <- dbConnect("MySQL", dbname = dbname)
    else
      con <- dbConnect("MySQL", user = user, password = password,
          dbname = dbname, host = host, port = port)

This could probably do something better if the user manages to specify 
one of user or password, or host, or port.

Paul.

On 13-12-20 04:30 PM, Mark Knecht wrote:
> 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>
>>
>
> _______________________________________________
> R-SIG-Finance at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only. If you want to post, subscribe first.
> -- Also note that this is not the r-help list where general R questions should go.
>