Single Quote R for SQL Query

Question

Hello I have the following data that I want to paste into an SQL query through a R connection.

UKWinnersID<-c("1W167X6", "QM6VY8", "ZDNZX0", "8J49D8", "RGNSW9", 
"BH7D3P1", "W31S84", "NTHDJ4", "H3UA1", "AH9N7", 
"DF52B68", "K65C2", "VGT2Q0", "93LR6", "SJAJ0", 
"WQBH47", "CP8PW9", "5H2TD5", "TFLKV4", "X42J1" )

The query / code in R is as following:

UKSQL6<-data.frame(sqlQuery(myConn, paste("SELECT TOP 10000 [AxiomaDate]
                      ,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3],[Factor4],[Factor5]
                      ,[Factor6],[Factor7],[Factor8],[Factor9],[Factor10],[Factor11],[Factor12]
                      ,[Factor13],[Factor14],[Factor15]FROM [PortfolioAnalytics].[Data_Axioma].[SecurityExposures]
                      Where AxiomaDate IN (
                        SELECT   MAX(AxiomaDate)
                        FROM     [PortfolioAnalytics].[Data_Axioma].[FactorReturns]
                                  GROUP BY MONTH(AxiomaDate), YEAR(AxiomaDate))
                      AND RiskModelID = 8
                      AND  AxiomaID IN(",paste(UKWinnersID, collapse = ","),")")))

I am pasting the UKWinnersID in the last line of the code above but that format of the UKWinnersID needs to be as ('1W167X6', 'QM6VY8', 'ZDNZX0'.. etc) with a single quote which I just cant get to work.

The strings are not being quoted at all, which will fail (in all SQL engines, I think). Try `paste(sQuote(UKWinnersID), ...` or `paste(dbQuoteString(UKWinnersID), ...`. — r2evans, Oct 18 '17 at 13:41
....paste(sQuote(UKWinnersID), collapse = ","),")"))) pastes the values with a curve quote ie (‘1W167X6’,‘QM6VY8’) as opposed to 'straight' line quotes ie ('1W167X6' ,'QM6VY8') which is what I want? — user8491385, Oct 18 '17 at 13:51
Run this first, then `sQuote` will give you straight line quotes: `options(useFancyQuotes = FALSE)` — David Klotz, Oct 18 '17 at 14:06
Oh, sorry user8491385, I did not correctly read your comment, @DavidKlotz is correct (thanks). I ran into that some time ago (ok, a long time ago) and forever dumped it from my brain. (I've deleted the previous comment to not confuse anybody, but the single/double quote discussion link is https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql) — r2evans, Oct 18 '17 at 14:11
MySQL does not have the `TOP` clause. Please tag actual RDBMS. Also, please include all `library()` lines so we know what DB API you are using. — Parfait, Oct 18 '17 at 14:50

score 0 · Answer 1 · answered Oct 18 '17 at 15:37

Consider running a parameterized query using the RODBCext package (extension of RODBC), assuming this is the API being used. Parameterized queries do more than insulate from SQL injection but abstracts data from code and avoids the messy quote enclosure and string interpolation and concatenation for cleaner, maintainable code.

Below replaces your TOP 10000 into TOP 500 for each of the 20 ids:

library(RODBC)
library(RODBCext)

conn <- odbcConnect("DBName", uid="user", pwd="password")

ids_df <- data.frame(UKWinnersID = c("1W167X6", "QM6VY8", "ZDNZX0", "8J49D8", "RGNSW9", 
                                    "BH7D3P1", "W31S84", "NTHDJ4", "H3UA1", "AH9N7", 
                                    "DF52B68", "K65C2", "VGT2Q0", "93LR6", "SJAJ0", 
                                    "WQBH47", "CP8PW9", "5H2TD5", "TFLKV4", "X42J1"))

# SQL STATEMENT (NO DATA)
query <- "SELECT TOP 500 [AxiomaDate], [RiskModelID], [AxiomaID], [Factor1],[Factor2]
                 , [Factor3], [Factor4], [Factor5], [Factor6], [Factor7], [Factor8]
                 , [Factor9], [Factor10], [Factor11], [Factor12]
                 , [Factor13], [Factor14], [Factor15]
          FROM [PortfolioAnalytics].[Data_Axioma].[SecurityExposures]
          WHERE AxiomaDate IN (
                   SELECT   MAX(AxiomaDate)
                   FROM     [PortfolioAnalytics].[Data_Axioma].[FactorReturns]
                   GROUP BY MONTH(AxiomaDate), YEAR(AxiomaDate)
                )
            AND RiskModelID = 8
            AND AxiomaID = ?"

# PASS DATAFRAME VALUES TO BIND TO QUERY PARAMETERS
UKSQL6 <- sqlExecute(conn, query, ids_df, fetch=TRUE)

odbcClose(conn)

Alternatively, if you really need to use the IN() clause:

# SQL STATEMENT (NO DATA)
query <- paste("SELECT TOP 10000 

                  ...same as above...

                  AND AxiomaID IN (", paste(rep("?", nrow(ids_df)), collapse=", "), ")")

# TRANSPOSE DATA FRAME FOR COLUMN EQUAL TO ? PLACEHOLDERS
UKSQL6 <- sqlExecute(conn, query, t(ids_df), fetch=TRUE)

Single Quote R for SQL Query

1 Answers1