On Tue, Jul 20, 2010 at 2:34 PM, harsh yadav <harsh.de...@gmail.com> wrote:
> Hi,
>
> I am running a query using sqldf() [package : sqldf]. The query is:-
>
> userid <- 5
> taskid <- 5
>
> tab1 <- fn$sqldf("SELECT tobiiEvents.data1, tobiiEvents.data2,
> events.`timestamp` as tobiiTime
> FROM tobiiEvents
> INNER JOIN events ON events.eventid = tobiiEvents.eventid
> WHERE tobiiEvents.subtype = 'MOUSE' AND tobiiEvents.userid = 5 AND
> tobiiEvents.taskid = 5
> ORDER BY events.`timestamp`")
>
> This runs fine when I am using constants.
>
> However, when I pass in variables (userid and taskid) like :-
>
> tab1 <- fn$sqldf("SELECT tobiiEvents.data1, tobiiEvents.data2,
> events.`timestamp` as tobiiTime
> FROM tobiiEvents
> INNER JOIN events ON events.eventid = tobiiEvents.eventid
> WHERE tobiiEvents.subtype = 'MOUSE' AND tobiiEvents.userid = $userid AND
> tobiiEvents.taskid = $taskid
> ORDER BY events.`timestamp`")
>
> it gives me following error:-
>
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver: (error in statement: near "(": syntax error)
>
>
As discussed offline the fn$ construct not only interprets $... but
also `...` See ?fn
Removing the back quotes solves the problem here.
Alternately it would be possible to use gsubfn to do $... substitution only:
s <- gsubfn("[$]([[:alpha:]][[:alnum:].]*)",, mysql.statement)
sqldf(s)
______________________________________________
R-help@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.
