Re: [R] Custom Command to Generate SQL

From: Gabor Grothendieck <ggrothendieck_at_gmail.com>
Date: Tue 20 Jun 2006 - 12:34:02 EST

Hi, Here is an example of paste:

> paste("and", 1:3, collapse = " ")
[1] "and 1 and 2 and 3"

in which and was prepended to each of the vector entries and then everything was collapsed together. In our situation we don't actually want the first 'and' so we substitute for it, using sub,
the first portion of the SQL statement.

Suggest you turn on debugging:

debug(retrieve) # undebug(retrieve) will turn it back off

and run the function and at each try printing out various partial expressions to see what they do.

Regards.

On 6/19/06, Alex Restrepo <alex_restrepo@hotmail.com> wrote:
> Hi Gabor:
>
> Thanks for the great example. I am an R newbie, so please forgive my
> question, but
> could you describe what the sub() function is doing in your example? Why is
> there an "and" in the first argument to the sub() function?
>
>
> Many Thanks:
>
> Alex
>
>
> >From: "Gabor Grothendieck" <ggrothendieck@gmail.com>
> >To: "Alex Restrepo" <alex_restrepo@hotmail.com>
> >CC: r-help@stat.math.ethz.ch
> >Subject: Re: [R] Custom Command to Generate SQL
> >Date: Mon, 19 Jun 2006 01:21:53 -0400
> >
> >This generates the sql statement so just pass that to your database:
> >
> >retrieve <- function(...) {
> > args <- list(...)
> > sub("and", "select * from people where",
> > paste(rbind("and", names(args), "=", dQuote(args)), collapse = " "))
> >}
> >
> ># test
> >retrieve(firstname = "JOHN", middlename = "WILLIANS", lastname = "FORD")
> >
> >On 6/19/06, Alex Restrepo <alex_restrepo@hotmail.com> wrote:
> >>Hi:
> >>
> >>I would like to create a custom command in R which generates SQL, which is
> >>then processed via RODBC.
> >>
> >>For example, the user would type:
> >>
> >> retrieve firstName('JOHN') middlleName('WILLIAMS')
> >>lastName('FORD')
> >>
> >>This would generate the following SQL which would then be processed by
> >>RODBC:
> >>
> >>Select *
> >> from people
> >> where firstName = 'JOHN' and
> >> middleName = 'WILLIAMS' and
> >> lastName = 'FORD'
> >>
> >>Does anyone have a recommendation? Any ideas would be greatly
> >>appreciated.
> >>
> >>Alex
> >>
> >>______________________________________________
> >>R-help@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
> >>
>
>
>



R-help@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 Received on Tue Jun 20 12:41:04 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Tue 20 Jun 2006 - 14:11:34 EST.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.