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("\r", " ", query) query <- gsub("\n", " ", 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?

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) }