R help - Jul 2010 - Error using sqldf

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)


Can you guys suggest me where I am going wrong in passing in variable to
sqldf() query.

Thanks in advance.

Harsh Yadav

	[[alternative HTML version deleted]]

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)