Build database objects/services in HANA service using SAP WebIDE Full-Stack : Part 1

In the previous blog, I walked through the steps to install and configure the HANA service on SAP Cloud Platform Cloud Foundry environment, In this blog series, I will focus on how to get started with building database objects using SAP WebIDE Full-Stack service. One of the main advantages of using SAP WebIDE is that you can now build a full-stack application (Database object, services with business logic and UI components etc). In this blog series, I am going to focus on how to create database objects and expose them as XSOData services. I have come across several requirements where data needs to be stored in SAP HANA service and needs to be exposed for other cloud platform services to consume. Hence, I will start by showing how to create DB artefacts and end by showing how to expose contents of a table using an OData service.

Setup WebIDE Full-Stack service

Enable and launch WebIDE Full-Stack serivce in Neo environment.

In the preferences, enable the below features

• SAP HANA Database Development Tools
• SAP HANA Database Explorer
• Tools for node.js Development

In the Cloud Foundry settings (under Workspace preference), you will need to provide your Cloud Foundry API Endpoint, Organization and space. You can find this info in your global subaccount – Overview menu.

After providing the details, click on the “Install builder”. This is required to enable you to build applications later in this blog.

This should create a builder application in the respective Cloud Foundry space as shown below

Create a Database Module

From WebIDE, create a new project using the template.

Select “Multi-Target Application”

Give a name for the project such as “ProductsApp01” and accept the default to create the project.

Once your project is created, select the folder and use the context menu to navigate to “Project Settings”.

Select the API Endpoint, Organization and Space in the Cloud Foundry section and save the changes

You are now ready to start creating the DB artefacts.

Select the project folder and use the context menu to create a new “SAP HANA Database Module”.

Give it a name as “products01_db”.

Remove the project name from the namespace and select the DB version. In my example, I am using 2.0 SPS 03

Once the project is created, select the “src” folder and use the context menu to create a new “HDB CDS Artifact”

Provide the name as “catalog.hdbcds”. Double click on the catalog table shown below.

This will take us to a blank screen where we can create entities and provide relationships between them. For this blog, we are going to create a simple product entity with set of attributes. On the left hand corner, select the “create entity” button and place the entity in the middle of the screen. Change the name to “product” as shown below.

Double click on the entity to navigate to the elements section

Create 6 elements as shown below with Id as “Key”.

Save your changes. Open the mta.yaml file using the code editor and update the resources section by adding parameters to it.

resources:
 - name: hdi_products01_db
   parameters:
      config:
        schema: product01
        database_id: 72b80a42-ea9d-477a-8722-9f8aa5281a3b
   properties:
      hdi-container-name: ${service-name}
   type: com.sap.xs.hdi-container

Notice that the schema can be any name. The database ID can be obtained from the HANA service dashboard as shown below.

After saving the changes, your yaml file should look like below.

Select the folder “products01_db” and use the context menu to build the artefacts

You should be able to see a success message in the log console.

Switch to the Database explorer from the left hand pane.

Click on the “Connect” button to establish a connection with the database.

When you are in the Database Explorer, click on the “+” sign to add database.

Search for “products” and select the HDI container which you have created.

Navigate to the Tables and select it. This will load all the tables in the bottom left hand corner. Select the product table and view its definitions/schema details. You can also view the contents of the table. It will be empty as we have not yet loaded data into it.

Open the SQL console from here and also look up the schemas which you have created as shown below.

Next, lets prepare a CSV file to upload data into the table. Select the “src” folder and use the context menu to create a new “Database Artifact”

Provide the name as “sampledata” and select file type as “CSV”

Provide sample values into the editor. Notice the header is retained at the top.

Similarly, create another database artefact of type hdbtabledata. Give it a name “uploadProducts”.

Paste the below contents into the file and save your changes. In the below file, we are referring to the csv file and providing the target table to upload the data. Notice that the object names are case sensitive.

{
    "format_version": 1,
    "imports": [
        {
            "target_table": "products01_db::catalog.product",
            "source_data": {
                "data_type": "CSV",
                "file_name": "products01_db::sampledata.csv",
                "has_header": true
            },
            "import_settings": {
                "import_columns": [
                    "Id","name","supplierName","category","unitPrice","unitsInStock"
                ]
            }
        }
    ]
}     

Below is how your file should look like:

Now you are ready to build the files. Select the folder “ProductsApp01” and use the context menu to Build the database module.

If the build is successful you should see a success message in the logs.

Navigate to the database explorer to view the contents of the table

Note: While creating a database module, the wizard provides the namespace as default. You can remove it to make it more easier to access the DB entities. Thanks to Lucia Subatin for pointing it out.

The next part of this blog is to create a nodejs module which will support the executing of a service.