Stored Procedures called from RPG programs

0 pts.
Tags:
AS/400
CLP
RPG
RPGLE
Web development
I created a stored procedure that returns a result set. I am using it in a PHP program and all works great. Pass in a account Id and returns all hits for valid records for that account. We have a excel report and subfile screen that runs off a green screen that needs to retreive this same data. My hopes were to create an SQLRPGLE program that runs the stored procedure and gets a result set back. This way I do not have to duplicate the code and if "valid records for that account." change I just have to change one program and all screens/reports are changed. Anyone have any ideas for me. I've searched the web over and came to a dead end. Lots of info on calling stored procedures from php, java, etc, but not from anouther RPG.
ASKED: January 17, 2007  1:41 PM
UPDATED: December 23, 2010  12:35 AM

Answer Wiki

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

Here’s the work-around I’ve used:

Rather than having the result set returned directly, in the stored procedure, write your results into a temp file. Return a SELECT * on the temp file as your result set. In your RPG program, read the temp file instead of the result set.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 5  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
  • Ashnirody
    You can also call a stored procedure directly from your SQLRPGLE program. If you are expecting one row back use the following: C/EXEC SQL C+ Call procedure(:Parm1, Parm2,......Parm5) C/END-EXEC If you have multiple rows returned you have to declare a SQLDA. C/EXEC SQL C+ INCLUDE SQLDA c/END-SQL Then call the procedure C/EXEC SQL C+ CALL procedure C+ using DESCRIPTOR :PARMS C.END-EXEC
    100 pointsBadges:
    report
  • TomLiotta
    From the i 6.1 Information Center, Returning result sets from stored procedures topic:
    • The interfaces that can work with stored procedure result sets include JDBC, CLI, and ODBC.
    This has been true from the beginning and is still true. Parms can be returned from a stored proc to RPG, potentially as an array, but not directly as a result set to be FETCHed from. RPG can call SQL CLI (ODBC) APIs or Java functions (for JDBC) to process result sets though. Tom
    125,585 pointsBadges:
    report
  • nu4040
    You can call a stored procedure from your SQLRPGLE program. Call procedure(:Parm1, Parm2,……Parm10)
    0 pointsBadges:
    report
  • TomLiotta
    The question doesn't need to know how to call a stored proc from RPG. The question wants to know about RPG that runs the stored procedure and gets a result set back. Calling a stored proc is easy. But getting a result set back requires a lot more than a list of parms. How would you put 10000 rows into a list of parms? What if the result set had 20000 rows on the next call? Tom
    125,585 pointsBadges:
    report
  • PSMurray
    nu4040 - We have created many stored procedures that create subsets of data into files in QTEMP. This allows us to manipulate a subset of data rather than the whole file(s). A simple example is our A/R inquire. We create a subset of the Customers invoices and payments into a QTEMP file. From there we use RPG/SQL statements to provide multiple views of this subset without having to manipulate the whole Invoice file and Payment file. BIG CPU time saver. NO logical files over the Invoice/Payment files. These files are huge - 1 million + in size. Makes the programmers life simpler. Phil
    530 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