Skip to Content
Author's profile photo SIMMACO FERRIERO

How to create a new OData service using the Web-Based Workbench (v2.0)

Introduction

I’ve decided to upgrade this blog to version 2 because, instead of creating a simple basic table on the HANA XS platform, I would like to show you how to create two tables in a master-detail relation so that this kind of scenario can be used as OData source for one of the Fiori Application templates available with SAP Web IDE. For this exercise we will use the HANA Trial landscape publicly available here. You just need to register and get your access to the full trial HANA platform.

We won’t use any external development tool like Eclipse, but we will rather take advantage of the integrated SAP HANA Web-based Development Workbench.

The final result of this blog will be used in another blog of mine at this link.

The scenario we are going to use is a table named Suppliers containing some very basic information about a limited number of suppliers and a table named Products which is linked to the Suppliers via a field named SupplierID: each product has a field which points to the supplier of that product. This means that we have a 1:n relation between Suppliers and Products. A single supplier might provide multiple products, but a product can have just one supplier. Even though this is not too much realistic, this fits very well our example.

Prerequisites

No particular prerequisites are needed for this blog: just a web browser and an account on the HANA Trial landscape.

Once you have registered to this service you will receive a username, usually a character and a number (i.e. i012345 or p01234567). You will be also assigned with an account name, which is normally the concatenation of your username with the word “trial”.

So in this document we will refer to

<your_HANA_trial_username> as the username you received when you registered to the Trial landscape

<your_HANA_trial_account> as “<your_HANA_trial_username>trial

Pay also attention to the fact that here we have used the following hardcoded names:

dev for the instance name

Suppliers  and Products for the name of the tables

myproducts for the name of the package/application

If you want to have different names for these objects, please remember to change them accordingly in all the SQL statements listed here.

Walkthrough

This is the list of steps we will go through:

  1. Creation of a new HANA XS instance
  2. Creation of the 2 new HANA tables
  3. Creation of a new application
  4. Definition the application permissions
  5. Creation the OData service

Let’s get started!

1. Creation of a new HANA XS instance

  • Click on the Databases & Schemas menu and click on New

/wp-content/uploads/2015/03/01_916254.png

  • Enter the name of the new instance (i.e. “dev“), choose HANA XS as Database System and click on Save

/wp-content/uploads/2015/03/02_916255.png


  • The instance has been created, now click on the SAP HANA Web-based Development Workbench link

/wp-content/uploads/2015/03/03_916256.png

  • From the toolbar select Catalog

/wp-content/uploads/2015/03/04_916257.png

  • Once in the Catalog, click on the SQL button on the top menu

/wp-content/uploads/2015/03/06_916258.png

  • Paste the following select statement  in the right side text area and click on the Execute button. You will get the name of the schema the you need to use for creating all the upcoming objects

SELECT SCHEMA_NAME FROM "HCP"."HCP_DEV_METADATA";










/wp-content/uploads/2015/03/07_916259.png

  • Copy this name and paste it in a separate text file in order to keep it for later use.


NOTE: From this moment on, when mentioning <NEO_schema_name>, we’ll refer to this string (i.e. “NEO_843LFYZXFMFDUHHV53EQB8A4M”).

2. Creation of a new HANA Table

You should be still on the Catalog page. If you are not, please reopen it.

  • Open a new SQL Query and paste there the following text. Then click on the execute button and check the message in the console. Two new tables, Suppliers and Porducts, have been created

-- DROP TABLE "<NEO_schema_name>"."Products";
CREATE COLUMN TABLE "<NEO_schema_name>"."Products" (
  "ProductID" VARCHAR(20) NOT NULL ,
  "Name" NVARCHAR(256),
  "Description" VARCHAR(256),
  "Price" DECIMAL(10,4),
  "CurrencyCode" VARCHAR(3),
  "PictureURL" VARCHAR(256),
  "SupplierID" VARCHAR(20),
  PRIMARY KEY ("ProductID")
);
-- DROP TABLE "<NEO_schema_name>"."Suppliers";
CREATE COLUMN TABLE "<NEO_schema_name>"."Suppliers" (
  "SupplierID" VARCHAR(20) NOT NULL ,
  "SupplierName" NVARCHAR(256),
  "Addresss" VARCHAR(256),
  "EmailAddress" VARCHAR(256),
  PRIMARY KEY ("SupplierID")
);










/wp-content/uploads/2015/03/08_916260.png

NOTE: Remember to replace the string <NEO_schema_name> with the name of your schema you found at the previous step

  • From the Catalog Explorer on the left side expand the name of your schema: you will find the new table under the Tables branch

/wp-content/uploads/2015/03/09_916261.png


  • We want now to put some sample records in these two tables. Open a new SQL Query tab and paste the following lines. For all the lines you need to replace the string <NEO_schema_name> with the name of your schema. Then click on the Execute button and check the result in the console. You shouldn’t get any error.

