sponsored links



No announcement yet.

Reading/Relaying SQL Result sets in SQLRPGLE?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Reading/Relaying SQL Result sets in SQLRPGLE?

    I have SQLRPGLE program A that returns an SQL Result Set.
    I would like program B to be able to call it, and read the SQL result set, ideally in a cursor
    Even better, I would like program B to be able to return the SQL result set to whatever called it. And each call is over a remote SQL connection

    Program J (on Java system) connects to IBMi A and calls Program A
    Program A connects to IBMi B and calls Program B
    Program B returns an SQL Result Set
    Program A receives the SQL Result Set and returns it
    Program J receives the SQL Result Set
    And somehow, when J is done. the open cursors all get closed

    Is any of this possible?

  • #2
    Result Sets can be handled in an RPG-Program with embedded SQL
    You need to define a RESULT_SET_LOCATOR (one for each result set). Then you call your stored procedure.
    After you associate the RESULT_SET_LOCATOR with the Result Set with the ASSOCIATE SQL Command.
    In the next step you have to define a cursor for the result set with the ALLOCATE SQL command.
    Now you can fetch through the result set and do whatever you want.
    At the end you need to close your cursor.

    Not sure why you want to read a result set and then return it to the caller. Why the caller will not call the program/stored procedure returning the result set directly?

    If you want to return the result set again, you may lead the result set into an array data structure and then return the data structure/result set with SET RESULT SET. You could also store the result set in a (temporary) table and the return the result set from the temporary table through an open cursor. Or Read the temporary table directly in your JAVA program.


    • #3
      I figured that would be the case, but thanks for confirming. Will probably use the array method for smaller data sets, and the temp file method for larger ones.
      As for the "relay" approach, it's because the Java system is external, so a direct incoming connection to the main processing LPAR is not allowed. It has to go through the intermediary super high security LPAR.