[R] Searching for a string in RSQLite

[David James]

Single quotes in a string are escaped by putting two single quotes in
a row.  E.g., 

   state <- "mess: '' ("

Regards,

--
David

Duncan Murdoch wrote:
> I'd like to search for a particular string in an SQLite database using
> RSQLite, but I'm running into problems constructing the query
> properly, because of embedded quotes and parens in the string.
> 
> Is there a function that escapes these for me, or some other fixup
> that would let me do the queries below?  In the real situation I don't
> have control over what strings get searched for.
> 
> Example based on ?SQLite:
> 
> > library(RSQLite)
> > m <- dbDriver("SQLite")
> > con <- dbConnect(m, dbname = "base.dbms")
> > data(USArrests)
> > dbWriteTable(con, "USArrests", USArrests, overwrite = T)
> [1] TRUE
> > state <- "Wyoming"
> 
> # this works fine:
> 
> > dbGetQuery(con, paste("SELECT * from USArrests where row_names='",state,"'",sep=""))
>   row_names Murder Assault UrbanPop Rape
> 1   Wyoming    6.8     161       60 15.6
> 
> # Buf if the search string contains characters that SQL interprets, I
> # get an error
> 
> > state <- "messy:  ' ("
> > dbGetQuery(con, paste("SELECT * from USArrests where row_names='",state,"'",sep=""))
> Error in sqliteExecStatement(con, statement) : 
>         RS-DBI driver: (error in statement: near "(": syntax error)
> 
> Duncan Murdoch
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html