Technical Articles

Neha Soni

December 3, 2018 3 minute read

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.

11 Comments

You must be Logged on to comment or reply to a post.

Jan Simon Kaltenecker

December 13, 2018 at 7:41 am

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

Naurin Jamil

June 2, 2019 at 2:14 pm

Hello Jan,

Did you find a solution to your problem. I am facing the exact same issue. Should the association be written differently in the .hdbcds? Please share.

Thank you,

Naurin

Neha Soni

Blog Post Author

January 2, 2019 at 11:35 am

Hi Jan,

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

Thanks,

Neha

Lukas Berwanger

January 16, 2019 at 9:28 am

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

Neha Soni

Blog Post Author

February 9, 2019 at 7:50 am

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

Gregor Wolf

June 11, 2019 at 7:37 pm

Hi Neha,

you're mentioning the SAP Wiki Page for CAPM. But I can't find anything at https://wiki.scn.sap.com/ which is the external Wiki. I think you mean the SAP internal Wiki. Is there anything planned to be published?

Best regards
Gregor

Jan Simon Kaltenecker

January 16, 2019 at 9:42 am

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

Neha Soni

Blog Post Author

February 9, 2019 at 7:48 am

Hi Jan,

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

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

Sasi Reddy

October 14, 2019 at 6:19 pm

Hi Neha Soni

Our app is CAP app with DB and nodejs. We have mobile odata services tied to the same DB which requires few columns to be in lower case (for example: row_version). I have added the sql_mapping in all these files and yet the hdbcds views in DB are all getting generated with all CAPS. Please let me know where i am missing.

Below is what i have in .cdssrc.json.

{
	"build": {
		"target": ".",
		"tasks": [{
			"for": "hana",
			"src": "db",
			"options": {
				"model": [
					"db",
					"srv"
				],
				"sql_mapping": "hdbcds"
			}
		}, {
			"for": "node-cf",
			"src": "srv",
			"options": {
				"model": [
					"db",
					"srv"
				],
				"sql_mapping": "hdbcds"
			}
		}]
	}
}

Below is what i have in package.json in nodejs module

	"cds": {
		"data": {
			"driver": "hana",
			"sql_mapping": "hdbcds"
		},
		"requires": {
			"db": {
				"kind": "hana",
				"model": "gen/csn.json"
			}
		}
	}

Below is what i have in package.json in the main module along with mta.yaml

	"cds": {
		"requires": {
			"db": {
				"kind": "hana",
				"model": [
					"db",
					"srv"
				],
				"sql_mapping": "hdbcds"
			}
		}
	}

Thanks

Sasi

Sasi Reddy

October 14, 2019 at 9:28 pm

Hi Neha Soni

Once I added SQL_MAPPING under cds-> data node in package.json in the main module which is in the same folder as mta.yaml in CAP app, i am running into the below error while building CDS view, any idea what is wrong with it?

	"cds": {
		"data": {
			"sql_mapping": "hdbcds"
		},
		"requires": {
			"db": {
				"kind": "hana",
				"model": [
					"db",
					"srv"
				],
				"sql_mapping": "hdbcds"
			}
		}
	}

CDS View:

entity newProducts @(
		title: '{i18n>Products}',
		Capabilities: {
			InsertRestrictions: {Insertable: true},
			UpdateRestrictions: {Updatable: true},
			DeleteRestrictions: {Deletable: true}
		}
	) as projection on Products;

Error on last line of the CDS view:

2:42:42 PM (Executor) [ERROR] srv/my-service.cds:33:21: With "hdbcds" naming, managed association elements cannot be used in a view

Thanks

Sasi

Sasi Reddy

October 16, 2019 at 6:59 pm

Hi All

Got this to working by adding the below in nodejs SRV package.json as well.

	"cds": {
		"data": {
			"driver": "hana",
			"model": "db/my.cds",
			"sql_mapping": "quoted"
		},
		"service": {
			"model": "srv/my-service.cds",
			"sql_mapping": "quoted"
		},
		"requires": {
			"db": {
				"kind": "hana",
				"model": "gen/csn.json"
			}
		}
	}

Thanks

Sasi

Technical Articles

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

Assigned Tags