HANA XS Dynamic Job Scheduling through UI5 application the Easy Way

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.

Source: scn.sap.com