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

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?
Try2BeBetterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

daveslaterCommented:
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

Try2BeBetterAuthor Commented:
If I declare it in the 2nd program, i think it will erase the cursor that is in memory.
Kent OlsenDBACommented:
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
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Try2BeBetterAuthor 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.
Kent OlsenDBACommented:
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
Gary PattersonVP 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_276102Commented:
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
daveslaterCommented:
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
Member_2_276102Commented:
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 ConsultantCommented:
Just a question,
Why should you like to do that?
what is the purpose of this methode?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Mainframe Languages

From novice to tech pro — start learning today.