[R] Unrecognized token

[Jeff Newmiller]

> 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
> <sreckojoksimovic at gmail.com> wrote:
>       thanks, Jeff,
> good point... I'll try that
> 
> 
> On Tue, Sep 17, 2013 at 9:43 AM, Jeff Newmiller
> <jdnewmil at 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:<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.
> 
> 
> 
> 
>

---------------------------------------------------------------------------
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
---------------------------------------------------------------------------