-- TRUNCATE TABLE "<NEO_schema_name>"."Products";
INSERT INTO "<NEO_schema_name>"."Products" VALUES('P001','Apple','Apple Gala',50.67,'USD','/images/P001','S001');
INSERT INTO "<NEO_schema_name>"."Products" VALUES('P002','Pineapple','Gold Pineapples',20.31,'USD','/images/P002','S002');
INSERT INTO "<NEO_schema_name>"."Products" VALUES('P003','Peach','Super Sweet Peaches',30.12,'EUR','/images/P003','S001');
INSERT INTO "<NEO_schema_name>"."Products" VALUES('P004','Banana','Bananas',80.48,'CHF','/images/P004','S002');
INSERT INTO "<NEO_schema_name>"."Products" VALUES('P005','Milk','Milky',60.96,'EUR','/images/P005','S003');
-- TRUNCATE TABLE "<NEO_schema_name>"."Suppliers";
INSERT INTO "<NEO_schema_name>"."Suppliers" VALUES('S001','Acme Export','New York','acme@test.com');
INSERT INTO "<NEO_schema_name>"."Suppliers" VALUES('S002','Nature Food','Boston','nf@test.com');
INSERT INTO "<NEO_schema_name>"."Suppliers" VALUES('S003','Northern Cathering','Denver','cath@test.com');










For all the three lines you need to replace the string <NEO_schema_name> with the name of your schema

/wp-content/uploads/2015/03/11_916262.png

  • If you right click on the name of one of the tables in the Catalog Explorer you can choose Open Content to display its records

/wp-content/uploads/2015/03/12_916263.png

3. Creation of a new application

  • Go back to the SAP HANA Web-based Development Workbench tab
  • Expand the Content\<your_HANA_trial_account> branch and select the dev package. Right click on it and choose New –> Package in order to create a new sub-package of “dev”. Enter the name of the sub-package (i.e. “myproducts”) and a description and click on Create

     /wp-content/uploads/2015/03/13_916264.png

  • Right click on this new sub-package and choose Create Application. Choose to create an empty application and click on Create.

/wp-content/uploads/2015/03/14_916265.png

  • Once the new application is created you should find 3 new files: .xsaccess, .xsapp, index.html

/wp-content/uploads/2015/03/15_916266.png

4. Definition the application permissions

  • Right click on the “myproducts” application and select New –> File

/wp-content/uploads/2015/03/16_916267.png

  • Create a new file named .xsprivileges, enter the following content and save the file. You shouldn’t get any error message in the console

{
"privileges": [
  {"name": "Execute", "description": "Execute"}
]
}










/wp-content/uploads/2015/03/17_916271.png

  • Click on the .xsaccess file, it will be opened in the editor. Add the line number 6 and change the “prevent_xsrf” parameter from “true” to “false” at line 13. Then save the file. Again, no errors should appear in the console window. Remember to replace the string <your_HANA_trial_account> with your real account

{
    "exposed": true,
    "authentication": [{
        "method": "Form"
    }],
    "authorization": ["<your_HANA_trial_account>.dev.myproducts::Execute"],
    "mime_mapping": [{
        "extension": "jpg",
        "mimetype": "image/jpeg"
    }],
    "force_ssl": false,
    "enable_etags": true,
    "prevent_xsrf": false,
    "anonymous_connection": null,
    "cors": [{
        "enabled": false
    }],
    "cache_control": "no-cache, no-store",
    "default_file": "index.html"
}


/wp-content/uploads/2015/03/18_916610.png

  • Create a new file named user.hdbrole, but close the tab that automatically opens

     /wp-content/uploads/2015/03/19_916272.png

  • Right click on the user.hdbrole file and choose to open it with the text editor

     /wp-content/uploads/2015/03/20_916273.png

  • Paste the following content paying attention to replace the string <NEO_schema_name> with the name of your schema. Save the file. Check that you don’t get any error in the console.

role <your_HANA_trial_account>.dev.myproducts::user
{
  catalog schema "<NEO_schema_name>": CREATE ANY, DROP, INDEX, SELECT, INSERT, UPDATE, DELETE;
  application privilege: <your_HANA_trial_account>.dev.myproducts::Execute;
}










/wp-content/uploads/2015/03/21_916274.png

  • Open again the Catalog, click on the SQL button and paste in the SQL text area the following statement. Then Execute the script and check that you have no errors in the console.

Remember to replace the string <your_HANA_trial_account> with your real account and <your_HANA_trial_username> with your username


CALL HCP.HCP_GRANT_ROLE_TO_USER('<your_HANA_trial_account>.dev.myproducts::user','<your_HANA_trial_username>');










/wp-content/uploads/2015/03/22_916275.png

  • Close the Catalog tab

5. Creation the OData service

  • Go back to the SAP HANA Web-based Development Workbench
  • Right click on the myproducts application and select New –> File

  • Create a new file named services.xsodata and paste the following content there. Then save the file.

service {
    "<NEO_schema_name>"."Products" as "Products"
        create forbidden
        update forbidden
        delete forbidden;
    "<NEO_schema_name>"."Suppliers" as "Suppliers"
        navigates ("SupplierProducts" as "Products")
        create forbidden
        update forbidden
        delete forbidden;
    association "SupplierProducts"
        principal "Suppliers"("SupplierID") multiplicity "1"
        dependent "Products"("SupplierID") multiplicity "*";
}










