Skip to Content
Technical Articles

Create an oData from an Existing Business Service using SAP Cloud application programming model

This blog will enable you to create oData using SAP Cloud Application Programming model using an existing DB.

Let us talk a little more about this scenario. Assume that you have developed a Business Service (could be multi-tenant as well) to expose rest endpoints. This service is up & running and it is being used by consumers. This service is bound to a HANA database and service data is saved in DB (different schema in case of a multi tenant service).

Now, there is a requirement for you to also provide oData, which exposes the business service data. Cloud Application Programming model can be help you build the oData on top of the existing DB. You can reuse the DB service as well as the xsuaa service to create the oData.

Please note that we could also develop the UI with the rest endpoints, but this will definitely need some amount of coding. Whereas if you have an oData, creating the UI becomes very simple with the usage of predefined smart templates available in webIDE. In very few steps you will be able to bind the oData to the UI and run it without writing much code.

Please follow the below steps to create the oData from an existing db.

1. Create an SAP Cloud Platform Business Application from webIDE.

2. Edit the MTA file. In the resource section, you can provide the existing DB and xsuaa services. The service(srv) and db modules too should be bound to the existing DB and xsuaa. Please change the dependency in the require section of db and srv.

3. Create cds files corresponding to hdbcds file in webIDE for the oData. Please give the same namespace provided in the hdbcds file(existing). Tag the entity as @cds.persistence.exists. This will make sure that no table creation in done and the data is fetched from the existing db which was already created. The tables/schema would have already been created using hdbcds file when your Business Service was deployed.

It should have all the fields which you would like to expose in the oData. You can not have additional fields unless you add it in your hdcds file as well. This means that it is going to fetch the data from the db and it will not be able to map anything extra which is not defined in the db.

You must provide a different context name in the data-model.cds or else you will get an error for duplicate entity. Please be careful in providing the namings in data-model.cds and my-service.cds. I have prefixed ‘new’ wherever it is possible to use new names.

4. Edit the my-service.cds file to expose the entities you have defined in the data-model.cds.

5. Change the sql_mapping to hdbcds in the root level package.json. This is needed to generate the exact same names of the entities and tables which is present in hana (existing DB). By default, it is plain. In plain mode, it generates the names in all caps and there is no :: in the table names. But this will not work with existing DB.

6. Build cds for the project and check the csn.json (srv->src->resources->edmx). @cds.persistence.name should have the same name which is present in your db.

7. Build the project and deploy it the space where your database service is deployed. Only then it will be able reuse the existing db.

8. Once the application is deployed, you can access the oData from postman or browser. If the db is secured with oAuth2, you will need jwt to access the oData. This jwt is same as what you would use to access your service.

9. oData can be accessed using the url which is generated in the srv module after deployment along with the namespace you provided. It will look something like – https://abc-srv.cfapps.sap.hana.ondemand.com/odata/v2/com.sap.existingdb.ExistingContextName. You can then access the metadata as well as the list of entities exposed.

10. All the operations supported on oData will work on the generated oData from SAP Cloud Application Programming model e.g. $select, $filter, $top etc.

Bonus – You can check the db name in webIDe directly. You will need to enable the “SAP HANA database development tools” from the Tools->Preferences->Features.

6 Comments
You must be Logged on to comment or reply to a post.
  • Hi Neha,

    Thank you for this nice tutorial. Finally, we managed to expose single CDS views created on the base of tables from SLT replication as oData v4 services using NodeJS (which was not possible in earlier oData solutions from SAP). So we moved on and for the oData expand feature we tried to implement a one to many association based on these links:

    https://github.com/SAP/cloud-sample-spaceflight-node/blob/master/db/data-model.cds

    https://help.sap.com/viewer/52715f71adba4aaeb480d946c742d1f6/2.0.03/en-US/809a42308d814b7ea1c8369e55591515.html

    Our data-model.cds:

    @cds.persistence.exists
    Entity PRODUCTS {
         TEST: Association[0..*] to COLORS {MATNR, WERKS};
         key MATNR: String(18) not null;
         key WERKS: String(4) not null;
         MAKTX: String(40) not null;
         MATKL: String(9) not null;
         […]
    }

    Unfortunately, when we call the service, we get “Unexpected SQL Error occurred” and “ERR {[Error: invalid column name: TEST_MATNR: line 1 col 8 (at pos 7): `SELECT TEST_MATNR, TEST_WERKS, MATNR, WERKS, MAKTX, MATKL, […] FROM XYZ_PRODUCTS LIMIT 1000`]”

    Apparently, the generated query is wrong.

    Are we doing something wrong or is the association/expand feature not yet implemented?

    Regards,
    Jan

  • Hi Jan,

    The expand feature is not yet supported bySAP Cloud Application Programming model as far as I know.

    Thanks,

    Neha

    • Hi Neha,

      10. All the operations supported on oData will work on the generated oData from SAP Cloud Application Programming model e.g. $select, $filter, $top etc.

      If this statement is not true, are there more things known that don’t work?

       

      Thanks,

      Lukas

      • Hi Lukas,

         

        CAP supports OData V2 and V4. More information on supported feature can be found in the SAP wiki page for CAP.

         

        Thanks,

        Neha

         

    • Hi Neha,

      Thank you for your response.
      Due to the fact, that expands and nested filters in expands are key features in oData v4, which are needed for many projects, could you give any information about the roadmap concerning this feature?

      Regards,
      Jan

      • Hi Jan,

         

        The expand feature is supported in OData V4 and you can switch it some basic setting :-

        1. package.json :-
        cds": {data": {"sql_mapping": "quoted"},"service": {"odata": {"version": "v4"}}},

         

        2. pom.xml under build tab-

        <properties>

            <packageName>com.sap.clouds.catalog</packageName>   

         </properties>

         

        There are few additional setting in web.xml and catalogueService.xml , which i am not to attach in the comment.

        I will try to provide you the information in the wiki via mail.

        Thanks,

        Neha