Introduction to HANA XS application development (Part 5): BlogProject exposing data with OData services and server-side JavaScript, and performing CRUD operations from ui

[Former Member]

Hello everyone,

Catch up with the previous posts of the series:

http://scn.sap.com/community/developer-center/hana/blog/2014/05/27/introduction-to-hana-xs-applicati...

http://scn.sap.com/community/developer-center/hana/blog/2014/05/27/introduction-to-hana-xs-applicati...

http://scn.sap.com/community/developer-center/hana/blog/2014/06/05/introduction-to-hana-xs-applicati...

http://scn.sap.com/community/developer-center/hana/blog/2014/06/11/introduction-to-hana-xs-applicati...

In this 5^th 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 2^nd 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.

http://10.67.66.156:8000/MyBlogProject/services/Calc_views.xsodata/Posts?$format=json&$orderby=UserI...

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!!!