Replace again the string <NEO_schema_name> with the name of your schema

/wp-content/uploads/2015/03/23_916277.png

  • When pressing the Execute button on the toolbar, you should get the working service. This is the URL you can use for addressing your new service.

/wp-content/uploads/2015/03/24_916278.png

  • Optionally, you can also fill in the index.html file with the following content, paying attention to replace the host name with the one in your URL



<html>
    <head></head>
    <body>
        <h1>The service is working fine</h1>
        <h2>Click here for the metadata file</h2>
        <a href="https://s12hanaxs.hanatrial.ondemand.com/i045523trial/dev/myproducts/services.xsodata/$metadata">Metadata file</a>
    </body>
</html>

/wp-content/uploads/2015/03/25_916279.png

  • When clicking on the execute button you get the following page:

/wp-content/uploads/2015/03/26_916280.png

  • Click on the “Metadata file” link. You can get the metadata file for this service.

/wp-content/uploads/2015/03/27_916281.png

That’s all folks!

Assigned Tags

      20 Comments
      Comments are closed.
      Author's profile photo Former Member
      Former Member

      Excellent Tutorial!!

      How do you consume this service in a FIORI APP via the SAP Web IDE ?

      Author's profile photo Denise Nepraunig
      Denise Nepraunig

      Not really. And I believe this won't change.

      Accessing HANA XS OData in Web IDE and beyond

      Author's profile photo Rishi Khandelwal
      Rishi Khandelwal

      Great Tutorial!! Helped a lot.. 🙂

      Just wondering how can we use association and navigation so that generated xml metadata will be consumed in a master-master detail ???

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Hi Rishi,

      you can check out my Blog: Raspberry Pi on SAP HANA Cloud Platform - without Java - Part 1: XSODATA Backend where I use two tables linked together.

      Best regards

      Gregor

      Author's profile photo Rishi Khandelwal
      Rishi Khandelwal

      Many Thanks Gregor.

      Author's profile photo Ged Hurst
      Ged Hurst

      got me started on webide - many thanks!

      Author's profile photo Former Member
      Former Member

      Hi,

      Thank you for sharing the step-step process.

      While trying myself, I'm getting error at CALL HCP.HCP_GRANT_ROLE.

      And also after creating the services.xsodata, I couldn't see the "Run" button.

      Is this something to do with authorizations??

      Author's profile photo Michel De Kimpe
      Michel De Kimpe

      Hi,

        If I save the file '.xsaccess' I always get the error

      "Unknown privilege "trialuser.dev.empdemo::Execute"."

        Any idea's on how to solve this ?

      Author's profile photo Neil Boardman
      Neil Boardman

      Hi Michel,

      Not sure if you resolved your error, I was getting the same error "Unknown privilege "trialuser.dev.empdemo::Execute"." because I had initially misnamed my .xsprivileges file.

      Renaming the file correctly resolved the issue for me.

      Author's profile photo Michel De Kimpe
      Michel De Kimpe

      Hello Neil,

      Thanks for your reaction, the filenames seems to be correct.

      Any more idea's ?

      Regards,

      M

      Author's profile photo Rakesh Jammula
      Rakesh Jammula

      amazing 🙂

      Author's profile photo Rakesh Jammula
      Rakesh Jammula

      Hello Michel,

      When I am executing ODATA services, I get following error

      "Request Execution Failed due to Missing privileges"?

      How to avoid it, I followed same steps listed above.

      BR, Rakesh

      Author's profile photo Rakesh Jammula
      Rakesh Jammula

      now its working.

      Author's profile photo Michel De Kimpe
      Michel De Kimpe

      Hello,

      Can you tell me how you solved it ?

      It's still not working for me.

      Thanks.

      Regards,

      M

      Author's profile photo Former Member
      Former Member

      Hi ,

      every thing is working fine but i am not able to get the data in json format ,only this is coming

      { "d": { "EntitySets": ["Employees"] } }

      i want to fetch all the data stored in table and also i created two tables .please help me to get both tables data .

      Regards,

      viplove

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Viplove,

      what URL do you use when you get this data?

      Best regards

      Gregor

      Author's profile photo Former Member
      Former Member

      Hi Gregor,

      After executing my odata service i am getting this link and i am modfying it to get the data in json format.

      https://s10hanaxs.hanatrial.ondemand.com/p1941859219trial/stark1db/starksub/services.xsodata?$format=json

      regards,

      viplove

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Hi viplove,

      with that URL that is perfectly fine. When you use:

      https://s10hanaxs.hanatrial.ondemand.com/p1941859219trial/stark1db/starksub/services.xsodata/Employees?$format=json

      you should get the data back. Perhaps you have to read up on the use of OData.

      Best regards

      Gregor

      Author's profile photo Former Member
      Former Member

      Hi Gregor,

      it would be a great help from your side if i am getting a blog or document  from you on  how to read up OData .

      Regards,

      viplove

      Author's profile photo Gregor Wolf
      Gregor Wolf
      Comments are closed.