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

[Sean Davis]

On Mon, Dec 7, 2009 at 10:01 PM, Nick Torenvliet
<n_torenvliet using hotmail.com> wrote:
>
> 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)

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)

>        sql <- toString(string)

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

>        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]]
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>