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

[Mark Knecht]

On Fri, Dec 20, 2013 at 1:49 PM, Paul Gilbert <pgilbert902 at gmail.com> wrote:
> 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.
>

This looks like a good direction for a potential solution.
Unfortunately my R function foo seems a bit lacking to make it work.

I copied the function I posted earlier to my own function file, called
it MySQLgetSymbols and added your idea. I unfortunately have two
problems:

1) R complains that 'missing' is being misused. Being that I don't
care about verbose of auto.assign for testing I just commented those
out.

#  if (missing(verbose))
    verbose <- FALSE
#  if (missing(auto.assign))
    auto.assign <- TRUE

2) The larger problem is that it needs a function called
convert.time.series. I don't know where that comes from but I suspect
it has something to do with the "<environment: namespace:quantmod>" I
see when I print out the function (see second post if you still have
it) but I don't know how to use in this case.

Thanks,
Mark


> if (DownloadNewData){
+   if (!UseMySQL){
+     getSymbolsCont(BuyHoldSymbol,  from = Date_Start, to = Date_End,
adjust = MyAdjust, src=SymbolSrc)
+   } else {
+  #   getSymbols(BuyHoldSymbol, src="MySQL")
+     MySQLgetSymbols(BuyHoldSymbol,  from = Date_Start, to =
Date_End, dbname=dbName)
+   }
+ }
Error: could not find function "convert.time.series"

>

MySQLgetSymbols =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"))
#   }

if ( is.null(dbname))
  stop('dbname must be specified')


#This section is new:

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)

# End of edit


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