How to Gracefully Include Formatted SQL Strings in an R Script

Can I gracefully include formatted SQL strings in an R script?

you can override the %+% operator to have better string concatination syntax:

'%+%' <- function(x,y) paste(x,y,sep="")

y<-"y1"
x<-"somethingorother"
query<-
'SELECT DISTINCT x AS ' %+% x %+%',\n'    %+%
'                y AS ' %+% y %+% '\n'    %+%
' FROM tbl
 WHERE id=%s
 AND num=%d'

cat(query,"\n")

yields:

> cat(query,"\n")
SELECT DISTINCT x AS somethingorother,
                y AS y1
 FROM tbl
 WHERE id=%s
 AND num=%d 

Is there a better way to code this sqlQuery in R?

I would use something like this:

stsample<-sqlQuery(odcon,
    paste("
####DATASET CONSTRUCTION QUERY #########
    select 
    *  
    from 
    BOB.DESIGNSAMPLE T1, 
    BOB.DESIGNSUBJECTGROUP T2, 
    BOB.DESIGNEVENT T3, 
    BOB.CONFIGSAMPLETYPES T4 
    WHERE 
    T1.SUBJECTGROUPID = T2.SUBJECTGROUPID 
    AND T1.TREATMENTEVENTID = T3.TREATMENTEVENTID 
    AND T1.SAMPLETYPEKEY = T4.SAMPLETYPEKEY 
    AND T1.STUDYID = T2.STUDYID 
    AND T1.STUDYID = T3.STUDYID 
    AND T1.STUDYID = 
###################################   
    ", as.character(chstudid), sep="")
    )

How to use a variable name in a SQL statement?

This should work:

foo = 23;

sqlStatement <- paste("select surname from names WHERE age =",foo,'"',sep="")

dbGetQuery(con, sqlStatement;)

Bind variables in R DBI

For anyone coming to this question like I just did after googling for rsqlite and dbgetpreparedquery, it seems that in the latest version of rsqlite you can run a SELECT query with bind variables. I just ran the following:

query <- "SELECT probe_type,next_base,color_channel FROM probes WHERE probeid=?"
probe.types.df <- dbGetPreparedQuery(con,que,bind.data=data.frame(probeids=ids))

This was relatively fast (selecting 2,000 rows out of a 450,000 row table) and is incredibly useful.

FYI.

How to keep the spaces at the end and/or at the beginning of a String?

Even if you use string formatting, sometimes you still need white spaces at the beginning or the end of your string. For these cases, neither escaping with \, nor xml:space attribute helps. You must use HTML entity   for a whitespace.

Use   for non-breakable whitespace.

Use for regular space.

How to parse a command line with regular expressions?

I tend to use regexlib for this kind of problem. If you go to: http://regexlib.com/ and search for "command line" you'll find three results which look like they are trying to solve this or similar problems - should be a good start.

This may work:
http://regexlib.com/Search.aspx?k=command+line&c=-1&m=-1&ps=20

---

Related Topics