R help - Mar 2011 - using R variables in RMySQL query

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

}

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.

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.