Re: [R] Unrecognized token
Maybe you should escape the single quotes something like ' id ' ? On Sep 17, 2013, at 6:03 PM, srecko joksimovic sreckojoksimo...@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@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. __ 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.
Re: [R] Unrecognized token
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-boun...@r-project.org [mailto:r-help-boun...@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@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. __ 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.
Re: [R] Unrecognized token
Thanks William, actually, the combination that works is: with(list(id=c(1234,abcd)), paste(paste(select * from tbl_user where student_id = '78789D', sep=), order by date_time, sep=), maybe I should try to replace double quotes with single (opposite of what I was doing...) On Tue, Sep 17, 2013 at 9:16 AM, William Dunlap wdun...@tibco.com wrote: 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-boun...@r-project.org [mailto:r-help-boun...@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@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. [[alternative HTML version deleted]] __ 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.
[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@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.
Re: [R] Unrecognized token
id - c(21328,78789D) query - paste(paste(select * from tbl_user where student_id = , id,sep=), order by date_time, sep=) query [1] select * from tbl_user where student_id = 21328 order by date_time [2] select * from tbl_user where student_id = 78789D order by date_time Now, does the second string look like valid SQL to you? In particular, the 78789D is a problem. On the other hand... query - paste(paste(select * from tbl_user where student_id = ', id,sep=), ' order by date_time, sep=) query [1] select * from tbl_user where student_id = '21328' order by date_time [2] select * from tbl_user where student_id = '78789D' order by date_time As others have pointed out, in this case escaping does not appear to be key to getting valid SQL syntax... but looking at the query before shipping it off to a database engine seems to me to be an obvious technique you should learn. On Tue, 17 Sep 2013, srecko joksimovic wrote: There is no difference, the same query structure is in the both cases:6683 character character select * from students where student_id = 6683 order by date_time 4738D character character select * from students where student_id = 4738D order by date_time and still is the same error On Tue, Sep 17, 2013 at 9:47 AM, srecko joksimovic sreckojoksimo...@gmail.com wrote: thanks, Jeff, good point... I'll try that On Tue, Sep 17, 2013 at 9:43 AM, Jeff Newmiller jdnew...@dcn.davis.ca.us wrote: 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:jdnew...@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 sreckojoksimo...@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@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. --- Jeff NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k ---__ 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.
Re: [R] Unrecognized token
Yes, you are right... the other is definitely not a valid query. thanks On Tue, Sep 17, 2013 at 11:22 AM, Jeff Newmiller jdnew...@dcn.davis.ca.uswrote: id - c(21328,78789D) query - paste(paste(select * from tbl_user where student_id = , id,sep=), order by date_time, sep=) query [1] select * from tbl_user where student_id = 21328 order by date_time [2] select * from tbl_user where student_id = 78789D order by date_time Now, does the second string look like valid SQL to you? In particular, the 78789D is a problem. On the other hand... query - paste(paste(select * from tbl_user where student_id = ', id,sep=), ' order by date_time, sep=) query [1] select * from tbl_user where student_id = '21328' order by date_time [2] select * from tbl_user where student_id = '78789D' order by date_time As others have pointed out, in this case escaping does not appear to be key to getting valid SQL syntax... but looking at the query before shipping it off to a database engine seems to me to be an obvious technique you should learn. On Tue, 17 Sep 2013, srecko joksimovic wrote: There is no difference, the same query structure is in the both cases:6683 character character select * from students where student_id = 6683 order by date_time 4738D character character select * from students where student_id = 4738D order by date_time and still is the same error On Tue, Sep 17, 2013 at 9:47 AM, srecko joksimovic sreckojoksimo...@gmail.com wrote: thanks, Jeff, good point... I'll try that On Tue, Sep 17, 2013 at 9:43 AM, Jeff Newmiller jdnew...@dcn.davis.ca.us wrote: 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 NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --**--** --- Sent from my phone. Please excuse my brevity. srecko joksimovic sreckojoksimo...@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@r-project.org mailing list https://stat.ethz.ch/mailman/**listinfo/r-helphttps://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/**posting-guide.htmlhttp://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. --**--** --- Jeff NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --**--** --- [[alternative HTML version deleted]] __ 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.
Re: [R] Unrecognized token
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 NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. srecko joksimovic sreckojoksimo...@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@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. __ 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.
Re: [R] Unrecognized token
thanks, Jeff, good point... I'll try that On Tue, Sep 17, 2013 at 9:43 AM, Jeff Newmiller jdnew...@dcn.davis.ca.uswrote: 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 NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. srecko joksimovic sreckojoksimo...@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@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. [[alternative HTML version deleted]] __ 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.
Re: [R] Unrecognized token
There is no difference, the same query structure is in the both cases: 6683 character character select * from students where student_id = 6683 order by date_time 4738D character character select * from students where student_id = 4738D order by date_time and still is the same error On Tue, Sep 17, 2013 at 9:47 AM, srecko joksimovic sreckojoksimo...@gmail.com wrote: thanks, Jeff, good point... I'll try that On Tue, Sep 17, 2013 at 9:43 AM, Jeff Newmiller jdnew...@dcn.davis.ca.uswrote: 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 NewmillerThe . . Go Live... DCN:jdnew...@dcn.davis.ca.usBasics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. srecko joksimovic sreckojoksimo...@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@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. [[alternative HTML version deleted]] __ 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.