[R] using R variables in RMySQL query

[Uwe Ligges]

On 23.03.2011 17:44, algotr8der wrote:
> I have the following function
>
> myGetstockdataMySQL<- function(startdate, enddate, ticker) {
>
> con<- dbConnect(MySQL(), user="blahblah", password="blahblah",
> dbname="blahblah",
> host="localhost")
>
>
> rs<- dbGetQuery(con, "SELECT price.close FROM price INNER JOIN stocks ON
> stocks.stock_id=price.stock_ID
> WHERE (price.date_holding BETWEEN 'startdate' AND 'enddate') AND
> stocks.stock_ticker_yahoo IN ticker";)

1. The semicolon is at the wrong position, definitively.

2. why not

rs <- dbGetQuery(con,
  paste("SELECT price.close FROM price INNER JOIN stocks ON 
stocks.stock_id=price.stock_ID WHERE (price.date_holding BETWEEN",
   shQuote(startdate), "AND", shQuote(enddate),
   "AND stocks.stock_ticker_yahoo IN ticker")
)

Uwe Ligges




>
> }
>
> I get all sorts of errors. I want to pass variables startdate, enddate and
> ticker. It would also be nice to pass the username, password and dbname
> information in the function.
>
> I have read through a few posts here regarding using "paste" but I still get
> errors. Can someone point me in the right direction please? Thank you very
> much.
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/using-R-variables-in-RMySQL-query-tp3399978p3399978.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.