Introduction to HANA XS application development (Part 5): BlogProject exposing data with OData services and server-side JavaScript, and performing CRUD operations from ui
Hello everyone,
Catch up with the previous posts of the series:
In this 5th part (I hope you didn’t get bored) we will see how to use server-side JavaScript to expose a procedure and how to perform CRUD operations with OData, more specifically how to expose our previously prepared data model to the user and how to transform our data. Of course after the exposure we will see simple CRUD operations from the ui.
OData
Open Data Protocol (OData) is a data access protocol that was designed to provide standard CRUD access to a data source via a website. To do so, OData defines operations on resources using RESTful HTTP commands (for example, GET, PUT, POST, and DELETE) and specifies the URI syntax for identifying the resources. Data is transferred over HTTP using either the Atom (XML) or the JSON (JavaScript) format.
An OData service running in SAP HANA XS is used to provide the consumption model for client applications exchanging OData queries with the SAP HANA database.
Notes:
- For modification operations, for example, CREATE, UPDATE, and DELETE, SAP HANA XS supports only the JSON format (“content-type: application/json”).
- Language encoding is restricted to UTF-8.
Create OData
In order to create an OData service, we right-click on the “services” folder of our XS application and create a new “XS OData file” file or we create a simple “file” adding the extension “.xsodata”.
- XS OData service definition
A simple XS OData service definition looks like this:
service {
“BlogProject”.”POST” as “post”;
}
Here we define the table (POST) to expose and how this entity will be called (post) in our OData model.
Note: When defining an object you can call it by its repository name () or catalog name (), although it is recommended to use the repository name whenever possible. Specifically for the calculation views, they are only accessible with their repository name. In our application the tables have been created via the catalog, thus they do not have a repository name.
Of course the definition of an OData can be rather complex, as it utilizes a lot of different features. Some of them are displayed in the following
Property Projection
service {
“BlogProject”.”POST” as “post” without (“UserID”);
}
Here the “UserID” column will not be exposed. Except from “without”, we can use the “with” keyword to perform a projection of the columns that we want to expose.
Key Specification
service {
“BlogProject”.”POST” as “post” key (“ID”);
}
To specify which column of the table to be dealt as a key.
service {
“BlogProject”.”POST” as “post” key generate local “genID”;
}
If the entity we want to expose has not a unique key, we can generate a key via the OData service.
The key specification is used to define the URL of a single row of the entity. In the URL OData calls that we will see, the e
Associations
Service {
“MyBlogProject.models::AT_COMMENT” as “Comments” without (“Text”) key (“ID”) navigates (“Comment_Post” as “its_post”);
“MyBlogProject.models::CALC_POST” as “Posts” key (“ID”);
association “Comment_Post” principal “Comments”(“PostTitle”) multiplicity “*” dependent “Posts”(“PostTitle”) multiplicity “1”;
}
First of all, we can see here that we can define more than one entities per OData service. If we do this, we can refer to any entity we want as if it was the only one defined inside the service.
An association allows as to navigate from an entity to another. In this example to see, for a specific comment, the post it was written to.
We define the association by specifying a name for the association (“Comment_Post”), the principal (from) and dependent (to) entities, the columns that act like foreign keys (from the comment’s post title to the title of the post) and the multiplicity (many comments connect to 1 post). Lastly, we define the navigation of the entity “Comments”, creating the navigation property (“its_post”).
Aggregation
service {
“sample.odata::calc” as “CalcView”
keys generate local “ID”
aggregates always;
}
This is used when the aggregation is derived from the metadata of exposed object, a calculation view for example.
service {
“sample.odata::revenues” as “Revenues”
keys generate local “ID”
aggregates always (SUM of “Amount”); }
When there is no aggregation specified, we can define it via the OData service.
Parameter Entity Sets
service {
“sample.odata::calc” as “CalcView”
keys generate local “ID”
parameters via entity;
}
If we want to expose an analytic or calculation view, which can have input parameters, we can interpret the parameters using the above definition.
Note 1: More information about OData in XS can be found in the SAP HANA Developer Guide p.376 – 416.
Note 2: By default an OData service enables create, update and delete operations. To change that, we have to specify which operation we want to forbid, using the keyword “forbidden”. We will see how it works in the next step.
Note 3: In my trial-and-error attempts I made the following observations:
- XSOdata does not support the TEXT column type (according to theory “The OData implementation in SAP HANA Extended Application Services (SAP HANA XS) does not support all SQL types”).
- If we define both “aggregates always” and “navigates” an error will be produced.
- The create, update and delete forbidden features are not supported at the same time with “aggregates always”.
- Hidden columns (defined in the Semantics node of the information views) are exposed through OData but the columns hold no values. To prevent the exposure we have to use the “without” feature in the OData service definition.
- Hidden columns cannot be used to create association between two entities
BlogProject OData services
The previous OData services definitions were made for the understanding of the concept.
For our BlogProject application we will create OData services that will have two roles. The first one will be to expose our analytic model (information views) to the ui application, and the second will be to expose our tables to support modification operations to the data, using stored procedures.
Analytic model
- Calc_views.xsodata
service {
“MyBlogProject.models::CALC_POST” as “Posts” key (“ID”) navigates (“Post_Comments” as “its_comments”, “Post_User” as “its_user”)
create forbidden
update forbidden
delete forbidden;
“MyBlogProject.models::CALC_USER” as “Users” key (“ID”) navigates (“User_Posts” as “users_posts”)
create forbidden
update forbidden
delete forbidden;
“MyBlogProject.models::AT_COMMENT” as “Comments” key (“ID”) navigates (“Comment_Post” as “its_post”)
create forbidden
update forbidden
delete forbidden;
association “Comment_Post” principal “Comments”(“PostTitle”) multiplicity “*” dependent “Posts”(“PostTitle”) multiplicity “1”;
association “User_Posts” principal “Users”(“ID”) multiplicity “1” dependent “Posts”(“UserID”) multiplicity “*”;
association “Post_Comments” principal “Posts”(“CommentID“) multiplicity “1” dependent “Comments”(“ID“) multiplicity “*”;
association “Post_User” principal “Posts”(“UserID”) multiplicity “1” dependent “Users”(“ID”) multiplicity “*”;
}
In this service we expose the 3 of the 4 information views we created in the 3rd part, CALC_POST, CALC_USER and AT_COMMENT. We used the forbidden feature to ensure that these entities will be used only for reading purposes. Then we defined 3 association so that we can navigate from a comment to the corresponding post, from a user to all his/her posts and from a post to its user.
- country_calc_view.xsodata
service {
“MyBlogProject.models::CALC_COUNTRY” as “Countries” key (“ID”)
create forbidden
update forbidden
delete forbidden;
}
Here we expose, like previously, the CALC_COUNTRY calculation view, so that we can expose our country data for statistical reasons.
Modification operations
The services that we will define below use the two insert procedures that we created in the previous part.
- post.xsodata
service {
“BlogProject”.”POST” as “post”
create using “MyBlogProject.procedures::insert_post”;
}
- comment.xsodata
service {
“BlogProject”.”COMMENT” as “comment”
create using “MyBlogProject.procedures::insert_comment”;
}
These definitions are similar to the first simple definition we show in the beginning of this post, with the difference that the creation of a new row in the exposed entities is not performed via the OData services, but with the use of a procedures. So every time a creation operation is required, the procedures are called.
Calling the OData services
To see and test our OData services we can simply call them from any browser using certain URL calls. For example:
http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/
Calls the OData service in the XML format. This exposes the names of the entities this service exposes
http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/$metadata
Gets the metadata of the service. For example, the columns of each entity and the defined associations.
http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/Posts
Gets the data of the “Posts” entity in XML format.
http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/Posts(‘2’)
Gets the data of the 2nd row of the “Posts” entity in XML format.
http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/Posts?$format=json
Gets the data of the “Posts” entity in JSON format.
http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/Posts?$filter=UserID eq ‘2’
Gets the data of the “Posts” entity in XML format, where the “ID” column equals 2.
Gets the data of the “Posts” entity in JSON and sorts the results based on the column “UserID”.
http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/Posts?$format=json&$top=3&$skip=1
Gets the data of the “Posts” entity in JSON, skipping the first row and selecting the 3 first tuples.
CRUD operation using OData services from the ui
The OData model enables binding of SAOUI5 controls to data from OData services. The OData model is a server-side model, meaning that the dataset is only available on the server and the client only knows the currently visible rows and fields. Sorting and filtering is done on the server. The client has to send a request to the server to accomplish these tasks.
Read the data from OData services and bind them to the ui
//OData model creation. The false property is referring to whether the requested data are in JSON format or not
var oModel = new sap.ui.model.odata.ODataModel(‘/MyBlogProject/services/Calc_views.xsodata/’, false);
var oControl;
oTable = new sap.ui.table.Table(“test”, {
tableId: “tableID”,
visibleRowCount: 10
});
oTable.setTitle(“Posts”);
//Table Column Definitions
//bindProperty() binds the property “value” of the TextField to the column “ID” of our data
oControl = new sap.ui.commons.TextField().bindProperty(“value”, “ID”);
oTable.addColumn(new sap.ui.table.Column({
label: new sap.ui.commons.Label({text:“ID”}),
template :oControl,
sortProperty :“ID”,
filterProperty :“ID”,
width :“125px”
}));
oControl = new sap.ui.commons.TextField().bindProperty(“value”, “Date”);
oTable.addColumn(new sap.ui.table.Column({
label: new sap.ui.commons.Label({text:“Date”}),
template :oControl,
sortProperty :“Date”,
filterProperty :“Date”,
width :“125px”
}));
oControl = new sap.ui.commons.TextField().bindProperty(“value”, “Username”);
oTable.addColumn(new sap.ui.table.Column({
label: new sap.ui.commons.Label({text:“Username”}),
template :oControl,
sortProperty :“Username”,
filterProperty :“Username”,
width :“125px”
}));
oControl = new sap.ui.commons.TextField().bindProperty(“value”, “PostTitle”);
oTable.addColumn(new sap.ui.table.Column({
label: new sap.ui.commons.Label({text:“PostTitle”}),
template :oControl,
sortProperty :“PostTitle”,
filterProperty :“PostTitle”,
width :“125px”
}));
oControl = new sap.ui.commons.TextField().bindProperty(“value”, “PostText”);
oTable.addColumn(new sap.ui.table.Column({
label: new sap.ui.commons.Label({text:“PostText”}),
template :oControl,
sortProperty :“PostText”,
filterProperty :“PostText”,
width :“125px”
}));
//we define the OData model to be used for the table
oTable.setModel(oModel);
//filter and sorter definition
var oFilter = new sap.ui.model.Filter(“ID”, sap.ui.model.FilterOperator.StartsWith, “1”);
var oSorter = new sap.ui.model.Sorter(“ID”);
//Lastly, we bind the rows of the table to the specific entity “Posts” of the OData model
oTable.bindRows(“/Posts”, oSorter);
//If we want to define a projection we can use the select parameter
//oTable.bindRows({path: “/Posts”, parameters: {select: “Username”}});
Insert data
Remember, we perform data transformation with the post.xsodata and the comment.sxodata services that we have created for this purpose. The previous service is forbidden to transform the data.
var oModel= new sap.ui.model.odata.ODataModel(‘/MyBlogProject/services/post.xsodata/’, false);
//we create an object to be passed as a JSON object
var oEntry = {};
//we add to the JSON object the values of the columns of the row we want to insert
oEntry.ID = “4”;
oEntry.Date = “”;
oEntry.UserID = “1”;
oEntry.PostTitle = “Hello”;
oEntry.Tags = “hi”;
oEntry.Subject = “”;
oEntry.Likes = 0;
oEntry.Dislikes = 0;
oEntry.Views = 0;
oEntry.PostText = “Universe”;
//we perform the insert request using the create() method of the model
oModel.create(‘/post’, oEntry, null, function() {
alert(“SUCCESS”);
}, function() {
alert(“FAIL”);
});
Update data
var oModel= new sap.ui.model.odata.ODataModel(‘/MyBlogProject/services/post.xsodata/’, false);
//we create an object to be passed as a JSON object
var oEntry = {};
//we add to the JSON object the values of the columns of the row we want to update
oEntry.ID = “4”;
oEntry.Date = “”;
oEntry.UserID = “1”;
oEntry.PostTitle = “Hello”;
oEntry.Tags = “hi”;
oEntry.Subject = “”;
oEntry.Likes = 0;
oEntry.Dislikes = 0;
oEntry.Views = 0;
oEntry.PostText = “World”;
//we perform the update request using the update() method of the model. Here we have to specify the “ID” of the post we want to update
oModel.update(“/post(‘4’)”, oEntry, null, function() {
alert(“SUCCESS”);
}, function() {
alert(“FAIL”);
});
Delete data
var oModel= new sap.ui.model.odata.ODataModel(‘/MyBlogProject/services/post.xsodata/’, false);
//to delete a post we use the remove() method passing the “ID” of the post to be deleted
oModel.remove(“/post(‘6’)”);
Server side JavaScript and call from ui
If the logic that can be embedded in OData is not enough, then we should consider using server-side JavaScript. With JavaScript, we will be able to call procedures and functions, and embed very complex application logic by building various JavaScript files calling each other.
In this last step, we will create a JavaScript file to call the procedure (update_post_subject) we created in the 4th part, responsible for updating the “Subject” column of a post.
To create a JavaScipt file, we go to the “services” folder of our application, we right-click and create a new “XS JavaScript file” or a new “file” adding the .xsjs extension.
updatePostSubject.xsjs
$.response.contentType = “text/HTML”;
var conn;
var pc;
//get the parameter ‘id’. Sent with the URL call
var id = $.request.parameters.get(‘id’);
try{
conn = $.db.getConnection();
//to perform call operations we use the prepare call method
pc = conn.prepareCall(“CALL \”BlogProject\”.\”MyBlogProject.procedures::update_post_subject\”(?)”);
pc.setString(1, id);
pc.execute();
conn.commit();
$.response.status = $.net.http.OK;
$.response.setBody(“Successfully updated”);
}
catch (e) {
$.response.setBody(“Failed to update” );
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
}
finally {
pc.close();
conn.close();
}
xsjs.html
<!DOCTYPEHTML><html>
<head>
<metahttp-equiv=“X-UA-Compatible”content=“IE=edge”>
<scriptsrc=“/sap/ui5/1/resources/sap-ui-core.js”id=“sap-ui-bootstrap”data-sap-ui-libs=“sap.ui.commons”data-sap-ui-theme=“sap_goldreflection”>
</script>
<script>
//first, we create a button to press and call the update
var myButton = new sap.ui.commons.Button(“btn”);
myButton.setText(“Update”);
myButton.attachPress(function(){
onUpdate();});
//the function to be called with the button press
function onUpdate() {
//we call the updatePostSubject.xsjs via ajax and we pass a parameter called id, and in this case we will update the post with id=3
var aUrl = ‘../../services/updatePostSubject.xsjs?id=3’;
jQuery.ajax({ url: aUrl,
method: ‘GET’, dataType: ‘json’,
success: function() {
alert(“Updated”);
}
});
}
myButton.placeAt(“content”);
</script>
</head>
<bodyclass=“sapUiBody” role=“application”><divid=“content”></div>
</body></html>
This completes this post and eventually the whole series. So, we have seen how to create a new XS project, how to create our persistence model, how to build our analytic data model using information views, how to embed to our application the needed data-centric application logic using text analysis and SQLScript and finally how to expose our data with OData services and server-side JavaScript for more complexity. In addition, in this post we saw small parts of the integration of the OData and JavaScript services with the ui (SAPUI5).
Thank you and enjoy!!!
Hello Panagiotis Koutsaftikis,
Can you please tell: How to update and delete using xsodata. Similarly like SAP Gateway oData (SEGW tCode). there we can Create, Read, Update & Delete also and even MIME type for attachment also can perform. How to do the same in xsodata.
update using "Traning.modelling::UPDATE_ENTRIES_IN_TABLE"
delete using "Traning.modelling::DELETE_ENTRIES_IN_TABLE"
statements are not working in xsodata.
Thanks
There are no additional syntax needed in the XSODATA to support update or delete. You get Create, Update, and Delete operations automatically. There is only syntax if you want to disable these functions.
OData Service Definitions - SAP HANA Developer Guide for SAP HANA Studio - SAP Library
By default, all entity sets and associations in an OData service are writeable, that is they can be modified with a CREATE, UPDATE, or DELETE requests. However, you can prevent the execution of a modification request by setting the appropriate keyword (create, update, or delete) with the
forbidden
option in the OData service definition.
Hello Thomas,
Thanks for quick response.
But i am able to create entries using below syntax, then why HANA is not allowing for update and delete.
create using "Traning.modelling::CREATE_ENTRIES_IN_TABLE"
Here CREATE_ENTRIES_IN_TABLE is stored procedure insert statement is in that and if it so then we can not use Stored Procedure for update or Delete in xsodata when suppose requirement is complex and records are going in different table after some calculation logic (i mean field value based on business logic)
Actually what similar kind i am looking for in HANA XSOADTA is like MIME content upload like SAP Gateway odata (SEGW tCode), There we can handle, file upload to table, multiple line items in single request kind of stuff. etc...Can you please give some references where i can get enough.
Thanks
>and if it so then we can not use Stored Procedure for update or Delete in xsodata when suppose requirement is complex and records are going
Sure you can use multiple exits for different operations. Is that what you are asking about?
>Actually what similar kind i am looking for in HANA XSOADTA is like MIME content upload like SAP Gateway odata (SEGW tCode),
There is no special processing for MIME. It can be sent as BLOB column.
>multiple line items in single request kind of stuff
For that you can use Batch mode.
Hi Panagiotis & Thomas
What will the URL(using above association) if I want the POST detail of Comment Id 'C101'.
Thanks
The "what"? What will we see?
Hi,
I am Doing update method in as like this("shema"."tableName" as "UpDateset" update using "Task_Track.XsjslibTutorial:update.xsjslib::update_instead" ;
);
but i am getting 403 Forbidden only for update.
plz help me in this
Are you using a browser or a REST client to POST the data? One thing to check is if you are using a REST client to test, and you are not passing a valid x-csrf-token, your request will be forbidden. Also check to make sure your service has CORS and POST/UPDATE enabled.
nice blog, kindly share git project url for more insights
Hello and thanks for your explication. I have a xsodata ressources, which are mostly by calculation views. What I am wondering is if it is possible to do CRUD on the calculation view exposed as xsodata ressource or it is only possible to do CRUD on a table exposed as xsodata resource?
Thanks in advance.