Generate OData services by mapping DB tables as data sources in SAP GW incl. limits

Let’s assume we’ve never heard of CDS Views and BOPF. How would we create our OData Services? We could implement our services on our own (code based) and there are plenty of blogs and white papers on how to do that. To do so you have to use the GW APIs to implement the standard OData system query options (system query options are prefixed with a “$” character, such as $filter, $orderby, etc). However, SAP Gateway also allows you to create OData services without writing a single line of code. All you do is some modelling in the Gateway Service Builder (Transaction SEGW) and the rest is done by the GW – doesn’t that sound amazing? That’s amazing, but not everything works as expected for some of us.

I can’t find find too many blogs illustrating how to map database tables to GW services (more precisely to EntitySets), and I did not find much about the pitfalls. That’s why I wrote this blog. This is what we want to do:

1. Create DB Tables
2. Insert some test data into the table
3. Create Entity + EntitySet by importing the fields from the DB table (DDIC)
4. Map the EntitySet to a DB table + gernerate + publish
5. Trigger GET_ENTITY and GET_ENTITYSET from GW Client
6. Trigger CREATE_ENTITY / UPDATE_ENTITY / DELETE_ENTITY from GW Client – Oops

I’ve verfied the examples on an NW ABAP 7.5 SP04 as well as on NW ABAP 7.4 SP12.

Create DB Tables

I’ve chose to create a very very simple DB table in SE11. My table will contain some fairly simple customer data:

Insert some test data into the table

To insert data you could write a simple report:

REPORT zz_nabi_create_customer_data.

DATA:
  ls_cust  TYPE znabi_customers.

DO 100 TIMES.
  ls_cust-kunnr      = sy-index.
  ls_cust-kunnr      =  |{ ls_cust-kunnr ALPHA = IN }|. "add leading zeros
  ls_cust-first_name = `First Name ` && sy-index.
  ls_cust-last_name  = `Last Name ` && sy-index.
  ls_cust-creadat    = sy-datum.
  INSERT INTO znabi_customers VALUES ls_cust.
  CLEAR ls_cust.
ENDDO.

Create Entity + EntitySet by importing the fields from the DB table (DDIC)

There are many blogs on to create a project in SEGW, so I’ll just skip this. Once you have a project created continue as follows to create a Customer Entity and the corresponding EntitySet by importing the fields from the DB table created above:

On the property level of your Entity set the checkboxes for Creatable, Updatable, Sortable, Nullable, and Firlterable according o your needs. Also make sure you have a key set.

I have renamed the generated EntitySet from CustomerSet to Customers (be screenshot below). Make sure the checkboxes Creatable, Updatable, Deletabel, Pageable, Addressable, and maybe also Searchable are checked on the EntitySet level for the Customers EntitySet:

Map the EntitySet to a DB table + gernerate + publish

Now we want to map the DB table we created earlier to our Service Implementation of our Customers EntitySet:

From the opened diealog you choose the Type Business Entity and then press the F4 help for Name. After this you can choose your DB table:

Hint: you could also have entered the Name DDIC~ZNABI_CUSTOMERS directly into the Name field.

Next we want to map the fields of the DB table to the properties of our Entity. In our case this is quite simple – all you need to do in pressing the Generate Mapping button (see screenshot):

After saving everything make sure go press the generate button in SEGW. After this continue with Transaction Code /IWFND/MAINT_SERVICE to publish the OData Service. After this you can call /IWFND/GW_CLIENT to test the generated OData Service in the GW Client.

Trigger GET_ENTITY and GET_ENTITYSET from GW Client

Assuming that the service was published successfully we can start using our OData service, i.e. in the GW Client. Let’s trigger GET_ENTITYSET first before we trigger GET_ENTITY:

CUSTOMERS_GET_ENTITYSET: /sap/opu/odata/sap/ZNABI_CUSTOMERS_SRV/Customers?$format=json&$inlinecount=allpages

Note that we applied the OData system query option $inlinecount and it worked like a charm. To prove that other system query options work as well try to call the following:

• /sap/opu/odata/sap/ZNABI_CUSTOMERS_SRV/Customers?$format=json&$inlinecount=allpages&$skip=50&$top=2This skips the first 50 entries and returns the next to, which are 51 and 52:

