Skip to content

dynamic sql statements and dbGetQuery

2 messages · Nick Torenvliet, Sean Davis

Original
Nick Torenvliet
# 
Hi, 

Just new to R and rmysql and trying to run the same query on a list of databases as per the following...

library(RMySQL)
drv = dbDriver("MySQL")
dbList = c("TSE")

for (market in dbList){
    con = dbConnect(drv, host="localhost", dbname=market, user="root", pass="rootPass")
    dbTableList <- dbListTables(con)
    for (symbol in dbTableList){
        #get the latest date in the table
        string <- c("select max(date(dayDate)) from ", symbol)
        sql <- toString(string)
        tableData = dbGetQuery( con, sql)
        cat(tableData)        
    }
    dbDisconnect(con)
}

When I run the script I get the following error... 

select max(date(dayDate)) from  AABError in mysqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''select max(date(dayDate)) from , AAB, '' at line 1)


Where AAB is the first table in dbTableList... I'm kind of stumped as to why this won't work -- the sql itself checks out fine... I've experimented with parenthesis and a semicolon... and dbSendQuery as well... all to no avail...

Any help would be appreciated, 

Nick
Sean Davis
# 
On Mon, Dec 7, 2009 at 10:01 PM, Nick Torenvliet
<n_torenvliet at hotmail.com> wrote:
Basic R problem here--c() forms a vector and is not a string
concatenation.  You want to use paste():

sql <- paste("select max(date(dayDate)) from",symbol)
Now, you don't need this line.

Untested, so let us know if there is still a problem after switching
to using paste().  The other way to do this is to use sprintf().

Sean