SQLRPGLE – Result Set

30 pts.
Tags:
AS400 Data Definitions
Data structures
Programming
RPGLE
SQLRPG
SQLRPGLE
I have an SQLRPGLE program [PGMA] containing the following:

D ResultSet       DS                                 D                                     Qualified      D                                     Occurs( 9999 ) D  AAuUserId                   10 D  AAuValFr                      10    

C/EXEC SQL                                              C+ CALL XXDLIB/PGMB (:##BegNo, :##EndNo, :##UserID)   C/END-EXEC                                             

PGMB has the same DS structure defined as in PGMA and successfully populates the DS ResultSet in PGMB

How do I get SQLRPGLE program PGMB to return the DS ResultSet to PGMA?

Best Regards

Martin



Software/Hardware used:
AS400, iSeries

Answer Wiki

Thanks. We'll let you know when a new response is added.

As Tom write, PGMA need to access PGMB result set values .
This is usually done by PGMB passing the result to PGMA by parameter or store the result in an object (workfile, data queue…) that PGMA can access.

Using parameters you have 2 choices : passing parameter by value or by reference.
In your example, passing by value, you pass the DS ResultSet (containing the values).
By reference you pass the address of the DS ResultSet, then in PGMA you set the DS address to the address received by parameter (you have lots of examples for this in this site)

Depending on what you want to achieve with PGMB, using DTAQ is a great for passing values between programs , because PGMA knows when “end of data” is reached (instead of having to read all DS dataset), can make subsequent requests to PGMB to retrieve more data …

================================================================

From the <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyresultsets.htm”>Returning result sets from stored procedures</a> topic (V5R4):

<ul>
<li>The interfaces that can work with <i>stored procedure</i> <b>result sets</b> include JDBC, CLI, and ODBC.</li>
</ul>
If you want PGMA to be RPG, then you will want to use the <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/cli/rzadpkickoff.htm”>SQL CLI</a> <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/apis/database2.htm”>APIs</a> to get it done. If you have, say, a Visual Basic ODBC function that accesses your stored proc, then you pretty much convert that to RPG. The ODBC APIs translate almost directly to SQL CLI.

Tom

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Just to be sure everything is clear...
    1. You have two SQL RPGLE programs.
    2. Your first program issues a SQL CALL to the second.
    3. You have a DS named ResultSet.
    4. You don't actually want to return a "result set". You simply chose the (somewhat confusing) name "ResultSet" for your DS.
    5. The objective is to have the DS in the first program contain whatever data was placed into a DS of the same name in the second program after the SQL CALL completes.
    6. However, you don't yet have any parameter defined to pass any part of the DS between the two programs
    Do I have it fairly straight? Tom
    125,585 pointsBadges:
    report
  • Riccam
    Hi Tom, you are spot on with my requirement. PGMB is an existing SQLRPGLE pgm which is called from a VB/.net front end application. PGMB returns a result set of mutliple records/rows. I am currently writing a 'green screen' version, and should be able to use the existing stored procedures, but not sure how best to return mutli-rows to another SQLRPGLE or RPGLE pgm It is not possible to use the Qualified DS in the passed parameters list, so how can I get the data back to PGMA without using external files or data queues? There must be a straight forward way of returning data held in a Qualified DS to the calling pgm.
    30 pointsBadges:
    report
  • philpl1jb
    Add the datastructrue to the end of the parameter list of PGMB with the option no pass Your stored procedure should still work fine. In Pgmb move the actual datastructure to the output parameter if the %parm value indicates that the data structure was passed The call in PGMA includes the data structure as the last parameter in the call to PGMB
    49,720 pointsBadges:
    report
  • TomLiotta
    Yes, rather than returning a "result set", you can simply return output or input/output parameters. This means that both PGMA and PGMB need to change their parameter lists to match, but if they're both yours and PGMB isn't already in use elsewhere, that should be easy. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following