Inspired by the Ruby on Rails Active Record, I started to work on an idea to replicate the concept of Active Record in SAPUI5. This post it to present that idea to community and get feedback. So comments are welcome.

I published entire project at Github and pull requests are very welcome as well.

Note: I spend some time thinking if it’s fits better here or on ABAP Development community. Then I decide to write two posts. If you want to see the project from ABAP perspective, check this blog, otherwise keep reading.

Motivation

Imagine that you need to retrieve some records for a given sales order type from VBAK table. Later in the program you need to retrieve a set of materials from MARA table. Then some company code details from T001 table. Then deliveries from LIKP table. If you are using SAP Gateway, you need to create a service or entity type for each one of those requests.

In order to avoid to create a new SAP Gateway service or entity type/set for simple retrieval records from SAP tables, the ABAP Active Record give direct access into the SAP tables in a very simple way.

It’s not the goal of this project to replace all programming at back-end, but avoid create new SAP Gateway objects just for some “incidental” table access.

How It Works

Suppose you need to retrieve all prices for flights of Lufthansa (LH) and connection 2402 from SFLIGHT table.

First, in SAPUI5 code create ABAP Active Record object:

var oARModel = new abapActiveRecord.ARModel();

Then, retrieve a data set (JSON) from ABAP Stack, choosing table, fields and filter

var oFlightsTable = oARModel.aarGetEntitySet('SFLIGHT', ["CARRID", "CONNID", "FLDATE", "PRICE"], {carrid: "LH", connid: "2402"});

Done.

Here is the JSON Model returned by aarGetEntitySet method call above:

{“SFLIGHT”:[ {“CARRID”:”LH”,”CONNID”:”00002402″,”FLDATE”:”08/21/1997″,”PRICE”:”555,00″},

{“CARRID”:”LH”,”CONNID”:”00002402″,”FLDATE”:”08/22/1997″,”PRICE”:”590,00″},

{“CARRID”:”LH”,”CONNID”:”00002402″,”FLDATE”:”08/25/1997″,”PRICE”:”490,00″},

{“CARRID”:”LH”,”CONNID”:”00002402″,”FLDATE”:”08/30/1997″,”PRICE”:”485,00″} ]}

Repeat the same procedure for any other SAP table (SPFLI, MAKT, BKPF, T001W etc.). No auto-generate code is necessary. Just call proper methods with proper parameters.

CRUD Methods

It’s also allowed to execute the CRUD (Create, Read, Update and Delete) methods.

Create

Create a record on SFLIGHT table:

oARModel.aarCreate('SFLIGHT', {carrid: "LH", connid: "2402", fldate: "20160119", price: "500.00"});

Read (SELECT)

Retrieve a set of records:

var oFlightsTable = oARModel.aarGetEntitySet('SFLIGHT', ["CARRID", "CONNID", "FLDATE", "PRICE"], {carrid: "LH", connid: "2402"});

The JSON model will be like bellow:

oFlightsTable.getProperty('/')

"{"SFLIGHT":[{"CARRID":"LH","CONNID":"00002402","FLDATE":"08/21/1997","PRICE":"555,00"}, ... ]}"

Read Simple Value (SELECT SINGLE)

Read a single value from a specific record at table SFLIGHT (select single):

var value = oARModel.aarGetEntity("SFLIGHT", "PRICE", { carrid: sap.ui.getCore().byId("carrid").getValue(), connid: sap.ui.getCore().byId("connid").getValue(),fldate: sap.ui.getCore().byId("fldate").getValue() });

Return the actual value of field PRICE in to variable value , “500.00”.

Update

Update a record on table SFLIGHT:

oARModel.aarUpdate("SFLIGHT", {carrid: "LH", connid: "2402", fldate: "20160119", price: "350.00"});

New value of PRICE: “350.00”.

Delete

Delete a record on table SFLIGHT:

oARModel.aarDelete('SFLIGHT', {carrid: "LH", connid: "2402", fldate: "20160119"});

Under the Hood

In the SAPUI5 side, I extended sap.ui.model.odata.ODataModel class and implemented methods for basic CRUD operations and return data in JSON format.

Those methods basically will parse all parameters in the CRUD methods above and create batch calls to be submitted to SAP Gateway.

The second main responsibility is “translate” the return from SAP Gateway:

<model>SFLIGHT</model>

<field>CARRID</field>

<value>LH</value>

<model>SFLIGHT</model>

<field>CONNID</field>

<value>2402</value>

<model>SFLIGHT</model>

<field>FLDATE</field>

<value>08/21/1997</value>

...

To JSON format:

{"SFLIGHT":[ {"CARRID":"LH","CONNID":"00002402","FLDATE":"08/21/1997","PRICE":"555,00"}, {"CARRID":"LH","CONNID":"00002402","FLDATE":"08/22/1997","PRICE":"590,00"}, {"CARRID":"LH","CONNID":"00002402","FLDATE":"08/25/1997","PRICE":"490,00"}, {"CARRID":"LH","CONNID":"00002402","FLDATE":"08/30/1997","PRICE":"485,00"} ]}

