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:
- Create DB Tables
- Insert some test data into the table
- Create Entity + EntitySet by importing the fields from the DB table (DDIC)
- Map the EntitySet to a DB table + gernerate + publish
- Trigger GET_ENTITY and GET_ENTITYSET from GW Client
- 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
Vivid blog indeed...Can reduce a lot of effort in GET_ENTITYSET coding...
Hi Nabi,
the whole setup would work if you would create a CDS view on top of your database table which uses your table as active persistence using the annotation
writeActivePersistence: ‘ZNABI_CUSTOMERS’
If possible you should use CDS views since they are the basis of the new programming model used in S/4 HANA.
So the DDL code of your CDS view should look like shown below.
This will generate an BOPF object that provides the CUD support.
All this will work if your backend is running on top of 7.50 or higher.
Using the annotation @OData.publish: true you can automatically register the CDS view as an OData Service in your backend our you use Service Builder and the Referenced Data Source approach.
Best Regards,
Andre
Thanks Andre,
I still wonder why CUDs are disabled. If they worked things would be even more easy…
Best, Nabi
Hi Andre,
Sorry for replying to an old thread. But just wanted to clear one thing. If we do a CDS + BOPF then using is for map data source in SEGW ( as I would need calculated fields added to the data with calculation logic ). in such case for updates will it work if I just provide map data source or should I do anything more.
When I tested the above with $batch option it gives an error.
Thanks
Nafran
Hi Nabi,
Thanks for the good information.
I tried to replicate the same . During this step i end up with dump.
Do you have any idea about this error ?
Appreciate your support. Thank you !
Regards
Prabha
Nope - I never had that issue. Sorry...
thx
for the Create Update Delete method you can consider an additional mapping to a RFC (bapi). Of course you will have to implement this function module