Cursor Does Not Exist After Executing Redshift Stored Procedure
I have created a cursor-based stored proc in Redshift according to the aws docs sample at https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-result-set.html. However, when calling it using the datagrip console using the following sql:-
BEGIN;
CALL myproc('mycursor');
FETCH ALL FROM mycursor;
COMMIT;
I receive the error [Amazon](500310) Invalid operation: cursor "mycursor" does not exist;.
If I try the exact same execution sql in sqlworkbench/j build 124, it runs successfully and i get the expected table of results. Curiously, it does fail in sqlworkbench/j build 125 with this error and also in the AWS Redshift query editor. I think what may be happening here is some kind of autocommit and so the cursor goes out of scope as soon as the call statement completes and thus "does not exist" by the time the fetch all statement is reached.
Is this somthing that can be fixed in datagrip or is this a low level driver thing that I can't get at?
Please sign in to leave a comment.
@Dan Anselm,
You can manage transaction control in data source settings:
and per console settings:
In your case, it looks like you need to switch to `Manual`.
doh, i am so sorry, i didn't see those options! many thanks, all sorted now :)
Cool!