Create a HANA DB and expose it via OData on SCP in under 45 minutes – 2

Expose HANA DB tables using OData on SCP via HANA Workbench

This is the concluding part of my duology of blogs to create a HANA database and expose it via OData on SAP Cloud Platform using SAP HANA Workbench. This part deals with creating tables in HANA DB and exposing them via OData services.

I have added 24 screenshots and 19 steps in this blog to try to be as detailed as possible.

Prerequisites:
You already have a HANA database running in your SCP trial account. If you are unsure how to do that, refer my first blog on this topic.

The steps mentioned in this blog would help you create your own tables, adding content in them and exposing them via an OData service.

Overview:
It would take you around 15 minutes to finish the steps in this blog.
This blog consists of 19 steps. They cover:

• Creating a table and a service : Steps 1 – 11
• Testing the service in browser and Postman : Steps 12 – 19

I will be referencing the “SAP HANA Web-Based Development Workbench” as Workbench in the below steps. This would mean the below screen:

Let’s get started:

1. Go to the Workbench screen. Click on “Catalog”. On the next screen, right click on “Catalog” on the left pane and choose “New Schema”
   
2. Give a name for your schema and click “OK”.
   
   Note: Choose a name which is specific to your current application. Think of it like a package to store all your related tables and services
   

3. You can view the success message in the console. View your schema under “Catalog” on the left pane.
   

4. Go to the Workbench screen. Click on “Editor”. On the next screen, right click on “Content” on the left pane and choose “Create Application”
   
5. On the next popup, choose the “Template” as “Empty application (with XSAccess and XSApp)”, enter a “Package” name and click on “Create”.Note: Choose a name which is specific to your current application.
   

6. The files “index.html”, “.xsapp” and “.xsaccess” are automatically generated under the package you just created.
   
7. Open the file “.xsaccess” and change the value of “prevent-xsrf” to “false” and “enabled” in “cors” to “true”. Without this change, you will not be able to modify the contents of your table from any external application.
   
8. Right click on your package and choose “New” -> “File”.
   
9. Create a file with any name but with the extension “.hdbschema”.
   I have created a file with the name “myschema.hdbschema”. Type the following in the file and click “Save”. Replace “DUMMY_SCHEMA” with the schema name that you have given in Step 2.

   schema_name = "DUMMY_SCHEMA";​

   Check the success messages in the console.
   

10. Create another file in the same package with the extension “.hdbtable”. The name of this file will be the name of your table.I have created a file with the name “mytable.hdbtable”. Type the following in the file and click “Save”. Replace “DUMMY_SCHEMA” with the schema name that you have given in Step 2.

    table.schemaName = "DUMMY_SCHEMA";
    table.tableType = COLUMNSTORE;
    table.columns = [
                    { name = "ID"; sqlType = VARCHAR; nullable = false; length = 2; },
                    { name = "Name"; sqlType = VARCHAR; nullable = false; length = 20; }
                    ];
    table.primaryKey.pkcolumns = ["ID"];​

    Check the success messages in the console.
    

11. Create a file with any name but with the extension “.xsodata”.
    
    I have created a file with the name “myservice.xsodata”. Type the following in the file and click “Save”.
    Here, replace “dummy_pkg::mytable” with “<package>::<table>”, where <package> is the name from Step 5 and <table> is from Step 10.

    service{
    "dummy_pkg::mytable";
    }​

    Check the success messages in the console.
    

    Note: In case you have multiple tables and want to create a single service for all of them, you can create it by adding the code as shown below

    service{
    "<package>::<table-1>";
    "<package>::<table-2>";
    "<package>::<table-3>";
    }

    —————– Your service is now ready for consumption —————–

    ————————- You can now test your service  ————————-

12. Open your “<filename>.xsodata” and click on “Run” (F8). This would open a browser tab with the below content.
    
13. To check the metadata of “mytable”, add “/$metadata” at the end of the above URL. This would give the following results.
14. Now, to add entries to your table, go to the Workbench screen and click on “Catalog”. Under your schema created in Step 3, you can view your table “mytable” as “dummy_pkg::mytable” under “Tables”.
    
15. Click on “Open Content…” on the right side. On the next screen, click on “Insert”  icon to add new rows in the table.
    
16. Add the content for the new rows and click “Save”.
    Check the success message in the console
    

17. To view the contents of “mytable”, append the text “mytable” at the end of the URL in Step 12.
18. To consume this URL in your application (or via Postman), use Basic Authorization with Username: SYSTEM and the password that you had set while creating your HANA database (Step 4 of my first blog).
    
19. You can add rows to this table using POST method by adding the body in the header

Conclusion:
Once you have followed the steps mentioned in both of my blogs, you would have created a HANA database in your SCP trial account, created tables for your application and created a service to expose these tables. Going forward, whenever you want to create a new application, you can refer to this blog and create a new schema for your new application. To create additional tables in your existing schema, you can start from Step 10 of this blog.

Hope these blogs were helpful to you.

That’s all folks!