Hi, when I generate query using sqldf library, like this: query = paste(paste("select * from tbl_user where student_id = ", id, sep=""), " order by date_time", sep="") student <- sqldf(query) everything works fine in case the id is "21328", "82882", or something like that. But, when id is something like "78789D", there is an error: Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: unrecognized token: "78789D") I tried replacing single quotes with double, but it still doesn't work... thanks, Srecko [[alternative HTML version deleted]]
Look at the query strings your code produces:> with(list(id=c("1234","abcd")), paste(paste("select * from tbl_user where student_id = ", id,sep=""), " order by date_time", sep="") ) [1] "select * from tbl_user where student_id = 1234 order by date_time" [2] "select * from tbl_user where student_id = abcd order by date_time" I suspect that the abcd should have quotes around it. If student_id is stored as string data the 1234 should probably also have quotes around it. Replace id with "\"", id, "\"" and you may get a query that works. Bill Dunlap Spotfire, TIBCO Software wdunlap tibco.com> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf > Of srecko joksimovic > Sent: Tuesday, September 17, 2013 9:04 AM > To: R help > Subject: [R] Unrecognized token > > Hi, > > when I generate query using sqldf library, like this: > query = paste(paste("select * from tbl_user where student_id = ", id, > sep=""), " order by date_time", sep="") > > student <- sqldf(query) > > everything works fine in case the id is "21328", "82882", or something like > that. But, when id is something like "78789D", there is an error: > Error in sqliteExecStatement(con, statement, bind.data) : > RS-DBI driver: (error in statement: unrecognized token: "78789D") > > I tried replacing single quotes with double, but it still doesn't work... > > thanks, > Srecko > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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.
Maybe you should escape the single quotes something like " ' " id " ' "? On Sep 17, 2013, at 6:03 PM, srecko joksimovic <sreckojoksimovic at gmail.com> wrote:> Hi, > > when I generate query using sqldf library, like this: > query = paste(paste("select * from tbl_user where student_id = ", id, > sep=""), " order by date_time", sep="") > > student <- sqldf(query) > > everything works fine in case the id is "21328", "82882", or something like > that. But, when id is something like "78789D", there is an error: > Error in sqliteExecStatement(con, statement, bind.data) : > RS-DBI driver: (error in statement: unrecognized token: "78789D") > > I tried replacing single quotes with double, but it still doesn't work... > > thanks, > Srecko > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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.
Why don't you print the 'query' variable with each id value and consider what the SQL syntax is for number and string literals. Then study the use of escaping in strings ("\\") to fix the query. --------------------------------------------------------------------------- Jeff Newmiller The ..... ..... Go Live... DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/Batteries O.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --------------------------------------------------------------------------- Sent from my phone. Please excuse my brevity. srecko joksimovic <sreckojoksimovic at gmail.com> wrote:>Hi, > >when I generate query using sqldf library, like this: >query = paste(paste("select * from tbl_user where student_id = ", id, > sep=""), " order by date_time", sep="") > >student <- sqldf(query) > >everything works fine in case the id is "21328", "82882", or something >like >that. But, when id is something like "78789D", there is an error: >Error in sqliteExecStatement(con, statement, bind.data) : > RS-DBI driver: (error in statement: unrecognized token: "78789D") > >I tried replacing single quotes with double, but it still doesn't >work... > >thanks, >Srecko > > [[alternative HTML version deleted]] > >______________________________________________ >R-help at 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.