Skip to Content
Technical Articles

Create Record with sequence ID and return the ID in SAPUI5 with xsoData and xsjsLib

This is an example to show, how to create a record in SAP HANA table with the sequence ID, using the xsodata and xsjslib exit methods while creating a record. The code written is in eclipse . This blog will help you to return back the ID in the frontend created with SAPUI5 application.

In this blog we are going to explore the concept of

  • Creating Xsodata with xsjslib exit method.
  • Creating a xsjslib function to create a record with back end sequence number.
  • Creating session.xsjslib to call in the exit function.
  • Sample SAPUI function to create and return the sequence ID.

 

 

Following are the steps to achieve the above-mentioned goal.

 

  • Create a Repository workspace and start the XS project

  • The XS project structure looks like this from the project explorer

 

Create a xsoData  file in the SAP HANA development perspective, in this case, the file name is staffinfo.xsodata in the services folder show in the figure above.

 

Code for the staffinfo.xsodata file

service namespace "sap.hana.democotent.emp"
{
"DEMOIKEMPLOYEE"."EMPLOYEE" as "STAFF";
"DEMOIKEMPLOYEE"."DEPT" as "DEPARTMENT";
"DEMOIKEMPLOYEE"."STAFFDEPT" as "EDepartment" create using "demoikpackage.demo3Prj:validateCreateDept.xsjslib::createAndReturnSeqNo";
}

 

Here we are using the xsjslib (“demoikpackage.demo3Prj:validateCreateDept.xsjslib::createAndReturnSeqNo”) while creating the record in the HANA table  “DEMOIKEMPLOYEE”.”STAFFDEPT”.

demoikpackage.demo3Prj is the package path

validateCreateDept.xsjslib is the xsjslib file name and createAndReturnSeqNo is the function which will be called to create the record, using the sequence generator created in SAP HANA system.

 

Code for the validateCreateDept.xsjslib file:

 

$.import("demoikpackage.demo3Prj.session.xsjslib","session");

var SESSIONINFO = $.demoikpackage.demo3Prj.session;

function createAndReturnSeqNo(param)
{
let after = param.afterTableName;
var pStatment = param.connection.prepareStatement('select * from "'+after+'"');
var user =SESSIONINFO.recordSetToJSON(pStatment.executeQuery(),'Details');
pStatment.close();
if(user.Details[0].DEPTNAME =='')
{
throw ('Dept Name cannot be blank');
}
pStatment = param.connection.prepareStatement('SELECT "DEMOIKEMPLOYEE"."DEPTIDSEQUENCE".NEXTVAL  FROM DUMMY');
var rs = pStatment.executeQuery();
var deptId ='';
while (rs.next()) {
deptId = rs.getString(1);
}
pStatment.close();
for (var i = 0; i < 2; i++)
{
if(i<1)
{
pStatment = param.connection.prepareStatement('insert into "DEMOIKEMPLOYEE"."STAFFDEPT"("DEPTID","DEPTNAME") values (?, ?)');
}
else
{
pStatment = param.connection.prepareStatement('TRUNCATE TABLE "'+after+'"');
pStatment.executeUpdate();
pStatment.close();
pStatment = param.connection.prepareStatement('insert into"'+after+'"values (?, ?)');
}
pStatment.setString(1,deptId);
pStatment.setString(2,user.Details[0].DEPTNAME);
pStatment.executeUpdate();
pStatment.close();
}
}

I have created a session.xsjslib which is imported the validateCreateDept.xsjslib file, you can see that is the screenshots provided in the document, you can download the tz file from shine for this file, but, I had some issues, for which, Created the session.xsjslib in my project ;

Copy the Code for the session.xsjslib file from this link:

https://github.com/SAP/hana-shine-xsa/blob/master/core-xsjs/lib/sap/hana/democontent/epm/services/session.xsjslib

Call the xsodata from the SAPUI5 application to create a record in HANA table with the DEPTID and DEPTNAME , DEPTID will be populated with the number used from the sequence generator.

Code for the SAPUI5 function which will create the record, the success function will return the ID (newDeptId = response.data.DEPTID) created from the Sequence.

 

createDepartmentEntry: function () {
var self = this;
var oDataEntry = {};
oDataEntry.DEPTID = 999;
oDataEntry.DEPTNAME = this.getView().byId("IdDeptName").getValue();
this.getView().getModel("myModel").create("/EDepartment", oDataEntry, {
success: function (oData, response) {
newDeptId = response.data.DEPTID;
self.onApproveDialog();
},
error: function (oError) {
MessageToast.show("Create unSuccessfull");
}
});
}

 

After completing the steps listed above, you will be able to create a record with sequence id and return the value in the SAPUI5 application.

 

Thanks

Mohammed Ikram.

1 Comment
You must be Logged on to comment or reply to a post.
  • Hi Mohammed,

    Thank you very much for sharing your experience. Your genius combination of ‘truncate + insert’ works perfectly on the after table!

    I’ve initially tried to update the after table directly with a new sequence id in order to receive it in the response, but the runtime didn’t allow this and threw the ‘feature not supported: update statement for volatile table‘ error. So the update was definitely not the way to go.

    Thanks,
    Anton