HANA XS Dynamic Job Scheduling through UI5 application the Easy Way
Hi everyone, in this blog I’ll share a simple example of how we achieved the HANA XS dynamic job scheduling with UI5 application.
So the process’s very simple, we define an “empty” job in XS Job Dashboard, then we add/delete the job scheduler through the UI5 application with a switch action.
First, the user you’re using to authentication for your UI5 application must have the following role assigned:
sap.hana.xs.admin.roles::RuntimeConfAdministrator
If you want to delete the job logs as well, you also need to manually add SELECT and DELETE to the following tables (schema _SYS_XS):
JOB_SCHEDULERS
JOB_LOG
Create the following files at the same package level in Web IDE (I found it won’t work if the files were located deeper than 2 sub-package, maybe you can try it in your way)
autopilot.xsjob
autopilot.xsjs
schedule.xsjs
Here’s the content of the file autopilot.xsjs, very simple code with an input parameter, if the input isn’t null then call the SQL Procedure.
function run(input) {
if (input.CODE === undefined || input.CODE === null) {
$.response.contentType = “application/javascript”;
$.response.setBody(“oCODE is null!”);
} else {
var CODE = input.CODE;
var conn = $.db.getConnection();
conn.prepareStatement(“SET SCHEMA TEST”).execute();
var stCal = conn.prepareStatement(‘CALL PRC_AUTOPILOT(?)’);
stCal.setString(1, CODE);
stCal.execute();
conn.commit();
conn.close();
}
}
The file autopilot.xsjob, it’s just an “empty” XS Job definition file with no scheules.
{
“action”: “wus.xsjs:autopilot.xsjs::run”,
“description”: “Autopilot Mode”,
“schedules”: []
}
The file schedule.xsjs, it’s also very simple, if the oACTION is “off”, it’ll delete the job scheduler and also delete the job logs, if the oACTION is “on”, then it’ll add scheduler to the job we defined earlier with user input parameter, please make your own changes since we’re not very interested in the error handling here.
var oCODE = $.request.parameters.get(“CODE”);
var oACTION = $.request.parameters.get(“ACTION”);
var myjob = new $.jobs.Job({
uri: “autopilot.xsjob”
});
if (oACTION === “off”) {
var conn = $.db.getConnection();
var stCalPRC = conn.prepareStatement(‘SELECT ID FROM “_SYS_XS”.”JOB_SCHEDULES” WHERE PARAMETER LIKE \’%\’||?||\’%\”);
stCalPRC.setString(1, oCODE);
stCalPRC.execute();
var oResultSet = stCalPRC.getResultSet();
var result = {
records: []
};
while (oResultSet.next()) {
result.records.push({
value: oResultSet.getInteger(1)
});
}
oResultSet.close();
stCalPRC.close();
var id = result.records[0].value;
myjob.schedules.delete({
id: id
});
//delete job logs
var sID = JSON.stringify(id);
var stCalClear = conn.prepareStatement(‘DELETE FROM “_SYS_XS”.”JOB_LOG” WHERE ID = ?’);
stCalClear.setString(1, sID);
stCalClear.execute();
conn.commit();
conn.close();
$.response.contentType = “application/json; charset=UTF-8”;
$.response.setBody(sID);
$.response.status = $.net.http.OK;
} else if (oACTION === “on”) {
var minute = Math.floor(Math.random() * 30) + 1; //from 1 – 30
var cron = “* * * * 15 ” + minute + ” 0″;
var jobid = myjob.schedules.add({
description: “Enable Autopilot Mode”,
xscron: cron, //use random minute to avoid system oveload
parameter: {
CODE: oCODE
}
});
}
Then go to the XS Job Dashboard with the following URL prefix, configure the job definition like user name and password, session timeout value etc. Notice there’s no scheduler available yet.
/sap/hana/xs/admin/jobs
The XML view and controller (only show part of the content here)
<mvc:View
xmlns:core=”sap.ui.core”
xmlns:html=”http://www.w3.org/1999/xhtml“
xmlns:mvc=”sap.ui.core.mvc”
xmlns=”sap.m”
xmlns:sap.ui.layout=”sap.ui.layout”
xmlns:tb=”sap.ui.table”
xmlns:sap.m.semantic=”sap.m.semantic” controllerName=”view.Autopilot”>
<App>
<pages>
<Page title=”{i18n>apTitle}” showNavButton=”true” navButtonPress=”goBack”>
<content>
<FlexBox width=”100%” id=”__box1″ alignItems=”Center” justifyContent=”Center”>
<FlexBox width=”80%” alignItems=”Center” justifyContent=”SpaceBetween” class=”marginLine”>
<items>
<Label id=”__labelAuto” text=”{i18n>apTitle}” class=”LabelDeactive”/>
<Switch id=”switchAuto” state=”false” customTextOn=”On” customTextOff=”Off” change=”switchAutoChange” />
</items>
</FlexBox>
</FlexBox>
</content>
<headerContent></headerContent>
</Page>
</pages>
</App>
</mvc:View>
Part of the Controller.js file
var code = view.byId(“__selectAgent”).getSelectedKey();
var action = “”;
…….If the action is to enable the switch
action = “on”;
$.ajax({
url: localStorage[“test.url”] + “/wus/xsjs/schedule.xsjs?CODE=” + code + “&ACTION=” + action,
type: ‘get’,
async: false,
headers: {
‘Authorization’: “Basic xxxxxxxxxxxxxxxxxxxxxxxxxx”
},
error: function(response) {
view.byId(“switchAuto”).setState(false);
sap.m.MessageBox.show(opError, sap.m.MessageBox.Icon.ERROR, “Error”, sap.m.MessageBox.Action.CLOSE);
},
success: function() {
view.byId(“switchAuto”).setState(true);
}
});
//…….If the action is to disable theswitch
action = “off”;
$.ajax({
url: localStorage[“test.url”] + “/wus/xsjs/schedule.xsjs?CODE=” + code + “&ACTION=” + action,
type: ‘get’,
async: false,
headers: {
‘Authorization’: “Basic xxxxxxxxxxxxxxxxxxxxxxxxxx”
},
error: function(response) {
view.byId(“switchAuto”).setState(true);
sap.m.MessageBox.show(opError, sap.m.MessageBox.Icon.ERROR, “Error”, sap.m.MessageBox.Action.CLOSE);
},
success: function() {
view.byId(“switchAuto”).setState(false);
}
});
If you go to the UI5 application and enable the switch, you can see that the job scheduler has one entry at the XS Job Dashboard.
If you disable the switch in UI5 application, the job scheduler will be deleted.
That’s all for this blog, hope it will be helpful to you and I’m willing to receive your feedback!
Regards,
Michael
Nice blog! Very helpful!