[R] Error using sqldf

[Gabor Grothendieck]

On Tue, Jul 20, 2010 at 2:34 PM, harsh yadav <harsh.delhi at 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)