Cursor-loop with DBI

[taran]

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?

[jlacko]

I don't think SQLite as a backend supports variable declaration - so your code crashes on the first line, never getting as far as the cursor part.

Are you certain you are running your code against the same backend from both SSMS and R?

[taran]

Ah sorry, so the example won't work with SQLite either way - thanks for the clarification! But OK, the problem is still the same.

What do you mean by backend, exactly? All other SQL-queries I have run work fine, and produce identical results in SSMS and R.

[jlacko]

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.