Re: SQL cursor defined two different ways?

I came across this just recently. I normally use the dynamic SQL approach, but in this case I used two cursors. Some background. I put my sql logic in its own procedures. Usually one procedure per statement. So the open C1, fetch from C1, and close C1 statements are all in separate procedures. The declare cursor needs to be in the same procedure as the open if you are using any local host variables because the host variables are scoped together. Anyway, for this most recent modification, it turned out to be easier for me to just put my if statement around the open, fetch, and close statements. You can have two declares, but that amounts to just having a bunch of templates defined since the declare cursor isn't executable in the first place. So you can do something like this:

dcl-proc OpenCursor;

exec sql
declare cursor c1 for
select ...;

exec sql
declare cursor c1 for
select ...;

if UseC1;
exec sql open c1;
else;
exec sql open c2;
endif;
end-proc;

dcl-proc CloseCursor;

if UseC1;
exec sql close c1;
else;
exec sql close c2;
endif;
end-proc;

dcl-proc FetchCursor;

if UseC1;
exec sql fetch from c1 ...;
else;
exec sql fetch from c2 ...;
endif;
end-proc;

You only have to open close and fetch the cursor you want, not both of them. And as has been mentioned before, the declare cursor is simply a declaration used by the precompiler, not executable code. In fact, the RPG compiler doesn't even use the declare cursor. When using dynamic SQL, I generally put the declare cursor and declare statement declarations up at the head of my program right after set options and before any real code.

Mark Murphy
Atlas Data Systems
mmurphy@xxxxxxxxxxxxxxx


-----David Gibbs <david@xxxxxxxxxxxx> wrote: -----
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
From: David Gibbs <david@xxxxxxxxxxxx>
Date: 11/30/2016 12:16PM
Subject: Re: SQL cursor defined two different ways?


On 11/30/2016 11:14 AM, Marc Couture wrote:

Yes you can

Declare c1 cursor
Declare c2 cursor

But that means I have to open, read, & close two separate cursors ... which is exactly the situation I was trying to avoid.

david