Hello again SAP community. I hope you are doing well and that you have had a chance to read my first blog regarding CAP here. Today, I would like to add on that blog regarding building full stack applications in the CAP framework and add a few more Database Artifacts such as calculation views and stored procedures to make it more of a real application. To read this blog in Spanish, go here
Let’s begin to add a calculation view. As you know, calculation views represent information combining data from multiple tables/views to present a consolidated data model. Keep in mind, when creating and building new objects, we must do a build (cds build) in the project before deploying to make sure we do not create issues in the HDI.
To add a calculation view to your CAP project do the following steps:
- Create your calculation view under the db/src folder of your CAP project.
From the command palette, type >hana: to open the suggestion drop down and select “Create SAP HANA Database Artifact”
- Follow the wizard to add the corresponding db object type, name, folder path, etc
Once your data model is created, we need to rebuild the project before deploying. To rebuild the project, from the terminal, please type: cds build
*Notice that the tables and the new view are being built (output in the terminal window)
Once all the objects are built, you may use the rocket icon from the BAS to deploy – hover your mouse at the project level not at the object level.
Once deployed, you may verify if the new calc view has been successfully deployed to the HANA instance using your database explorer (from the Column views section)
And you can query the view from the SQL console
or also from the terminal using the hana-cli
The next step is to add this calculation view to our DB cds model file and also as a reference to the OData service. Since the CV already exists in the system, we must include some annotations to the DB cds file so the system is aware of an existing object.
Using the hana-cli tool, let’s inspect the view. Using the output as cds (-o flag) would generate this view as a CDS view. As I mentioned before, since this is an existing view, the hana-cli also includes the annotations that this is an existing calculation view (@cds.persistence annotations above the Entity name) feel free to play w the command without the flag to see other definitions
With this CDS definition, go ahead and add it to the database CDS file. Notice the changes in the removal of the namespace and the addition of the context structure below. This is so that we do not need to include a namespace in the name of the calculation view.
Once the view is included in the db cds definition, let’s proceed to add it to the OData service.
We’re including it here so we can expose it as a different endpoint. The @readonly annotation is added so that the service shows this is read only and the end consumer does not try to create/update any records using this endpoint.
The next step to insure this endpoint works is to build and deploy (as done before). After it has been deployed, re-run your service from the terminal to see if the new endpoint is exposed. Remember that there may be some caching and the app watch has to be re-run.
When clicking on the endpoint, you can observe that the data is also being returned from the OData service as shown next.
Another common type of database object that is always needed in a real application is a stored procedure. Stored procedures are functions that can read/create/update/delete records in the database. Most of the available examples on the web show the stored proc execution but not many are showing how to pass input parameters into the endpoint and further into the stored procedure itself. In my case, I want to show a stored procedure to create vendors passing parameters from the api to the node function into the stored procedure so it is a full cycle being showcased.
Starting out, we only have the following vendors.
Then, the code in the stored procedure looks like this
In the API, we must create an Action (Functions are only for reading data). My (partial) API definition looks like this (think of GET requests as functions and POST requests as actions) :
Furthermore, I want to show the function with no input params, one function with a single type input param and then an action with a complex type input param which mimics a more realistic scenario. To have code logic to execute these endpoints, we must have an exit, which simply is a js file with the same name as the cds service. In my case, I created a service.js due to the service.cds file.
Below, you can verify the code in the service.js to look at the differences between the last 3 functions I just mentioned. Let’s build and re-deploy using the BAS. I would like to suggest to make sure every time you add artifacts and you test them, push them to the git repository to avoid losing your work.
Once you have done your code, you may test the POST request from postman as follows:
- Notice the complex object in the body of the POST request as my input param of the js function. It was defined as a type in the CDS OData service
- The POST request requires a cookie value passed in the header of the request; you may obtain this one from any of the other get requests (F12 dev tools – Headers – cookie)
** Possible issues errors could come up at this step, be careful when setting up the body of the request to match the input parameter of the action (Vendor), as well as the JSON structure of the type being passed to the action. Should any error come up, you may quickly figure it out, for example and http400 error would immediately tell you that your structure does not match the type.
** Should the POST url be incorrect, you probably get a 404 – notice that a function call required parenthesis at the end of the url, while the action does not require parenthesis.
Voila! Here is the table records after the insert happened successfully.
My Struggles:
- I had difficulties when building the calculation view using mix-case letters. I had to delete and recreate my view with all caps.
- When you need to deploy the project – make sure you’re rebuilding using the cds build command and make sure there are no issues in the terminal window
- Understanding when we have multiple terminals running: always have 2 running (watch in one and also the cds bind –exec npm run app in another.
- Understanding the difference between function and action (for stored procedure endpoint)
Actions: used for operations which add or modify data via POST
Functions: used to only retrieve data via GET
- The new syntax/available functions on CDS nodejs code. I am not sure where to find a comprehensive list of examples for this yet.
Other Resources that helped me complete this exercise:
https://www.youtube.com/watch?v=yceqr9vycrs&t=2402s
https://www.npmjs.com/package/sap-hdbext-promisfied
As you can see I was able to build this simple app and also add a couple additional realistic scenarios, one with a calculation view exposed via the OData service and another scenario using a stored procedure to create a record in our database. Thank you for reading this blog and I hope you were able to follow it. Please share your thoughts or struggles and share this blog with others.
