Hello,
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?