[R-sig-DB] dynamic sql statements and dbGetQuery

[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
 		 	   		  
	[[alternative HTML version deleted]]