Installation

You can find installation instructions in this guide.

Feedback

I started this project to share my idea and the initial proof of concept. I’m not consider ready to be used in production and improvements are needed. However, in the pure open source spirit, I decided to share my idea and receive feedback from the community.

To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

  1. Christopher Solomon

    Well, you started off using three words I loathe…”Ruby on Rails” 😥 hahaha 😆 …but you redeemed yourself. Great concept and interested to see it in action. Any metrics on performance? Since it is fairly generic, I am curious how it handles some of the “heavier” tables.

    (0) 
    1. Flavio Furlan Post author

      Sorry to mention RoR… I had some experience long time ago, but now it just inspire me 🙂

      I didn’t make any test with large tables yet. Definitively I’ll do in the future.

      (0) 
  2. Daniel Ruiz

    hi Flavio,

    the idea is quite good, but I think you broke REST over there – considering how it was implemented in OData and obviously the OData Framework delivered by SAP cannot handle dynamic URLs.

    wouldn’t it be better to deliver this out of SREST where you could really deliver a dynamic REST API? – it would be extremely more efficient considering the OData Framework slows it down quite a lot compared to SREST while still maintaining REST constraints.

    Cheers,

    Dan.

    (0) 
    1. Trond Stroemme

      Hi Daniel,

      I haven’t studied the solution in detail, but aren’t the actual SQL commands delivered as a parameter to the OData service? If so, they would fall under the REST paradigm, right?

      Regards,

      Trond

      (0) 
      1. Daniel Ruiz

        hi Trond,

        Neither did I, but I sort of know the limitations of the OData Framework delivered by SAP – and as far I understand you wouldn’t be able to have real dynamic endpoints because there is not way to dynamically manipulate the metadata.. I could be wrong thou, but considering the time (it was little I confess) I’ve spent checking the underlying SAP code I couldn’t find a clear way.

        It seems to me we are talking about one Set with one Entity with a few parameters that later will be translated into a Query – whoever, the operation itself we would define (inside the payload) is the operation that should happen inside an Entity or a Resource. (SFLIGHT in the scenario he presented is the resource in question) – IMO this breaks REST principles.

        In the past I’ve achieved something similar using SREST where you can define classes and “extend” resource handlers into different specialized classes to deal with them – or have just a generic one, like the above.

        Main difference is we would still compile an URL which would be GET /icfnode/api/SFLIGHT/1 – and, when I traced speed it was not even on the same league as OData (v2) – it was absolutely faster and more reliable in speed (stable) than OData.

        But then again, no one really cares much to paradigms or conventions when writing software that runs in SAP, as long it works is fine.. – I just wanted to point there is another alternative that provides faster, more reliable and “cleaner” results.

        Cheers,

        Dan.

        (0) 
        1. Trond Stroemme

          Hi Dan,

          I see your point; I’ll look into the SREST option and compare. Would be good to look at the performance differences. Thanks for providing the info!

          I guess the current solution works OK as a generic lightweight service where each call is completely self-contained. Whether it’s truly REST I really cannot say for now…

          As for paradigms and conventions, true that this is something that has largely “eluded” the mainstream SAP community. However, there are those among us who value such concepts, and this is partially why I find these discussions on SCN valuable and interesting.

          Thanks again for sharing your thoughts!

          Regards,

          Trond

          (0) 
          1. Daniel Ruiz

            hi Trond,

            As per https://en.wikipedia.org/wiki/Representational_state_transfer (Applied to Web Services section) you notice that “REST” has something we call a “resource” – which is the “Entity” counterpart in OData.

            One rather “debated” point of REST is that URI should be descriptive of the resource in question.. taking the SFlight example in this blog, in OData (generated) we would end up with and Entity called SFlight and possibly a set called SFlightSet – and when someone wants to address those resources they will address in the URI, not in the payload.

            It may be seen as a “technicality” perhaps but the naming of the resource is a rather important aspect – at least most people would find it – Again, we would be dealing with different entities on the same URI on the above example, which is not “correct” as per say.. – but then again, since this is a “convention” there is a lot of talk of “whats right whats wrong”, really hard for everyone to agree on something.

            Cheers,
            Dan.

            (0) 
    2. Flavio Furlan Post author

      Hi Daniel,

      Humm… now you make me think about that. I confess I never heard about SREST.

      Thanks for you comment and I’ll investigate further and try other possibilities.

      Furlan

      (0) 
      1. Trond Stroemme

        SREST is just the package that OData is built on, in Gateway. Using the Gateway builder to generate the service implementations means your implementing classes will use the interfaces from package SREST.

        This is SAP’s recommended way of setting up services, by the way.

        I assume Dan refers to using the classes contained in SREST to set up the service “manually”. Not sure what the benefits would be, but maybe Dan could elaborate?

        Regards,

        Trond

        (0) 
        1. Daniel Ruiz

          Hi Trond,

          Well, “recommended way” to which kind of scenarios? – I can see a couple of issues here being a) why SAP thinks they can actually say what’s best when they have no clue what one wants to achieve and b) why they actually built their product on top of “SREST” but others should not really use it.

          IMO, SREST seems to be sort of a bastardized copy of the JSR311 with a lot of limitations (after all it’s just ABAP and not Java) where the methods and the flow of operations have been studied and discussed in the JSR process itself – and it works for Java so it’s obvious it will work in SAP related scenarios.

          OData.. cool, SAP pushed it.. but who else really uses OData other than Microsoft and SAP? – Go thru all major API’s out there, stuff that runs Facebook, Twitter, all huge services, absolutely no one even thinks of OData because it has limitations.

          Other than that, the OData layer is slow because it does far too much validations, logic and a lot of things you may not need – all of this slows down, and when you think about ABAP (which is rather slow by nature) and slowing even more is not a good thing..

          So why use OData when you clearly don’t need the metadata? – Because SAP said so is not really valid unless one cannot think by himself and above all, measure different approaches and solutions for different problems. No offense here to people who wants to follow SAP words, I just find it terri-bad.

          What happens when you want to “extend” an OData Service – meaning, I want to re-implement same service, same classes, same structure and re-define one or two operations to do something slightly different for another use case.. you can’t, you need to “re-work” another service from ground up.. unless again, I’m missing something..

          Shall we really build the foundations for a “Services Approach” with a non-extensible framework or classes?

          About performance, this is rather old but as a basis Performance of SAP Gateway you can see how bad the “odata” layer is – I did the same test (years ago) comparing SREST to OData and SREST was always faster.. perhaps I need to re-test latest versions just to be sure, but OData have been since it’s introduction into the ABAP stack a little “slug”.

          Cheers,
          Dan.

          PS: I appreciate this kind of conversation and if any of the above sounds rude it’s because my writing is rather poor! 🙂

          (0) 
      2. Daniel Ruiz

        hi Flavio,

        We’re both from Sao Paulo, feel free to contact me if you need an example how to use it.. SAP Documentation is not too bad for SREST, pretty sure you won’t have issues trying it out.

        Cheers,
        Dan.

        (0) 
  3. Trond Stroemme

    Hi Flavio,

    great concept – I’ll implement it and see how it works. I had some similar needs and started building a generic data replication mechanism for copying tables across from Gateway to HANA Cloud Platform, by retrieving metadata and using it to dynamically create local HCP tables. Slightly different use case. Anyway, your project looks very promising; I’ll try it out!

    Regards,

    Trond

    (0) 
    1. Flavio Furlan Post author

      Hi Trond,

      Thanks for the comment! I know the project is in very early stage, but my goal is to present and get feedback about the concept.

      The actual feedback convince me to keep working on 🙂

      Regards,

      Furlan

      (0) 
  4. Marco Dahms

    Hi Flavio,

    interesting concept, thanks for sharing your approach with us here and on Github.

    I took a quick look at the ABAP code and the installation guide.


    Your solution allows the UI5 developer to deal with any ERP table in a dynamic and

    generic way.

    There were some things concerning security and authorizations coming to my mind:

    – There is a generic OData service available as HTTP endpoint within the network zone where the ERP resides in. Anybody who owns ERP credentials is capable of calling this service to obtain arbitrary data. By what means can we avoid this?

    – There is no authorization check with regards to any business domain involved so far (correct my if I overlooked it). Thus, a manager who owns cost center A can obtain all cost center line items from cost center B he is not responsible for. This contradicts with the common authorization requirements within SAP. What is your idea here?

    Regardless of my points, I appreciate such generic approaches and as a developer I prefer the freedom they provide. On the other hand, we require means to avoid that unauthorized data access is possible.

    Best,

    Marco

    (0) 
    1. Flavio Furlan Post author

                                  Marco Dahms wrote:

      – There is a generic OData service available as HTTP endpoint within the network zone where the ERP resides in. Anybody who owns ERP credentials is capable of calling this service to obtain arbitrary data. By what means can we avoid this?

      Hi Marco,

      According to my understand, only users with proper credentials could access ERP data, so it’s not my objective expose ERP data to public users. When I started the project, I was thinking in SAPUI5 projects that already interact with ERP services.

      However, I could answer that in the future, as I’m already thinking in another project where I will expose Netweaver service to public users. 🙂

                                  Marco Dahms wrote:

      – There is no authorization check with regards to any business domain involved so far (correct my if I overlooked it). Thus, a manager who owns cost center A can obtain all cost center line items from cost center B he is not responsible for. This contradicts with the common authorization requirements within SAP. What is your idea here?

      Very good point that escape from my attention! I just included that in to do list for future releases!

      Thanks for your comments!

      Furlan

      (0) 
  5. Bruno Lucattelli

    Hey Furlan! I am impressed with the possibilities of this. I definitely can see a lot of people using it in real world scenarios. Congratulations on this initiative and thank you for sharing with us!

    (0) 

Leave a Reply