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
<[email protected]> wrote:
thanks, Jeff,
good point... I'll try that
On Tue, Sep 17, 2013 at 9:43 AM, Jeff Newmiller
<[email protected]> 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:<[email protected]> 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 <[email protected]> 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]]
>
>______________________________________________
>[email protected] 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 Newmiller The ..... ..... Go Live...
DCN:<[email protected]> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------______________________________________________
[email protected] 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.
