Skip to Content
Technical Articles
Author's profile photo Thomas Salvador

How to consume an OData Service with OpenUI5 / SAPUI5?

OpenUI5 is the open source version of SAPUI5. It is an UI development toolkit for HTML5. See http://openui5.org/ or https://blogs.sap.com/2013/12/11/what-is-openui5-sapui5/ for more details.

UI5 provides the sap.ui.model.odata.ODataModel. The model can be used via data binding or via the API.

The API supports synchronous and asynchronous processing. The asynchronous functions return an oRequest object that provides an abort function to abort the according request.

In addition it is possible to work directly with the model and collect changes to submit them together.

Create

oModel.create("/YourCollection", oData, {
  success: function(oCreatedEntry) { /* do something */ },
  error: function(oError) { /* do something */ }
);

Create always returns the topmost created node. If a hierarchy was created and shall be processed further, you can read it with $expand.

oModel.create("/YourCollection", oData, {
  success: function(oCreatedEntry) {
    oModel.read("/YourCollection('"
      + oCreatedEntry.ObjectID+"')/?$expand=YourChild", {
      success: function(oCompleteEntry) { /* do something */ },
      error: function(oError) { /* do something */ }
    });
  }, 
  error: function(oError) { /* do something */ }
});

The success callback is supplied with the created ROOT (e.g. Employee) node. Its ObjectID value is used to formulate a read request of the very same ROOT node (e.g. Root node of Employee BO), expanding to include the COMMON nodes (e.g. EmployeeCommon) as well. Using EmployeeRoot and EmployeeCommon it would be formulated like:

oModel.create("/EmployeeCollection", oData, {
  success: function(oCreatedEntry) {
    oModel.read("/EmployeeCollection('"
      + oCreatedEntry.ObjectID+"')/?$expand=EmployeeCommon", {
      success: function(oCompleteEntry) { /* do something */ },
      error: function(oError) { /* do something */ }
    });
  }, 
  error: function(oError) { /* do something */ }
});

An alternative would be to directly read the COMMON nodes that have the created ROOT as parent:

oModel.create("/EmployeeCollection", oData, {
  success: function(oCreatedEntry) {
    oModel.read("/EmployeeCommonCollection/?"+
      "$filter=ParentObjectID eq '"+oCreatedEntry.ObjectID+"'", {
      success: function(oChildEntry) { /* do something */ },
      error: function(oChildError) { /* do something */ }
    });
  },
  error: function(oError) { /* do something */ }
});

Retrieve

The read function can be used to retrieve multiple instances:

oModel.read("/YourCollection...", {
  success: function(oRetrievedResult) { /* do something */ },
  error: function(oError) { /* do something */ }
});

or just one

oModel.read("/YourCollection('...')", {
  success: function(oRetrievedResult) { /* do something */ },
  error: function(oError) { /* do something */ }
});

Update

There are two types of updates, MERGE and PUT:

MERGE is the more frequently used and updates only the stated properties with the new values.

oModel.update("/YourCollection('...')", oData, {
  merge: true, /* if set to true: PATCH/MERGE */
  success: function() { /* do something */ },
  error: function(oError) { /* do something */ }
});

PUT replaces the node data, sets the stated properties as given and reverts all others to there default values.

oModel.update("/YourCollection('...')", oData, {
  success: function() { /* do something */ },
  error: function(oError) { /* do something */ }
});

OpenUI5 can directly submit changes, or collect them to then submit all model changes together.

This allows you to minimize requests and model refreshes. In addition, the model has batch support to bundle operations.

As an update is a Business Object Modify, other node values or nodes might change as a consequence.

oModel.submitChanges(
  function() { /* success: do something */ },
  function(oError) { /* error: do something */ }
);

Delete

To delete an Business Object instance, we use a remove function call on the instance to delete:

oModel.remove("/YourCollection('...')", {
  success: function() { /* do something */ },
  error: function(oError) { /* do something */ }
});

Function Imports

oModel.callFunction("yourfunctionimport",
  "POST", /* or PATCH or POST or GET or DELETE */
  {"parameter1" : "value1" },
  null,
  function(oData, oResponse) { /* do something */ },
  function(oError){ /* do something */ }
);

While callFunction clearly indicates, that a Function Import is called, it is also possible to use other functions to access them as long as the used HTTP method matches.

For example, a query could be triggered with read (Method GET).

oModel.read("/your_query?firstname='John'&lastname='Doe'", {
   success: function(oQueryResult) { /* do something */ }
});

Security

OpenUI5 provides protection against cross site request forgery (CSRF) out of the box. The token is fetched and used automatically. You can get the current token with

var sToken = oModel.getSecurityToken();

You can request a new token at any time, for example before mass updates.

oModel.refreshSecurityToken(function() {
  window.alert('Successfully retrieved CSRF Token: ' 
    + oModel.oHeaders['x-csrf-token']); 
}, function() {
  window.alert('Error retrieving CSRF Token');
}, false);

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Emmanuel Dacosta
      Emmanuel Dacosta

      Hello

      Great blog.

      I have a question about ByD Odata and SAP CP Web Ide.

      Is it possible to create a web application using the web Ide templates or it is mandatory to create it from scratch ?

      Regards

      Emmanuel

       

       

      Author's profile photo Thomas Salvador
      Thomas Salvador
      Blog Post Author

      Hi Emmanuel.

      I think, that the model might be the only things that would change. Hence I do not see a reason to not use the templates. I would think, you can create from there and edit the model afterwards.

      Thanks and regards,

      Thomas Salvador.

      Author's profile photo Naoto Amari
      Naoto Amari

      Hi Thomas Salvador thanks! very well explained, but i have i doubt, what if i want to consult this /sap/opu/odata/sap/ZERP_ACTIVIDADES_SRV/TileInfoSet(‘10002122’)

      how i would do it ? be cause i did this

      	        var x = 10002122;
                      var sURI = "/sap/opu/odata/SAP/ZERP_ACTIVIDADES_SRV";
          		var oDataModel = new ODataModel(sURI, true);
          		var oModel = new sap.ui.model.json.JSONModel();
          		var oFilter = new Filter("Asesor", "EQ", x);
          		oDataModel.read("/TileInfoSet", {
          			filters: [x],
          			success: function(oData, response) {
          	        var oResults = oData.results;
          	        oModel.setData(oData.results);  	        
          			 }

      but i don’t know if it is ok be cause it’s an get_entity not entityset so i a little confused

       

       

      Author's profile photo Thomas Salvador
      Thomas Salvador
      Blog Post Author

      Hi Naoto.

      I am not sure, that i get your question. To access the single entity, you would provide this as URL:

      oDataModel.read("/TileInfoSet(10002122)", {

      Did this not work?

       

      Thanks and regards,

      Thomas.

      Author's profile photo Rakan Harb
      Rakan Harb

      Hi there,

      I’ve tried to use the following code:

      var FirmaModel = new sap.ui.model.odata.ODataModel("/xsodata/Firma.xsodata", true);
      	FirmaModel.setHeaders({
      		"content-type": "application/json;charset=utf-8"
      	});
      	FirmaModel.create("/firma", {
      			FirmaName: "test", 
      			Bezeichnung: "Hallo" 
      		}, {
      		  success: function(response) { console.dir(response);  },
      		  error: function(response) { console.dir(response);  }
      		}
      	);
      }

      But I do always recieve the following error-message:

      {"error":{"code":500,"message":{"lang":"en-US","value":"Error while executing a DB query"}}}

      Any ideas?

      Is something in your example missing? Like how have you configured your xsodata-service?

      Update, Delete and Read do work as expected.

      Kind regards

       

      Rakan 

      Author's profile photo Thomas Salvador
      Thomas Salvador
      Blog Post Author

      Hi Rakan.

      I am not talking about any specific realization, like XS OData. But OData in general. So, the examples should be complete.

      As the error message more sounds that the request causes an issue in the backend, have you tried to create an instance of Firma directly with the attributes specified.

      Could it be, something else is mandatory, not yet specified, or of another type?

      E.g. I could imagine, that the entries have to have some unique id identifying the firma, defaulted to empty (or something or not at all) if not specified, and causing a database insertion issue as consequence.

      Maybe you could try to pinpoint what exactly the reported 'Error' is, e.g. what the used service expects for creation.

      Thanks and regards,

      Thomas Salvador.

      Author's profile photo Marc Roesner
      Marc Roesner

      Hi Salvador,

      as I'm a colleague of Rakan and I know the context, I will answer the questions.

      "(....) As the error message more sounds that the request causes an issue in the backend, have you tried to create an instance of Firma directly with the attributes specified.(....)"

      Yeah, we've already done several db-wise inserts via SQLScript. That works like expected.

      We have got the theory that the problem is caused by the (auto-incremented) id-field of our target-table. It seems like the mapping between the data of the sending system (the Webapp) the service and the target table don't work. And even if it works, it's not possible to insert any ID-value in the auto-incremented field. That would explain the db-error.

       

      Examples: 

      Case 1: Input-Object vs. target-table with FirmaID 

      Input => Target

      Input-Object: 
      { 
       FirmaID: 1, 
       FirmaName: "example", 
       Bezeichnung: "test"
      } 
      
      Target-Table:  
      FirmaID Integer Identity 
      FirmaName String 
      Bezeichnung String 
      
      Mapping: 
      FirmaID => FirmaID 
      FirmaName => FirmaName 
      Bezeichnung => Bezeichnung 
      
      Result: Crashes because FirmaID does not expect an input value, as it is automatically generated (like you wouldn't add an id value onto an auto incremented field. that would be logically explanationable). 
      
      

      Case 2: Input-Object vs. target-table without ID 

      Input => Target

      Input-Object: 
      { 
       FirmaName: "example", 
       Bezeichnung: "test" 
      } 
      
      Target-table: 
      FirmaID Integer Identity 
      FirmaName String 
      Bezeichnung String 
      
      Mapping: 
      FirmaName => FirmaID 
      Bezeichnung => FirmaName 
      empty => Bezeichnung 
      
      Result: 
      Crashes because a String cannot be inserted into an integer-field and the id-field generated values on it's own as it's an identity-field. 

      Just as a side-note: If we remove the ID-column from the tables definition completely, the insert does work. I think that this was the way you did it in your blogpost? If the id field is then readded, it stops working and throws the mentioned error message.

      We've also tried to add a stored procedure to the service - still the same error occurs (even if the stored procedure is completely empty). The idea was simply to do the mapping manually in the procedure.

      Do you have any idea about access management in hana xs? Is it needed to configure the system that procedures are allowed in the odata-services?

      The Code: 

      Service:

      service {
          "Orga.firma" as "firma" 
          create using "RH_Orga_1.Orga_DB.procedure::setDBEntry"; 
      } 

      Stored Procedure:

      PROCEDURE "DB_1::setDBEntry" ()
       LANGUAGE SQLSCRIPT SQL
       SECURITY INVOKER AS
       --READS SQL DATA AS
      BEGIN
       -- This is just a test 
      END;

      This empty procedure crashes. A procedures with input and output parameters has been tested aswell and .... crashes. It even crashes if we remove the id field. In contrast to the create / insert WITHOUT the id field, a service using a procedure crashes every time - even if it has been tested via SQLScript.

      Of course it might be the case that the auto-incrementation is not of interest in your post, but maybe you know an explanation for this behaviour.

       

      Kind regards

       

      Marc

      Author's profile photo Thomas Salvador
      Thomas Salvador
      Blog Post Author

      Hi Marc.

      Thanks for the details. Indeed, this is not in scope of the blog post.

      Have you tried to supply a value to FirmaID, that it won't try to really set, but understand that this is just a placeholder. Maybe you could try to supply

      {
        FirmaID: null, // or false or maybe 0
        FirmaName: "test", 
        Bezeichnung: "Hallo" 
      }

      so that it might understand to

      • map FirmaID to FirmaID (and not FirmaName, interesting, that it is mapping by sequence and not by name) on one hand, but to
      • not supply a value on the other, but let it trigger the auto increment.

      Besides that maybe the XS colleagues could tell, how to provide a fitting payload to your case, or an example. Maybe one can set the explicit mapping or that it has to go by names/key.

      The mapping by sequence is not that helpful in my eyes, but if this is the only way, maybe you can try reordering the target table to have the auto increment at the very end. Because then execution would be

      Input-Object: 
      { 
       FirmaName: "example", 
       Bezeichnung: "test" 
      } 
      
      Target-table: 
      FirmaName String 
      Bezeichnung String 
      FirmaID Integer Identity 
      
      Mapping: 
      FirmaName => FirmaName
      Bezeichnung => Bezeichnung
      empty => FirmaID 

      which might work, as it does supply nothing to FirmaID, neither an unexpected value nor an unexpected type.

      Thanks and regards,

      Thomas Salvador.

      Author's profile photo Marc Roesner
      Marc Roesner

      Hi Salvador,

      thank you very much for your fast reply.

      (….) Have you tried to supply a value to FirmaID, that it won’t try to really set, but understand that this is just a placeholder. Maybe you could try to supply (….)

      Unfortunately we’ve already tried both of your ideas. We tried to insert an FirmaID value of “null” just like the example you’ve posted. This has caused the same db-error. I’ve also thought that inverting the db scheme and putting the id parameter as the last column would solve the issue, but unfortunately that was not the case.

      It seems like the only way is to do explicit mapping is via xsjs or a stored procedure, but – as I’ve already set – not even an empty procedure can be executed on our install. That’s why I think it might be a problem with the HANA XSA settings and not with our code, but I have no idea.

      We’ve found an implementation of the uuid for Javascript (RFC: https://tools.ietf.org/html/rfc4122). As we unfortunately cannot use the auto-incrementation of the SAP HANA Database (and also not the server side uuid functionality) this seems to be the easiest solution for very simple database models.

      Unfortunately it would be of course better if we find out how to get the stored procedures to work. According to your last post, I don’t think that you have an idea or a link we can check on the problem described above?

      Thank you very much for your support.

      Kind regards

       

      Marc

      Author's profile photo Thomas Salvador
      Thomas Salvador
      Blog Post Author

      Hi Marc.

      Thanks for the details.

      No, unfortunately I do not know this.

      However, the stated names are just examples, right? I would assume that called procedure and defined one to have the same name.

      Also, can a procedure really be empty if it is supposed to create the entry? What would be the expected result. I mean, it is called/executed during creation and not doing anything puts nothing in the store.

      However, I am not sure, if this this more for transiently providing data, then put to DB afterwards, or if it indeed should put it there itself.

      It should be better to check the XS related blogs out for this.

      Thanks and regards,

      Thomas Salvador.

      Author's profile photo Marc Roesner
      Marc Roesner

      Hi Salvador,

      thank you for your reply.

      "(....) However, the stated names are just examples, right? I would assume that called procedure and defined one to have the same name. (....)

      The procedure and the one I'm calling are having the same names. I even have recreated the basic part of the project step by step and it still does not work (sources I've worked with: https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.00/en-US/81ccb259f8564776a4bcf0bb479dcc2e.html and some step-by-step workthrough on setting up a hana/SAPUI5-project. Unfortunately I cannot find the link).

      "(....) Also, can a procedure really be empty if it is supposed to create the entry? What would be the expected result. I mean, it is called/executed during creation and not doing anything puts nothing in the store. (....)"

      I've also tried it with a simple insert like:

      INSERT INTO <table> ( 
       <field 1..n> 
      ) VALUES ( 
       <value 1..n> 
      ) 

      Even a simple insert with "ID" = 1 did not work (only if I've called the procedure via the SQLScript-Console).

      I came to the conclusion that I do maybe not have the necessary rights to attach procedures to odata-services. I've found out that procedures are "read only" on default (I don't know if that only counts if they're called via an odata-service). Only a view tutorials even mention the role of access-right in hana-db here.

      "(....) However, I am not sure, if this this more for transiently providing data, then put to DB afterwards, or if it indeed should put it there itself. (....)"

      I've also thought about that. It might be possible to maybe use a Trigger database wise to move data from an input-table to an internal target table on insert. I still think this is very strange and I don't think that this would even work. I guess the effect would be the same error as already mentioned in the previous texts.

      "(....) I should be better to check the XS related blogs out for this. (....)"

      If implemented in XSJS, the logic also doesn't work as it's not even beeing executed. It fails with the same error.

      Kind regards

      Marc

      Author's profile photo Marc Roesner
      Marc Roesner

      Hi Salvador,

      I’ve got big news to share. Maybe that won’t be nothing new to many experienced HANA-Developers, but as I do really hate conversations in threads, where the result is kept as a secret and the conversation just stops, I’m going to post our solution here.

       

      There are problems with executing DB-Procedures in ODATA-Services (maybe) due to security-configurations 

      “I don’t know why it would have worked the other day; but generally you do need to make system configuration to allow non-read-only procedures in the repository.  This can be done from the Admin Console. Choose the Configuration tab. Then navigate to indexserver.ini ->Repository.  You need a parameter named sqlscript_mode and the value should be set to UNSECURE.”

      — Thomas Jung: https://answers.sap.com/questions/10227572/index.html

      As you can see, your SAP coworker Thomas Jung, who is – as far as I know – a HANA developer has said that for procedures to perform writes, the sqlscript_mode should be set to “unsecure” in an indexserver.ini. Unfortunately I did not find a complete description on how to activate the Admin Console in the WebIDE to be able to modify that. Unfortunately I wasn’t able to find a source stating that this only counts for procedures called by odata services neither. so it’s just my theory that, if a webapplication calls the hana database via an odata service, the calling system does not have access to the procedure unlike the database manager in the database console.

      As someone who has worked as a database admin in the past, I would have appreciated if stored procedures would have been best practice here. Unfortunately that does not seem to be the case because of the trend in web development to use orms and to do everything database related based on javascript and database connectors (it’s not bad though – for me it's okay).

      The final solution was indeed XSJS 

      To solve this depressing issue, I’ve coded a small xsjs insert-script as a part of a xsjslib(rary). Here is the final (working) result:

      function setFirma(inputData) { 
      	/* 
      		@param inputData Object 			   - Contains all the data of the request like the input-parameters 
      												 as well as information about the temporary table where the 
      												 incomming data has been stored to db wise 
      												 example for such a tempory table: #NAME_OF_TABLE 
      		@param temporaryDBTableWithData String - Contains the name of the temporary database table 
      												 Where the data send by the client has been stored to 
      		@param databaseStatement String		   - Contains the database statement 
      		@param FirmaName, Bezeichnung String   - Data set to the http-body of the client-request 
      		@param request Object				   - Contains the data of the temporary table 
      		
      	*/ 
      	var temporaryDBTableWithData = inputData.afterTableName;
          var databaseStatement, 
          	FirmaName,
          	Bezeichnung, 
          	request; 
          try {
              databaseStatement = inputData.connection.prepareStatement('select * from "' + temporaryDBTableWithData + '"');
              request = databaseStatement.executeQuery();
              while (request.next()) {
                  FirmaName = request.getString(2);
                  Bezeichnung = request.getString(3); 
          	}
              databaseStatement = inputData.connection.prepareStatement("insert into \"DB_1\".\"Orga.Firma2\" ( \"FirmaName\", \"Bezeichnung\") values ('"+ FirmaName + "', '" + Bezeichnung + "');");
              databaseStatement.executeUpdate();
              databaseStatement.close();
          } catch (e) {
          	databaseStatement.close();
          }
      }

      Here is the description of the odata service:

      service {
          "Orga.firma2" as "firma2" 
          create using "xsjs:setFirma.xsjslib::setFirma"; 
      } 

      Where “Orga.firma2” is the name of the database table that has been created based on the definiton of the “.hdbcds”-file.

      Scheme: <context>.<table>

      The xsjs-script is part of the xsjs-library. The function that is executed in this script is “setFirma”.

      Scheme: <xsjs folder>:<xsjs-library>.xsjslib::<function>

      I hope this helps others reading this conversation.

       

      Thank you very much for your ideas, Salvador.

       

      Kind regards

       

      Marc