[R-Forge #2158] getSymbols.MySQL creates a new connection each time

Submitted by: Edward Choh
Assigned to: Nobody
[R-Forge link](http://r-forge.r-project.org/tracker/index.php?func=detail&aid=2158&group_id=125&atid=546)

`getSymbols.MySQL` creates and closes a new DBI connection for each call. With heavy traffic, this will cause DBI to reach the connection limits (16) with sustained querying.

`getSymbols.MySQL` should accept a `conn` argument that reuses the DBI connection, instead of creating a new one each time.

Tested by fetching 1400+ symbols from MySQL back to back, throughput was greatly increased because of connection reuse.

```diff
Index: man/getSymbols.MySQL.Rd
===================================================================
--- man/getSymbols.MySQL.Rd (revision 582)
+++ man/getSymbols.MySQL.Rd (working copy)
@@ -21,6 +21,7 @@
dbname = NULL,
host = "localhost",
port = 3306,
+ conn = NULL,
...)
}
%- maybe also 'usage' for other objects documented here.
@@ -39,6 +40,7 @@
\item{dbname}{ database name }
\item{host}{ database host }
\item{port}{ database port }
+ \item{conn}{ DBI connection }
\item{\dots}{ currently not used }
}
\details{
@@ -53,6 +55,8 @@
The purpose of this abstraction is to make transparent the
\sQuote{source} of the data, allowing instead the user to
concentrate on the data itself.
+
+If conn is supplied, the other database values are unneccessary.
}
\value{
A call to getSymbols.MySQL will load into the specified
@@ -83,7 +87,7 @@
\code{\link{setSymbolLookup}} }
\examples{
\dontrun{
-# All 3 getSymbols calls return the same
+# All 4 getSymbols calls return the same
# MSFT to the global environment
# The last example is what NOT to do!

@@ -101,10 +105,15 @@

getSymbols('MSFT')

+## Method #3
+# Uses DBI connection
+conn = dbConnect('MySQL',dbname='test')
+getSymbols('MSFT',conn=conn)
+
#########################################
## NOT RECOMMENDED!!!
#########################################
-## Method #3
+## Method #4
getSymbols.MySQL('MSFT',env=globalenv())
}
}
Index: R/getSymbols.R
===================================================================
--- R/getSymbols.R (revision 582)
+++ R/getSymbols.R (working copy)
@@ -418,7 +418,7 @@
"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,
+ user=NULL,password=NULL,dbname=NULL,host='localhost',port=3306,conn=NULL,
...) {
importDefaults("getSymbols.MySQL")
this.env <- environment()
@@ -434,13 +434,18 @@
} else {
stop(paste("package:",dQuote('DBI'),"cannot be loaded."))
}
- if(is.null(user) || is.null(password) || is.null(dbname)) {
+ if(!is.null(conn)) {
+ # reusing connection
+ } else 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)
+ if(is.null(conn))
+ con <- dbConnect(MySQL(),user=user,password=password,dbname=dbname,host=host,port=port)
+ else
+ con <- conn
db.Symbols <- dbListTables(con)
if(length(Symbols) != sum(Symbols %in% db.Symbols)) {
missing.db.symbol <- Symbols[!Symbols %in% db.Symbols]
@@ -466,7 +471,8 @@
assign(Symbols[[i]],fr,env)
if(verbose) cat('done\n')
}
- dbDisconnect(con)
+ if(is.null(conn))
+ dbDisconnect(con)
if(auto.assign)
return(Symbols)
return(fr)
```