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:
- Creation of a new HANA XS instance
- Creation of the 2 new HANA tables
- Creation of a new application
- Definition the application permissions
- Creation the OData service
Let's get started!
1. Creation of a new HANA XS instance
- Open your HANA Trial Cockpit https://account.hanatrial.ondemand.com/cockpit#
- Click on the Databases & Schemas menu and click on New
- Enter the name of the new instance (i.e. "dev"), choose HANA XS as Database System and click on Save
- The instance has been created, now click on the SAP HANA Web-based Development Workbench link
- From the toolbar select Catalog
- Once in the Catalog, click on the SQL button on the top menu
- 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";
- 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")
);
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
- 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
- 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
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
- Right click on this new sub-package and choose Create Application. Choose to create an empty application and click on Create.
- Once the new application is created you should find 3 new files: .xsaccess, .xsapp, index.html
4. Definition the application permissions
- Right click on the "myproducts" application and select New --> File
- 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"}
]
}
- 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"
}
- Create a new file named user.hdbrole, but close the tab that automatically opens
- Right click on the user.hdbrole file and choose to open it with the text editor
- 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;
}
- 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>');
- 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
- 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.
- 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>
- When clicking on the execute button you get the following page:
- Click on the "Metadata file" link. You can get the metadata file for this service.
That's all folks!
- SAP Managed Tags:
20 Comments
