Content of this series:

The power of OData – Part 1 – Introduction

The power of OData – Part 2 – Setting up the Environment

The power of OData – Part 3 – Creating a data Model

The power of OData – Part 4 – Creating an OData service

      The power of OData – Part 5 – Consuming the service with SAPUI5 (this part)

So, welcome to the last part of the series, the one where we put everything together and really experience what we’ve build so far. Sorry for the delay of this post, I had to setup new hardware and to eliminate a few more “errors” within the PHP producer library. At least I think it’s an error, but we will see and correct it in this post. Furthermore, we will create a simple UI5 based web application to show the data, will enhance the look&feel and also create a mobile app for listing the data.

Let’s begin. Where are we? We should have an Apache webserver running in combination with a MySQL server. Inside the MySQL server, a table called “customer” should exist and we should have setup a webservice running at http://localhost/customer_api/Customer.svc. The query /cities should list all cities inside our database.

Now let’s look at the “error” I found. If we consume the service and don’t set any limit in our UI5 table for display, the request will be http://localhost/customer_api/Customer.svc/cities?$skip=0. If we set the property firstVisibleRow to 3, the query parameter $skip will be set to 3. With the last option, we will get results, but if we use $skip=0, an error will be returned. It took me some time to identify the coding line where this validation happens and after I added a little dirty fix, everything went well.

So start by modifying DataSerivceHost.php. The file is located in your customer_api folder. Go to library\ODataProducer\OperationContext.

/wp-content/uploads/2014/06/1_474762.png

The function validateQueryParameters has to be modified here so that our requests will work. To do so, go to line 370 and add the following after “$optionValue = current($queryOption)” and before “if (empty($optionName))”:


if($optionName == '$skip' && $optionValue == 0)                                                            //Added for skip OData Requests
{
                $optionValue = 1;
}

/wp-content/uploads/2014/06/2_474763.png

Save and close the file.

The next issue I faced when testing and developing the sample applications for this part was about cross-origin resource sharing (cors in short). This is mainly an issue because of our development environment. Request across different domains aren’t allowed in general and we might not receive any data if we try to do so. Looking at the service, we are using port 80 (Apache’s standard). When we test in Eclipse, we get a port like 54874 where our UI5 app is running. If we use Tomcat with Eclipse for testing, we have 8080. Although we use localhost, everything is seen as a different domain because of the different ports. SAP provides a simple Proxy solution here (https://help.sap.com/saphelp_nw74/helpdata/de/2d/3f5fb63a2f4090942375df80abc39f/content.htm) but this didn’t work for me. So for the demo environment, I adjusted my .htaccess like this:


Header set Access-Control-Allow-Origin "*"
Header set Access-Control-Allow-Headers "MaxDataServiceVersion, DataServiceVersion"

This allows access from other domains and also header values like the ones used for OData.  After those adjustments, we can now start with the development. Start up your eclipse instance, I assume you all have the UI5 development tools installed.

Simple Project

Create a new project via File -> New -> Project

/wp-content/uploads/2014/06/3_474764.png

Choose SAP UI5 Application Development and continue:

/wp-content/uploads/2014/06/4_474765.png

Let’s use customer_simple as project name here. As library, we choose “sap.ui.commons” as we will create a simple desktop application.

/wp-content/uploads/2014/06/5_474766.png

Name your view (I choose mainView) and simply click finish:

/wp-content/uploads/2014/06/6_474767.png

At this simple step, we don’t have to touch index.html much. Just add a new library. In the script section of the generated index.html, we have to tell the lib that we want to use a table. So add sap.ui.table here:

/wp-content/uploads/2014/06/js_474768.png

That’s it. Let’s start consuming the service. Close index.html and open up your controller file. If you took my naming, this should be mainView.controller.js. I like to follow the MVC approach and try to store much logic inside my controllers and use the views really for building the UI. So uncomment the onInit function at the top and add the following code:


var sServiceUrl = "http://localhost:80/customer_api/Customer.svc";
var oModel = new sap.ui.model.odata.ODataModel(sServiceUrl, false);
           

sap.ui.getCore().setModel(oModel);

It should look like this now:

/wp-content/uploads/2014/06/js2_474770.png

Here, we declare a serviceUrl pointing to our OData service. Additionally, we create an OData model and set this model for our application.

Let’s go to the main view. Here is where the binding and UI happens. Delete anything within the createContent function. We will add our own content here. We will create two tables. The first will display all countries from our database; the second will display the cities that belong to a chosen country.

Here is the code, I will explain it afterwards:


var oCountryTable = new sap.ui.table.Table({
title: "Countries",
      width : "100%",
      selectionMode : sap.ui.table.SelectionMode.Single
});

oCountryTable.addColumn(new
      label: new sap.ui.commons.Label({text: "Country ID"}),
      template: new sap.ui.commons.TextView().bindProperty("text", "countryID"),
      sortProperty: "countryID",
      filterProperty: "countryID",
      width: "50px"
}));

