Topic
10 replies Latest Post - ‏2013-03-03T01:05:07Z by JonParis
DanielNelson
DanielNelson
6 Posts
ACCEPTED ANSWER

Pinned topic Question about OAR

‏2013-02-28T13:22:50Z |
I've seen and tested examples of how to develop a handler per table, but I’m wondering if it possible to have a more generic handler. For example the handler could have access to 50+ SQL members that could be executed to return data to the calling program. The calling program could also pass parameters to the handler to be used in the SQL statements. The calling program would pass the name of the SQL member to execute. I wouldn’t want the handler to have to keep a data structure for every possible result.
I would hope it’s possible for the handler to pass back a single row or a data set.

What is the best way to do this? Is it even possible?

One way I was thinking of doing this would be to pass a pointer between the Calling program and handler. In the memory space would be the record(s) retrieved from the handler.

The other would be to generate XML in the handler and pass that back to the calling program. Then the XML could be parsed with the “INTO” statement.

Thanks,
Dan
Updated on 2013-03-03T01:05:07Z at 2013-03-03T01:05:07Z by JonParis
  • JonParis
    JonParis
    113 Posts
    ACCEPTED ANSWER

    Re: Question about OAR

    ‏2013-02-28T15:31:39Z  in response to DanielNelson
    Dan,

    I have written and published a number of generic handlers. Here's a list of my current articles - the ones marked ** are generic handlers.
    • Browser implementation of a print report: www.ibmsystemsmag.com/ibmi/OAR_handlers/33511p1.aspx

    General introduction to OA: http://www.ibmsystemsmag.com/ibmi/developer/general/oa_revisited/

    Basics of OA: http://www.itjungle.com/fhg/fhg101310-story01.html

    Buffer handler for Currency Exchange web service: http://www.ibmsystemsmag.com/ibmi/developer/general/oa_rpg/

    • Creating an OA CSV Writer http://www.ibmsystemsmag.com/ibmi/developer/rpg/Getting-a-Handle-on-RPG’s-Open-Access/

    • Handling Input Handlers With RPG Open Access - Input CSV handler: http://www.ibmsystemsmag.com/ibmi/developer/rpg/input_handlers/
    I hope these help but I can't help but wonder if somewhere in your studies you missed some of the point of OA.

    For one - with the second parameter on the HANDLER you already have an engineered method for passing additional parameter information. For another I would strongly suggest that you don't start looking to pointers etc. to return information. If you are going to that extreme to return data from a handler then, in my option, you are really missing the point completely.

    Think about it. The primary purpose if an OA handler is to make the access of a non-standard device look exactly the same as a database, printer, or whatever you have modelled it on. Data should be being retrieved via a conventional READ or EXFMT (for example). You need to engineer your handler to fit the existing I/O model.

    Let me give you an example that may get you thinking.

    Suppose there is a web service that I can call that returns a currency exchange rate. I treat that as an indexed disk file with the two currencies as keys and in response to a CHAiN it returns the exchange rate(s). In fact this is exactly what my currency exchange example does.

    Now suppose that I find a currency service that given just one currency code (say USD) will return an XML doc containing all of the exchange rates for that currency. My architecture for that handler would probably work like this. If the user issues a CHAIN then use the first part of the key to retrieve the information. Then process the XML to extract the values for the second key. If the user issues a SETLL then do the same thing responding with %Found/%Equal as appropriate then sort any returned data into key sequence (if not already returned that way by the web service) keep track of where you are in the data in the state info. Respond to any subsequent READE etc. with data or %EOF as normal. The "result set" is being retrieved via conventional I/O - and that is what OA is all about.

    Do you see the difference? The "twiddly bits" are being done under the covers. The last thing on earth I want the user of a handler to have to do is play with pointers and manipulate their own way through the result sets. If you're going to force them to do that you might just as well set up callable APIs and forget about OA all together. The more handlers I write, the more I realize that the coding of handlers is not the hard part - the software engineering, deciding what and how they should do it, is the hard part. I discuss this to some extent in all of my articles, but the Browser one above is the one that made the most use of design conventions to avoid the user having to do anything different.
    Hope this helps.
    • JonParis
      JonParis
      113 Posts
      ACCEPTED ANSWER

      Re: Question about OAR

      ‏2013-02-28T15:33:25Z  in response to JonParis
      Apparently this idiotic forum software regards two consecutive asterisks as some kind of request for indented lists. Please ignore the resulting mess on my previous response - I would fix it myself but another deficiency of this software is that it won't let you edit your posts. Grrrrrrr.
      • DanielNelson
        DanielNelson
        6 Posts
        ACCEPTED ANSWER

        Re: Question about OAR

        ‏2013-02-28T16:40:52Z  in response to JonParis
        First, thank you for the response. I understand what you are saying.
        One more question if I may.

        From what I've read there are two methods for the data (Data Structure & Name Value pairs). When you use name value pairs in the handler can that be passed back to the calling program or does the handler ALWAYS have to pass back a record format?

        If you can pass back name value pairs, do you have an example that does that?

        I've seen & used the example that used name value pairs to write to the IFS as a CSV file. But what I'm looking for is something like reading a CSV file from the IFS and getting the fields and returing that to the calling program. The handler would parse out the fields until End Of Record. It would then send back name value pairs. *In this example field names would come from first record.

        Hope this makes sense.

        Thanks Again,
        Dan
        • JonParis
          JonParis
          113 Posts
          ACCEPTED ANSWER

          Re: Question about OAR

          ‏2013-02-28T17:15:51Z  in response to DanielNelson
          OK - first a quick (hopefully not too harsh) comment.

          Forget about returning name value pairs. Think that way and you're missing the point. The "user" programmer should do no more work than with a conventional disk file. Think returning records.

          Actually I have just completed work on a CSV reader that does what you want. It wasn't on my previous list because I didn't realize it had been published yet. This is the URL http://www.ibmsystemsmag.com/ibmi/developer/rpg/flexible_input_handlers

          Basically it works by matching the column heading names with the field names from the name/value pairs. The next iteration of this one (if I build it) would have allowed the user to specify an array to allow the code to match column headings other than the field name. For instance "Purchase Date" as a column heading might match PODATE as an RPG field name.

          Again the detail is in the design/engineering aspects. Study those parts of the article more than the code. Make believe you are a Rochester software engineer trying to design a new operating system feature to allow RPG programs to read CSVs. That is what you're trying to build.

          P.S. The code link in the article is not working yet ... doh! I will have it done in a couple fo hours so you can download it and play with it.

          Now repeat after me - "I am designing a record retrieval system. I must not force the user programmer to do any more work that (s)he would with a disk file." - and repeat that mantra every time you come to design a handler. <grin>
          • DanielNelson
            DanielNelson
            6 Posts
            ACCEPTED ANSWER

            Re: Question about OAR

            ‏2013-02-28T18:03:22Z  in response to JonParis
            LOL

            I understand "Do not use a tool in a way it wasn't desinged for!"
            FYI: Thanks for the example and please keep up with the good work you do!!!
            And for putting up with people like me :)
          • barbara_morris
            barbara_morris
            383 Posts
            ACCEPTED ANSWER

            Re: Question about OAR

            ‏2013-02-28T18:14:08Z  in response to JonParis
            Hi Dan, I just want to echo what Jon's saying, that you have to think of the transaction with the RPG program as a record's worth of information at a time.

            It sounds like you're familiar with a handler that would handle a specific file. If an RPG program is already written to work with a file-specific handler, and it's going to change to use a generic handler, then the only change for the RPG program should be to change the name of the handler. And maybe to add a second parameter to the HANDLER keyword for the handler-provider's private use, not for the RPG programmer to do anything with.

            Usually, generic handlers are much more complex than handlers that handle a single file. None of that complexity should be passed on to the RPG programmer that is using the handler. All the complexity should remain in the handler.

            Remember that the purpose behind OA is to allow an RPG programmer to use RPG's I/O model to access the data for any type of device or resource.

            Other than the handler keyword, the rest of the RPG program should look as though it is working with a system database, printer or display file.

            (With some minor exceptions, such as having to set the path for an IFS file before the OPEN.)

            RPG's I/O model sends and retrieves data one record at a time. It sounds like you want to send the data to the RPG programmer one field at a time, or in the XML case, in a form that would require the RPG programmer to do extra work (XML-INTO). But that would require the RPG programmer to completely change how the program does I/O for that file.

            The name-value pairs are just intended as another way for the handler-writer to build the record. Sometimes it's easier to provide and consume the data in "human readable form", like "-123.45" rather than DS-subfield form, x'12345d'. But the handler can't just pass back data for any name; it can only pass back data for the names in the name-value array, matching the names of the fields in the file. And it has to set the data for all the fields at once, to build up a whole record's worth of data.
            • DanielNelson
              DanielNelson
              6 Posts
              ACCEPTED ANSWER

              Re: Question about OAR

              ‏2013-03-01T13:30:23Z  in response to barbara_morris
              Barbara,

              My goal wasn't to add more work on the developer. First goal was to return multiple records at a time. For example I need 15 records for my subfile. Send them with one read.

              The second goal was to have a very generic handler for reading data. It would look at the request (table Name) and execute the correct SQL (stored in source members). If the handler didn’t have to have a Data Structure defined inside itself for each table (the calling program would have the DS) then it would be a simple matter of adding an SQL source member for a new table with zero changes to the handler. The handler would have to be smart enough to build the memory space that is returned to the calling program to match the data structure that is in the calling program.

              Anyway thanks again for your help.

              Dan
              • JonParis
                JonParis
                113 Posts
                ACCEPTED ANSWER

                Re: Question about OAR

                ‏2013-03-01T14:23:00Z  in response to DanielNelson
                Dan,

                If you want a subfile maybe you should model the handler to use a subfile. But I still think that making a result set available to an RPGer by allowing them to position (CHAIN/SETLL) and then READE will be more familiar.

                As to the SQL one. Look at what I did with the CSV and other examples. The only DS the caller should have would be a LIkeRec type. You seem to have missed the point that you can change the handler by simply causing it to be associated with a different file and letting RPG feed you the field names. Look at how I handled column names in the second CSV example. They can be built dynamically in the handler and then simply matched against the results.

                Actually the hard part of what you seem to be trying to do will be to build the SQL in the absence of advance knowledge of the host variable definitions. You may have to resort to CLI for that. But it does raise the question - what are you hoping the handler can achieve for you that wouldn't be met more simply by embedding SQL in the calling program?
                • DanielNelson
                  DanielNelson
                  6 Posts
                  ACCEPTED ANSWER

                  Re: Question about OAR

                  ‏2013-03-01T15:41:44Z  in response to JonParis
                  As you know I've not been able to get the last example you referenced. So I need to get it and digest it. Then based on all of the information provided and other research I’ve done. I'm going pause and review all that I know now and see what makes sense.

                  I have a weird way of going about things. What are the extremes I would like to do and then what can be done and last what should be done. May not be the quickest route but over the years I’ve learned a lot.

                  Now if I didn't have CRS I would be even better at what I do :)

                  Thanks again for your help!

                  Dan

                  CRS = Can’t remember shit
                  • JonParis
                    JonParis
                    113 Posts
                    ACCEPTED ANSWER

                    Re: Question about OAR

                    ‏2013-03-03T01:05:07Z  in response to DanielNelson
                    It's ALIVE! - the web page that is. You can find the example I referenced here: http://partner400.com/Examples/RPGOAPart4.html

                    It references back t the previous version and also to the magazine articles that describe them in more detail. If you find any errors or come up with enhancements please let me know.