Technical Articles
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);
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
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.
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
Hi Naoto.
I am not sure, that i get your question. To access the single entity, you would provide this as URL:
Did this not work?
Thanks and regards,
Thomas.
Hi there,
I’ve tried to use the following code:
But I do always recieve the following error-message:
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
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.
Hi Salvador,
as I'm a colleague of Rakan and I know the context, I will answer the questions.
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
Case 2: Input-Object vs. target-table without ID
Input => Target
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:
Stored Procedure:
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
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
so that it might understand to
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
which might work, as it does supply nothing to FirmaID, neither an unexpected value nor an unexpected type.
Thanks and regards,
Thomas Salvador.
Hi Salvador,
thank you very much for your fast reply.
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
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.
Hi Salvador,
thank you for your reply.
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).
I've also tried it with a simple insert like:
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.
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.
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
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
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:
Here is the description of the odata service:
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