A recent question prompted this content. A customer had an existing SAP HANA only project they had created in the SAP Business Application Studio. They now wanted to add OData service functionality to this project. In SAP Web IDE you would run a wizard to add a Node.js module which could support XSJS and XSODATA. However those technologies are no longer recommended and/or supported in latest SAP HANA Cloud and SAP Business Application Studio approach. Instead SAP recommends using the SAP Cloud Application Programming Model. But this is a different project type and structure from the SAP HANA project and Dev Space they were already using.
In this Tech Byte content, I want to demonstrate a low effort approach to convert an existing SAP HANA project to the CAP project type. And instead of re-creating all the DB artifacts in CAP natively we will show how to create views and proxy entities to run on top of the existing database artifacts. Finally as a little bonus content at the end we will show how to implement exit handlers (often needed when coming from XSODATA), OData V2 support (also sometimes needed for backwards compatibility with XSODATA) and adding both REST and openAPI/Swagger UI support to your CAP project.
Converting to a CAP Project
All the source code for this content can be found here:
I wanted a simple HANA project to simulate the customer’s experience. I used the Business Application Studio and the HANA Dev Space and Project Wizard to create this starting project. In this project we have
- Tables all modeled using hdbtable such as sap-tech-bytes/Header.hdbtable at 2021-05-03-hana-existing-starting · SAP-samples/sap-tech-bytes (github.com)
- SQL Views on those tables using hdbview which are important because these views are used to simplify the naming of columns making them compatible with CAP. For example: sap-tech-bytes/Header.hdbview at 2021-05-03-hana-existing-starting · SAP-samples/sap-tech-bytes (github.com)
- CSV files and configuration to load sample data into the project: sap-tech-bytes/db/src/data/loads at 2021-05-03-hana-existing-starting · SAP-samples/sap-tech-bytes (github.com)
- A Calculation View (which we will want to expose via OData as well): sap-tech-bytes/PO_BASIC.hdbcalculationview at 2021-05-03-hana-existing-starting · SAP-samples/sap-tech-bytes (github.com)
- A Stored Procedure (which we also want to expose via OData): sap-tech-bytes/getPOItems.hdbprocedure at 2021-05-03-hana-existing-starting · SAP-samples/sap-tech-bytes (github.com)
The whole of this starting HANA only project: SAP-samples/sap-tech-bytes at 2021-05-03-hana-existing-starting (github.com)
The final CAP adjusted project with all the new content can be found here: https://github.com/SAP-samples/sap-tech-bytes/tree/2021-05-03-hana-existing-cap
We also created a video that will walk through all the steps to convert the project and expose the existing DB artifacts via CAP.
To summarize the video, here are the key points.
Rather than start a new project, I use cds init and cds add to adjust the HANA project and add the necessary CAP configuration files. This does overwrite the mta.yaml file and requires some manual adjustments after using the cds commands. But otherwise the adjustment works quite well and does most of the work for you.
5:29 Convert Project type to CAP
The next major point of adjustment to the project is that we have existing HANA native database artifacts. We don’t want to model these again in CAP. Therefore we want to expose these objects to CAP as they are. Here we can use the special annotation, @cds.persistence.exists.
You can read more about this approach in the CAP documentation:
But essentially we need to create a kind of proxy definition of the target table or view in CAP CDS syntax. This special annotation tells CAP not to try and recreate the object in the database, but we still need the CAP CDS specific metadata redefined. In this example I use the hana-cli sample tool and the inspectTable or inspectView command which reads the existing database table/view from the catalog and converts that to CAP CDS for you.
10:14 Create CAP Proxy Entities
Once the existing tables and views (including the Calculation View) service entities can be modeled upon them like any native CAP entity definition. We get full Create, Update, Delete, and Read operations on these existing database objects.
16:46 Add CAP service
As bonus content in this video and code sample we look at some other things that might be useful when converting HANA Projects and/or having to replace existing XSODATA or XSJS content.
First we look at how to use the @sap/cds-odata-v2-adapter-proxy module to add OData V2 support to your service. This can be particularly useful when needing to emulate existing XSODATA services without having to update the consuming frontend to support OData V4 (which is the default now in CAP)
23:00 Bonus OData V2
Depending upon how you want to consume this service you might not want CAP at all. For example if you aren’t consuming the service in SAPUI5 or Fiori, you might find that plain REST might provide better compatibility. CAP now supports service endpoints with plain REST as well and we see how to do that in this video:
23:28 Bonus Pure REST endpoint
When converting from XSODATA you might have some exit handlers written in SQLScript or XSJS. We can create similar handler exits in CAP for your services.
24:04 Bonus Custom Exit Handlers
And to call the Stored Procedure as an OData function in CAP we will also need one of these Exit Handlers.
28:25 Bonus Swagger UI
Alternative Using Node.js Directly Without CAP
In the second part of this series we continue to look at the idea of adding a service layer to an existing SAP HANA project in the era of SAP HANA Cloud and Business Application Studio. In Part 1 we saw how you can convert an SAP HANA Project to an SAP Cloud Application Programming Model project. In this video we want to explore an alternative approach by manually adding just a Node.js module to our SAP HANA project.
I want to stress that SAP Cloud Application Programming Model is the recommended and preferred choice in standard CRUD service options. However this approach with a standalone Node.js service can be useful when you need more control, a custom response body or a non REST or OData service.
The final adjusted project with all the hand-built, Node.js module can be found here: https://github.com/SAP-samples/sap-tech-bytes/tree/2021-05-03-hana-existing-nodejs
And like in part 1 we have a video walk through of the whole process:
As many developers are faced with the opportunity to move their projects to the SAP Cloud Application Programming Model but probably also have lots of existing HANA native projects and content; I hope this Tech Bytes sample and other content helps with easing that migration effort.