5

I have a fairly complex SQL query that I am trying to run through RODBC that involves defining variables. A simplified version looks like this:

DECLARE @VARX CHAR = 'X';
SELECT * FROM TABLE WHERE TYPE = @VARX;

Running this code works just fine. This fails:

library(RODBC)
q <- "DECLARE @VARX CHAR = 'X';\nSELECT * FROM TABLE WHERE TYPE = @VARX;"
sqlQuery(ch, q)
# returns character(0)

I have found through experimentation that the first statement before the semicolon is executed, but the rest is not. There is no error--it just seems that everything after the semicolon is ignored. Is there a way to execute the full query?

I'm using SQL server by the way.

NOTE: I asked this question before and it was marked as a duplicate of this question, but they are asking completely different things. In this question I would like to execute a script that contains multiple statements, and in the other the author is only trying to execute a single statement.

crf
  • 1,810
  • 3
  • 15
  • 23
  • Try removing `\n` before select and run. – Lukasz Szozda Aug 15 '17 at 13:58
  • @lad2025 no good – crf Aug 15 '17 at 14:04
  • `q <- "DECLARE @VARX CHAR = 'X' SELECT * FROM TABLE WHERE TYPE = @VARX"` – Lukasz Szozda Aug 15 '17 at 14:12
  • 1
    AFAIK - you can only send one SQL call in an ODBC connection. For multiple queries, use a stored procedure and call/execute it from R. For your example, simply pass parameter from R without `DECLARE`. – Parfait Aug 15 '17 at 14:15
  • @lad2025 still doesn't seem to work. – crf Aug 15 '17 at 14:21
  • @Parfait I see... alright I guess that's the answer if it's true Is that a bug? It certainly seems like a limitation. – crf Aug 15 '17 at 14:21
  • That's not a bug but the sole design. In most DB APIs, cursors run one statement at a time. Some databases do not even have extended languages like MSSQL's TSQL, or Oracle's PL/SQL. ODBC is agnostic to be used across RDBMs (SQLite, MySQL, etc.). – Parfait Aug 15 '17 at 16:48
  • @Parfait alright.. so is there a way to run a script that contains variable definitions from RODBC? – crf Aug 15 '17 at 16:49
  • As mentioned above, why not pass parameters into the `SELECT` statement? No need for `DECLARE` or variables. See [ROBDCext](https://cran.r-project.org/web/packages/RODBCext/vignettes/Parameterized_SQL_queries.html) to parameterize queries. – Parfait Aug 15 '17 at 16:51
  • @Parfait Just makes the code a bit cleaner IMO. The actual script I'm running is quite complicated and refers to certain parameters multiple times. It would be simpler to be able to pass those in once as a variable declaration than insert them in multiple places across the script. But I guess that's the way I'll have to go. If you write your comment as an answer I'll accept it. – crf Aug 15 '17 at 16:53
  • Then use a stored procedure and have R call it. Again mentioned above. – Parfait Aug 15 '17 at 16:53

1 Answers1

1

You can try this:

library(RODBC)
library(stringr)

filename = "filename.sql"   ### file where the sql code is stored
queries <- readLines(filename) ### read the sql file into R

queries1 = str_replace_all(queries,'--.*$'," ")  ### remove any commented lines
queries2 = paste(queries1, collapse = '\n') ### collapse with new lines
queries3 = unlist(str_split(queries2,"(?<=;)")) ### separate individual queries

set up the odbc connection at this point and run the for loop below. you can also modify the queries to add/change variables within the queries before running the for loop

for (i in 1:length(queries3)) {
  print(i)
  sqlQuery(conn, queries3[i])
}

after the for loop is done, you can pull any volatile or regular tables generated in your session into R using sqlQuery(). I havent tested this extensively and there might be cases where it can fail, but it worked for what I was doing

Abhi
  • 46
  • 2