Yes, you are right... the other is definitely not a valid query. thanks
On Tue, Sep 17, 2013 at 11:22 AM, Jeff Newmiller <[email protected]>wrote: > > 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<https://stat.ethz.ch/mailman/listinfo/r-help> >> >PLEASE do read the posting guide >> >http://www.R-project.org/**posting-guide.html<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 > ------------------------------**------------------------------** > --------------- > [[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.
