How to read the contents of an .sql file into an R script to run a query?

Question

I have tried the readLines and the read.csv functions but then don't work.

Here is the contents of the my_script.sql file:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE HireDate >= '1-july-1993'

and it is saved on my Desktop.

Now I want to run this query from my R script. Here is what I have:

conn = connectDb()

fileName <- "C:\Users\me\Desktop\my_script.sql"
query <- readChar(fileName, file.info(fileName)$size)

query <- gsub("
", " ", query)
query <- gsub("
", " ", query)
query <- gsub("???", " ", query)

recordSet <- dbSendQuery(conn, query)
rate <- fetch(recordSet, n = -1)

print(rate)
disconnectDb(conn)

And I am not getting anything back in this case. What can I try?

question from:https://stackoverflow.com/questions/44853322/how-to-read-the-contents-of-an-sql-file-into-an-r-script-to-run-a-query

Answer 1

I've had trouble with reading sql files myself, and have found that often times the syntax gets broken if there are any single line comments in the sql. Since in R you store the sql statement as a single line string, if there are any double dashes in the sql it will essentially comment out any code after the double dash.

This is a function that I typically use whenever I am reading in a .sql file to be used in R.

getSQL <- function(filepath){
  con = file(filepath, "r")
  sql.string <- ""

  while (TRUE){
    line <- readLines(con, n = 1)

    if ( length(line) == 0 ){
      break
    }

    line <- gsub("\t", " ", line)

    if(grepl("--",line) == TRUE){
      line <- paste(sub("--","/*",line),"*/")
    }

    sql.string <- paste(sql.string, line)
  }

  close(con)
  return(sql.string)
}