oCountryTable.addColumn(new
      label: new sap.ui.commons.Label({text: "Country Name"}),
      template: new sap.ui.commons.TextView().bindProperty("text", "country"),
      sortProperty: "country",
      filterProperty: "country",
      width: "200px"
}));

oCountryTable.bindRows("/countries");
oCountryTable.placeAt("content");

First we create a new table here, give it a title and width and say that we can select single lines. After that we add three columns and assign a label, a template and some other properties. Most important here is the bind option in the template. We bind to the fieldname we have assigned in our service. Remember that the producer build the PHP files depending on the metadata of the database? Here we refer to the fields or our metadata description. After defining the columns we bind the rows of the table to your collection. In out controller, we specified only the service URL ending with Customer.svc. Here we finally bind to the collection, in this case “countries”. And this will result in the above mentioned link like Cusomer.svc/countries?$skip=0. Finally, we place the table in the content div of our index.html.

Let’s create another table, but this time, we don’t bind and place it yet:


var oCityTable = new sap.ui.table.Table({
      title: "Cities",
      width : "100%",
      selectionMode : sap.ui.table.SelectionMode.None
});

oCityTable.addColumn(new
      label: new sap.ui.commons.Label({text: "City ID"}),
      template: new sap.ui.commons.TextView().bindProperty("text", "cityID"),
      sortProperty: "cityID",
      filterProperty: "cityID",
      width: "50px"
}));

oCityTable.addColumn(new
      label: new sap.ui.commons.Label({text: "City Name"}),
      template: new sap.ui.commons.TextView().bindProperty("text", "city"),
      sortProperty: "city",
      filterProperty: "city",
      width: "200px"
}));

This should be straight forward as it is the same as above. We will now add a rowSelection event that is fired whenever we select a new row. In this event, we build a new query that will run against our service and fetch all the cities depending to a country.



oCountryTable.attachRowSelectionChange(function
      selectedRowContext = event.getParameter("rowContext"),
      selectedCityLineItems = selectedRowContext + "/cities";
      oCityTable.bindRows(selectedCityLineItems);
});

The selected row context will get the current row and the ID of the country. We add /cities as we navigate to a city from the country. The query will look like this: Customer.svc/countries(countryID=1)/cities

So UI5 here creates well-formed correct OData queries and the best, our custom service can understand those queries and return the results.

We bound the newly selected rows above, let’s place it on the screen. To do this, I’ve copied the content div in index.html and named the new one detail:

js 3.png

So that I can use oCityTable.placeAt(“detail”); to show it.

If you run in Eclipse, the following should be seen:

/wp-content/uploads/2014/06/8_474782.png

Mobile Demo

The mobile version is almost the same as the simple one. Create a new project and choose sap.m as library this time. After the project is created, we don’t have to add anything to our index.html this time, as we are completely using the mobile libraries. The controller looks completely the same as in the simple example; therefore, I will only present the view here:

My createContent function looks like this:


var oTable = new sap.m.Table({
      mode: sap.m.ListMode.None,
columns: [
      new sap.m.Column({
              header : new sap.m.Label({
                    text : "City ID"
              })
      }),
      new sap.m.Column({
              header : new sap.m.Label({
                    text : "City"
              })
      })]
});
   

oTable.bindItems("/cities"new
cells : [
              new sap.m.Text({
                    text : "{cityID}"
              }),
              new sap.m.Text({
                    text : "{city}"
      })]
}));
var oPage = new sap.m.Page("Title");

oPage.addContent(oTable);
return oPage;

Mobile is a bit different here. The table has a columns collection, which will only store the description of the columns, like their header. After creating a mobile table, we have to bind the items. In this function, we specify the path, here /cities, and then define a ColumnListItem that represents the cells that are shown. In each cell, we bind an item like in the columns for sap.ui.table.

After binding, we add the table to a page object and return this for display.

/wp-content/uploads/2014/06/9_474784.png

I’ve included a more complex example as zipped Eclipse project as an attachment here, if anyone might be interested, just replace txt by zip. This example uses a shell for a better UI and displays all tables in different panes as shown here:

/wp-content/uploads/2014/06/10_474785.png

