How can I use one SQL Cursor between two RPG programs?

Try2BeBetter
Try2BeBetter used Ask the Experts™
on
BACKGROUND:
  -Platform:  iSeries V5r4
  -Language:  RPG IV (using \free syntax)

Here is what i am trying to do:  
      The first RPG program declares and opens up a SQL cursor by means of "exec" statements.  It will then call the second RPG program.  The second program will try to access the SQL cursor that was opened by the first program.

According to IBM's website there is an option you can choose "*ENDSQL" and "*ENDJOB" for the parameter "CLOSQLCSR" that will keep SQL cursors open.

Question:  How can I get the called program to use the SQL cursor that should already exist in memory?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi
what you are tring to do is the same as an OVRDBF share(*Yes). I don't think SQL has this function.


why not simply delacre the cursor in the 2nd program.

Dave

Author

Commented:
If I declare it in the 2nd program, i think it will erase the cursor that is in memory.
Hi Try2,

Try to declare the cursor WITH HOLD.  That is generally unaffected by other cursors, commits, and other events that can void a cursor.


Kent
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

Commented:
@Kdo:  That holds it in the memory, but what would be the RPG code that would retrieve it?  I tried to declare the same variable in the second program and it either created a new cursor or erased the other one.  Because when I went to "fetch" the data, it was blank.
Hi Try2B,

That would be an entirely different cursor.

I'm not an RPG programmer, but I have trouble visualizing how a cursor would be shared between routines.  The cursor is a local object that represents a place in a result set that is local to the same routine.  The item that was fetched from the cursor also resides in this routine's memory.

Sharing any of those objects with another routine won't happen by accident.  The language will have to have made specific accommodations for it.



Kent
VP Technology / Senior Consultant
Commented:
As far as I know, there is no way to "pass" a cursor across an RPG program boundary.  

If you declare cursor "A" in Program1, you can't just use it in Program 2 without declaring it.  Probably won't even compile.

And if you declare cursor "A" in Program2, then you get a new a new cursor that is scoped to program 2.

Sure you can hold cursor open in a program while you go away and do something else in another program for a while, and it will be right where you left it when you come back, but that doesn't make the cursor (or any other program-local structure) is visible to any of the other programs on the call stack.

The CLOSQLCSR option of the various CRTxxxxxx commands allows you to determine when cursors are closed if you don't explicitly close them in your program.  This article explains the CLOSQLCSR parameter pretty well:

http://www-01.ibm.com/support/docview.wss?uid=nas199795108dfed94cd862565c2007cf5c9

- Gary Patterson
I haven't heard of any way to share a cursor across program objects.

One suggested 'rule of thumb' has been "If you're using a cursor, you're probably not doing things the best way." That's not to say that a cursor is always wrong; it's more to say that cursors are often used even when not necessary.

You are asking about two separate "programs" rather than "procedures". I would take a serious look at a design that expected two separate programs to access a single shared cursor. Something is very likely not quite right at some basic level there.

Tom
The only way I can think of doing this is as a service program in a named activation group.
or
all the programs caouls be compiled with activation group *caller

Pgm 1 calls a procedure in the service program to open the cursor
pgm 2 calls a procedure to read the cursor and pass back a data structure

Dave
Gary PattersonVP Technology / Senior Consultant

Commented:
Yeah, dave's suggestion is probably as close as you can come.  

Found the relevant Info Center Reference.  Clearly says you can't access a cursor across program boundaries.  Read all the subtopics:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Frzajq%2Fretcurs.htm

- Gary Patterson
You could break the cursor out to have it generated and read by a third program. Your original two programs would then CALL the third program to have it perform any needed FETCHs.

Tom
Theo KouwenhovenApplication Consultant

Commented:
Just a question,
Why should you like to do that?
what is the purpose of this methode?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial