So I have to run a large SQL-program from R (for reasons too long to explain here). However, my replication process came to a halt when one of the steps in SQL used a cursor loop.
When trying to run the SQL-statement using DBI::dbExecute, I get a syntax error - even though the code works fine when I test it in SSMS.
I tried to create a reproducible example:
con <- dbConnect(RSQLite::SQLite(), ":memory:")
iris <- iris %>%
mutate(test = Sepal.Length)
dbCreateTable(con, "iris", iris)
dbAppendTable(con, "iris", iris)
b <- "
DECLARE @var1 INTEGER
DECLARE @var2 VARCHAR(MAX)
DECLARE iris_cur CURSOR
FOR SELECT
test,
Species
FROM
iris;
OPEN iris_cur;
FETCH NEXT FROM iris_cur INTO
@var1,
@var2;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @var1 + CAST(@var2 AS varchar);
FETCH NEXT FROM iris_cur INTO
@var1,
@var2;
END;
CLOSE iris_cur;
DEALLOCATE iris_cur;"
dbExecute(con, b)
This code (which does nothing useful, by the way) works in SSMS, but does not run from within R using DBI, recieving the error "Error: near "DECLARE": syntax error".
Does anyone see what I am doing wrong here, or does the DBI package simply not support cursors?
By backend I mean the SQL engine that actually executes your script. All that DBI does is that it passes your command as a string from R to the database engine, and then processes a result (and sometimes not even that - consider dbSendQuery without dbFetch).
Thinking a bit harder about the issue: cursors and variable declarations are not a part of ANSI SQL, but are a part of the various procedural extensions (like PL/SQL in Oracle). It may be that the procedural extensions can not be passed the way of dbExecute statement.
If that is the case - and I am not in a position to check right now - would it be a possibility in your use case to wrap your "cursor code" into a stored procedure and execute as such from R? I am confident that a stored procedure can be called via dbExecute.