creating a looped SQL QUERY using RODBC in R

Question

First and foremost - thank you for taking your time to view my question, regardless of if you answer or not!

I am trying to create a function that loops through my df and queries in the necessary data from SQL using the RODBC package in R. However, I am having trouble setting up the query, since the parameter of the query change through each iteration (example below)

So my df looks like this:

     ID     Start_Date    End_Date    
     1       2/2/2008      2/9/2008
     2       1/1/2006      1/1/2007
     1       5/7/2010      5/15/2010
     5       9/9/2009      10/1/2009

How would I go about specifying the start date and end date in my sql program?

here's what i have so far:

 data_pull <- function(df) {
    a <- data.frame()
    b <- data.frame()

    for (i in df$id)
{ 
    dbconnection <- odbcDriverConnect(".....")

    query <- paste("Select ID, Date, Account_Balance from Table where ID = (",i,") and Date > (",df$Start_Date,") and Date <= (",df$End_Date,")")

    a <- sqlQuery(dbconnection, paste(query))
    b <- rbind(b,a)
 }
  return(b)
 }

However, this doesn't query in anything. I believe it has something to do with how I am specifying the start and the end date for the iteration.

If anyone can help on this it would be greatly appreciated. If you need further explanation, please don't hesitate to ask!

Answer 1

A couple of syntax issues arise from current setup:

1. LOOP: You do not iterate through all rows of data frame but only the atomic ID values in the single column, df$ID. In that same loop you are passing the entire vectors of df$Start_Date and df$End_Date into query concatenation.

2. DATES: Your date formats do not align to most data base date formats of 'YYYY-MM-DD'. And still some others like Oracle, you require string to data conversion: TO_DATE(mydate, 'YYYY-MM-DD').

A couple of aforementioned performance / best practices issues:

3. PARAMETERIZATION: While parameterization is not needed for security reasons since your values are not generated by user input who can inject malicious SQL code, for maintainability and readability, parameterized queries are advised. Hence, consider doing so.

4. GROWING OBJECTS: According to Patrick Burn's Inferno Circle 2: Growing Objects, R programmers should avoid growing multi-dimensional objects like data frames inside a loop which can cause excessive copying in memory. Instead, build a list of data frames to rbind once outside the loop.

---

With that said, you can avoid any looping or listing needs by saving your data frame as a database table then joined to final table for a filtered, join query import. This assumes your database user has CREATE TABLE and DROP TABLE privileges.

# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
          Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
          End_Date = as.Date(End_Date, format="%m/%d/%Y")
})

# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)

# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance 
      FROM Table t
      INNER JOIN myRData r 
        ON r.ID = t.ID 
        AND t.Date BETWEEN r.Start_Date AND r.End_Date"

final_df <- sqlQuery(dbconnection, q)