ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

UDF-SQL-External

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

  • UDF-SQL-External

    I have a business analyst who wants me to write a function he can use to read a record from one of our files. He'll be working in Access, if that matters. . I got some help from a co-worker who has a procedure that is called externally. He told me that every other group here who needs to connect to our iSeries uses this method. My analyst says he needs a function.

    OK - that's fine. But my in-house guru hasn't done that! I've searched on the internet and found a great powerpoint from Mr. Scott Klement and I've waded through parts of the IBM redbook and support site. I have the function itself figured out (I think), but I'm having a problem designing the RPG properly. We have a mish-mash of old styles and I can't use anything too new or it will mess up the other developer and cause maintenance headaches.

    That's a really long introduction - I basically want to know if I'm understanding how this works -- The business analyst writes a(n) SQL statement that has my function name -- something like SELECT field1, field2 FUNCTION(field3) from FILE and then my FUNCTION calls a program (sub procedure? I'm still really struggling with that.) that holds the logic to figure out what field3 is.

    If that's right, it sounds really simple. I do the Create Function

    Create Function FUNCTION (input field, input field, input field)
    RETURNS character
    LANGUAGE RPGLE
    NOT DETERMINISTIC
    READS SQL DATA
    EXTERNAL NAME PROGRAM(FUNCTION) --- or just PROGRAM ????
    PARAMETER STYLE GENERAL;

    I apologize that this isn't very well thought out. I guess what I need are a few more examples. And wouldn't my program be pretty much the same as the procedure that my coworker helped me with?

    Any push in the right direction, as always, will be greatly appreciated.
    Happy Friday!

  • #2
    Oh, wait! I think I found an example that got my brain in gear. A wonderful article by Jim Staton from Nov. 2012

    Reuse Your RPG Code with SQL User-Defined Functions, What? You haven't used a UDF before? No worries. There's nothing to it.

    If anyone knows of an (inexpensive?) class that would help me with this type of thing, I would appreciate recommendations. My attempts to attend the COMMON fall conference (which has been within driving distance) have been shot down by management for 2 years. They won't look beyond the fact that it is called a conference, even though the other ISeries developers (my guru and his boss) both endorsed it.
    (sorry; off soapbox now)

    Thanks again!

    Comment


    • #3
      What is your actual question, here? Whether you can call a program vs. a subprocedure? You can do either.

      To call a program use these keywords:
      Code:
         PARAMETER STYLE SQL
         PROGRAM TYPE MAIN
         EXTERNAL NAME 'LIBRARY/PROGRAM'
      To call a subprocedure, use these:
      Code:
        (parameter style can be any of the possible optionns that SQL offers)
        PROGRAM TYPE SUB
        EXTERNAL NAME 'LIBRARY/SRVPGM(SUBPROCEDURE)'

      Comment


      • #4
        Thank you, Scott. I guess I had many questions! I'm sorry I didn't formulate them better.

        Comment

        Working...
        X