[R-sig-DB] [R] Argument to database

[Gabor Grothendieck]

On Tue, Oct 26, 2010 at 3:23 PM, Nilza BARROS <nilzabarros using gmail.com> wrote:
> Dear Rusers,
>
> I am using Rscript and I'd like to use arguments as an input.
> I need it because I will use these arguments to consult Mysql  Databse using
> (SELECT) .
> I need different select to each Model Type and date.
>
> ./GrafDens.R [ModelType trim date ]
>
> The script I am using it is working but I'd like to know if there is
> something more efficient.
>
>
> Below here what  I am using:
> ==================
> #! /usr/bin/Rscript --vanilla
> args <- commandArgs(TRUE)
>
> if (length(args)!=4 )
> {
> print("********************************************")
> print("Enter the arguments   Model (20,21,...) - Trim(01,02,03,04) - Year
> (AAAA)")
> print("********************************************")
> stop("Incorrect arguments length")
> }
> npt.freq <- args[1]
> npt.trim <-args[2]
> npt.ano <- args[3]
>
>
> MES1.00 <- paste(npt.ano,npt.mes,"__","00",sep="")
> MES2.00 <- paste(npt.ano,npt.mes,"__","00",sep="")
> MES3.00 <- paste(npt.ano,npt.mes,"__","00",sep="")
>
>
> ===How I use the strings above to SELECT my data:
>
> drv=dbDriver("MySQL")
> con=dbConnect(drv,dbname='xxx',user='xx',password='xx')
> dados <- dbGetQuery(con,paste("SELECT
> OBS_StationNo as station_no,
> OBS_date as obsdate,
> FCT_fdate as fdate,
> FCT_mtype as fct_mtype,
> FCT_mrun as  fct_mrun,
> FROM VWFct_Obs
> WHERE
> FCT_mtype=",npt.mtype,
> " AND OBS_StationNo <> 'NULL'
> AND (FCT_fdate LIKE  ",  paste("'",MES1.00,"'",sep=""),
> " OR FCT_fdate LIKE  ", paste("'",MES1.12,"'",sep=""),
> " OR  FCT_fdate LIKE ", paste("'",MES2.00,"'",sep=""),
> " OR  FCT_fdate LIKE ", paste("'",MES2.12,"'",sep=""),
> " OR  FCT_fdate LIKE ", paste("'",MES3.00,"'",sep=""),
> "OR  FCT_fdate LIKE  " ,paste("'",MES3.12,"'",sep="")," )" ,sep=""))
> #

You may wish to use sprintf to construct your strings

  s <- sprintf("select * from mytable where mycolumn = '%s' ", myvalue)
  out <- dbGetQuery(con, s)

or else the perl-like string interpolation facilities of the gsubfn
package. Just preface any function call with fn$ and you get string
interpolation in the arguments (subject to certain rules):

  library(gsubfn)
  out <- fn$dbGetQuery(con, "select * from mytable where mycolumn =
'$myvalue' ")


-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com