Cursor Does Not Exist After Executing Redshift Stored Procedure

Completed

This is identical to https://intellij-support.jetbrains.com/hc/en-us/community/posts/360004336079-Cursor-Does-Not-Exist-After-Executing-Redshift-Stored-Procedure

The suggestion in the comments no longer seems to be the case. Setting transaction to manual in both the database options as well as the query console has no impact. We still get the error 'cursor "<cursor_name>" does not exist.

6 comments
Comment actions Permalink

Thanks Vasily.  Changing the 'For selection execute' option was the missing piece of the puzzle for me.  

1
Comment actions Permalink

Jonnoadams,

Could you try to switch to previous version of Redshift JDBC driver and try it there?

Also, it would be handy if you provide a sample script to reproduce the issue.

0
Comment actions Permalink

This is still an issue.

0
Comment actions Permalink

I can reproduce with very similar code:

begin;
call public.nameOfMyProcedure(1234, 90, 'myCursor')
fetch all from myCursor
;

Results

datawarehouse> begin
[2022-06-22 07:43:24] completed in 33 ms
datawarehouse> call public.nameOfMyProcedure(1234, 90, 'myCursor')
[2022-06-22 07:43:25] 1 row retrieved starting from 1 in 332 ms (execution: 53 ms, fetching: 279 ms)
datawarehouse> fetch all from myCursor
[2022-06-22 07:43:25] [34000] ERROR: cursor "mycursor" does not exist

Transaction mode is set to manual.

0
Comment actions Permalink

Jcronin,

I see, according to console output you run query as separate statements. It should me like that:


So, let's do the following:

  1. Set Console Tx Mode to Manual
  2. Adjust Execution Mode and set Exactly as one statement for separate Execute action or for selection.

 

In general I use the following settings:


Proper way to execute:

0

Please sign in to leave a comment.