I hope you enjoyed this blog series and have many good ideas on how to combine different technologies and enrich your data with SAP data or vice versa. I’d be glad if you leave me some comments, queries, suggestions or similar things about the series.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Paulo Cesar de Biasi Vantini

    Hi Marc,

    Good to come to this final part of your OData series! 🙂

    I need your help again, unfortunately it seems like I could not bind the table to the div.

    I followed your steps, but the output for http://localhost:54158/customer_simple/index.html has no ui5 table .

    Where do you place the line: oCityTable.placeAt(“detail”); ? in the script of index.html or in the mainView.view.js ?

    Also, the changes for .htaccess file is for the customer_api folder, right?

    Maybe I am facing the second issue you descrided about the proxy settings for odata 🙁

    If you can send me your project files again it would help me solve it a lot faster.

    Below is my createContent code:

    createContent : function(oController) {

      var oCountryTable = new sap.ui.table.Table({ 

      title: “Countries”, 

           width : “100%”,  

           selectionMode : sap.ui.table.SelectionMode.Single  

      });

      oCountryTable.addColumn(new {

           label: new sap.ui.commons.Label({text: “Country ID”}), 

           template: new sap.ui.commons.TextView().bindProperty(“text”, “countryID”), 

           sortProperty: “countryID”, 

           filterProperty: “countryID”, 

           width: “50px” 

      }); 

      

      oCountryTable.addColumn(new {

           label: new sap.ui.commons.Label({text: “Country Name”}), 

           template: new sap.ui.commons.TextView().bindProperty(“text”, “country”), 

           sortProperty: “country”, 

           filterProperty: “country”, 

           width: “200px” 

      }); 

      

      oCountryTable.bindRows(“/countries”); 

      oCountryTable.placeAt(“content”); 

      var oCityTable = new sap.ui.table.Table({ 

           title: “Cities”, 

           width : “100%”,  

           selectionMode : sap.ui.table.SelectionMode.None  

      }); 

      

      oCityTable.addColumn(new { 

           label: new sap.ui.commons.Label({text: “City ID”}), 

           template: new sap.ui.commons.TextView().bindProperty(“text”, “cityID”), 

           sortProperty: “cityID”, 

           filterProperty: “cityID”, 

           width: “50px” 

      }); 

      

      oCityTable.addColumn(new {

           label: new sap.ui.commons.Label({text: “City Name”}), 

           template: new sap.ui.commons.TextView().bindProperty(“text”, “city”), 

           sortProperty: “city”, 

           filterProperty: “city”, 

           width: “200px” 

      });

      oCountryTable.attachRowSelectionChange(function () {

           selectedRowContext = event.getParameter(“rowContext”), 

           selectedCityLineItems = selectedRowContext + “/cities”; 

           oCityTable.bindRows(selectedCityLineItems);       

      });

      oCityTable.placeAt(“detail”);

      }

    (0) 
    1. Marc Augustin Post author

      Hi Paulo,

      glad you enjoy the posts 🙂

      I place the table in my mainView.js, the additions are, as you say, to my .htaccess of the service.

      You can also try out Fiddler when testing those applications. I read about it in some post here on SCN, but don’t remember it, so I can’t link to it, sorry. Fiddler let’s you trace and analyse your http requests and responses very detailed. There is a stand-alone version for IE and a Chrome extension, which I am using. With that, you are alse able to see the requests that your application will send to your service and the responses. This way I was able to see the “error” in the producer scripts, as they don’t actually show any error but only a blank table.

      Additioanlly, the developer tools in Chrome are really good for looking at errors in your script. Open them with F12 on Windows and go through the sources. If there are any runtime errors, Chrome will mark the line with some red comments.

      I’ll send you all of my 3 projects and zip you my customer_api again, so that you can have a look.

      Marc

      (0) 
      1. Paulo Cesar de Biasi Vantini

        Hey Marc,

        it worked perfectly! You rocked again like germany did today in WC! 🙂

        The problem was my createcontent code and my .htaccess file was wrong too.

        I missed some of your details and attachRowSelectionChange was not working properly.

        I did not know about Fiddler but I´ll try to find don´t worry. I just use simple curl commands to retrieve requests and responses. I knew about chrome tools but I´m not used to it. I´ll try to study it a little bit more.

        Brother, I am amazed by your skills and I am very grateful for everything you did to me!

        You can count on me for whatever you need. Friends like you makes life happier!

        Hope you can keep on sharing awesome content like these tutorials with practical samples you´ve made.

        All the best for you!

        /wp-content/uploads/2014/06/odataui5_476835.png

        (0) 
        1. Marc Augustin Post author

          Hey Paulo,

          I’m glad that it works now 🙂 Well, I didn’t watch yesterday, but must have been a good game, according to the score.

          That’s the community feeling I like about everything here. Turning work into something like this is always amazing.

          (0) 
    1. Marc Augustin Post author

      Thanks Jun,

      although it is now more a tutorial and example, I initially wanted to share how powerful the new technologies and tools are, especially in combination with OData. It’s now so easy to combine multiple different sources of information into one single application 🙂

      Marc

      (0) 

Leave a Reply