• /sap/opu/odata/sap/ZNABI_CUSTOMERS_SRV/Customers?$format=json&$inlinecount=allpages&$skip=50&$top=2&$orderby=Kunnr DESCSame as above but with $orderby DESC on the Kunnr property:

Note that we did not implement anything so far. The queries worked like a charm! This is pretty cool, isn’t it? Next let’s try out GET_ENTITY:

CUSTOMERS_GET_ENTITY: /sap/opu/odata/sap/ZNABI_CUSTOMERS_SRV/Customers(‘1’)?$format=json

That worked pretty well, too!

Note that the GW took care of the conversion exit for our property Kunnr. This worked seamlessly because the underlying Domain is KUNNR and there you can find the ALPHA conversion exit. For details about conversion on SAP GW have a look Thomas Nitschke’s great blog about Conversions in SAP Gateway Foundation – Part 1 . For us this means our client applications do not see ugly values for Kunnr, i.e. ‘0000000001’.

Trigger CREATE_ENTITY / UPDATE_ENTITY / DELETE_ENTITY from GW Client – Oops

Not it get’s interesting. One would assume that Create, Update, and Delete works out of the box as well. Well, this is a pitfall, because it actually does not work out of the box. Let’s trigger each one of them via GW Client:

CUSTOMERS_CREATE_ENTITY: POST /sap/opu/odata/sap/ZNABI_CUSTOMERS_SRV/Customers

CUSTOMERS_UPDATE_ENTITY: PUT /sap/opu/odata/sap/ZNABI_CUSTOMERS_SRV/Customers(‘1’)

CUSTOMERS_DELETE_ENTITY: DELETE /sap/opu/odata/sap/ZNABI_CUSTOMERS_SRV/Customers(‘1’)

In all 3 cases we get basically the following error:

CX_SADL_ENTITY_SRVICE_NOT_SUPP: The requested service is not supported by entity ~ZNABI_CUSTOMERS

This is something I have not expected. If you do some debugging you will get to an interesting piece of code:

On NW ABAP 7.5 SP04 it seems crud_supported is false (see line 8+9), that’s why CUD’s don’t work. However, R (=Read) works just fine (hm, so read is not considered being CRUD here 🙂 ).

In the past, i.e. on an NW ABAP 7.4 SP12 you could simply tweak a little by redefining the generated CUSTOMER_CREATE_ENTITY etc. and then before calling super method you would just add the following code:

  METHOD customers_create_entity.

    "works on NW ABAP 7.4 SP12 but not on NW ABAP 7.5 SP04
    "ATTENTION: You should not use this because it is not a public API
    SET PARAMETER ID 'SADL_DDIC_CRUD_MODE' FIELD 'X'.

    CALL METHOD super->customers_create_entity
      EXPORTING
        iv_entity_name          = iv_entity_name
        iv_entity_set_name      = iv_entity_set_name
        iv_source_name          = iv_source_name
        it_key_tab              = it_key_tab
        io_tech_request_context = io_tech_request_context
        it_navigation_path      = it_navigation_path
        io_data_provider        = io_data_provider
      IMPORTING
        er_entity               = er_entity.

  ENDMETHOD.

How did we find out about this? Answer: by debugging the GW processing, which lead us to the following piece of code:

Anyway, this little tweak does not work on newer systems, i.e. on NW ABAP 7.5 SP04, because SAP changed the backing code a little. That also means you will face some issues in case you have used the tweak mentioned above if you upgrade your systems.

Conclusion

So the big question is what can I do to make Creates, Updates, and Deletes (CUDs) work again out of the box? And why did SAP choose to disable them (in non CDS/BOPF scenarios)?

I’d suggest to redefine the generated CUDs and implement your own logic, most probably simple OpenSQL statements. It’s great enough that we don’t have to code the GET_ENTITY and GET_ENTITYSET, especially the latter one can get quite ugly. If you want more than that you might want to switch to CDS + BOPF, but the assumption of this article was we’ve never heard of that 🙂

Hint: I avoided to mention batch processing to keep this blog smaller…

Best, Nabi