ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

how to call peocedure in rpgle and get result sets?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • how to call peocedure in rpgle and get result sets?

    the store procedure will return a result sets
    how can I call the procedure and get the result sets?


    thanks!

  • #2
    Re: how to call peocedure in rpgle and get result sets?

    Heres a simple example of returning a string



    jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #3
      Re: how to call peocedure in rpgle and get result sets?

      Hi,

      if you want to receive result sets from stored procedures within RPG, you have to use CLI-APIs.

      An other way would be, to use create a User Defined Table Function (UDTF) and use it in RPG. The result returned by a UDTF is similar to a result set returned from a stored procedure, but in RPG you can use a UDTF in a SELECT-Statement.

      Example:
      PHP Code:
      CREATE FUNCTION MySchema/GetAddress (ParAddrNo Integer)
              
      RETURNS TABLE
                     
      (FirstName CHARACTER15 ) ,
                      
      Name      CHARACTER15 ) ,
                      
      ZipCode   DECIMAL (50),
                      
      City      CHARACTER15 ))
              
      LANGUAGE SQL
              NOT DETERMINISTIC
              CONTAINS SQL DATA
              CALLED ON NULL INPUT
              NO EXTERNAL ACTION
              DISALLOW PARALLEL
              
      RETURN Select from MyFile
                     Where MyAddrNo 
      ParAddrNo;

      Select  FirstNameUpper(LastName)
         
      from Table(GetAddress(MyAddrNo)) as MyUDTF
         Where 
      ....
         
      Order by Upper(LastName), FirstName
      Birgitta

      Comment


      • #4
        Re: how to call peocedure in rpgle and get result sets?

        >> if you want to receive result sets from stored procedures within RPG, you have to use CLI-APIs.

        Or use embedded SQL if that is an option.

        It is also perfectly acceptable to return a DS array - but of course you also need to notify the receiving program of the number of elements returned and he must loop through the array.

        Comment

        Working...
        X