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

Question

I'm writing an R script to get some database data and then do stuff with it, using the RODBC package. Currently all my sqlQuery commands are one long string;

stsample<-sqlQuery(odcon, paste"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 = ", chstudid, sep=""))
head(stsample)

which looks ugly and is hard to read/update. I've tried putting them multiline, but then new line characters get in the way, currently my best is this using lots of paste's;

stsample<-sqlQuery(odcon,
    paste(
        "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 = ",chstudid,
        sep="")
    )
head(stsample)

But I don't like having to put quotes around everyline, and getting my whitespace correct. Is there a better way ?

Take a look on http://stackoverflow.com/questions/1630724/can-i-gracefully-include-formatted-sql-strings-in-an-r-script — Marek, Feb 25 '10 at 11:07
This question and the one Marek links too both allude to problems with the new line chars. I use the paste syntax like gd047 uses in his answer below and have no issues with new line chars. I've used that syntax with both SQL Server and Oracle. What database is giving you grief about new line syntax? — JD Long, Feb 25 '10 at 17:01
I haven't had any errors as a result of newlines, I just had a bad feeling about having them, but maybe that's me being wrong... (the db is oracle if it matters) — PaulHurleyuk, Feb 25 '10 at 17:50

score 6 · Accepted Answer · answered Feb 25 '10 at 12:18

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="")
    )

score 2 · Answer 2 · answered Feb 25 '10 at 15:21

2

What about using gsub("\n", " ", "long multiline select string") instead of paste?

answered Feb 25 '10 at 15:21

Karsten W.

17,826
11
69
103

score 0 · Answer 3 · answered Jul 02 '19 at 09:51

This is a really old question, but thought this may be useful to someone.

One thing I have found useful is the GetoptLong package, which provides the qq() function. I think it is inspired by Perl, but essentially it provides a way to do a multiline string with easy variable interpolation. For example:

library(GetoptLong)

tableName <- "myTable"
id <- 42

sqlQuery(odcon, qq("
    SELECT * FROM @{tableName}
    WHERE id = @{id}
    LIMIT 1
")

Obviously I should mention the usual caveat that this is a bad idea if you are working directly with user input and it would be better to use some kind of prepared statement in that case.

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

3 Answers3