Wednesday, 11 September 2019

Create CSV File Upload SAPUI5 App with SAP HANA XSA

In this blog, I am going to share how to create a simple SAPUI5 app to upload .csv file into SAP HANA XSA tables. We will create the multi-target-application that consist of database, XSJS and SAPUI5 module.

Let’s get started.

Prepare the Project


◈ Logon to HANA XSA Web IDE and create project from template.
◈ Select SAP Cloud Platform Business Application and click Next.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Give a project name zcsvfileupload and click Next.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ On the following screen, click Next to continue.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ On the Project Details screen, set Service to Node.JS, Database to SAP HANA Database and version is 2.0 SPS 04. Click Next to continue.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Click Finish to complete.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

Create Database and Stored Procedure Artifacts


Now we need to create some database artifacts like table and stored procedure.

Go to folder zcvfileupload in Web IDE and create a new folder called data in src folder.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ In src/data folder, create zfileupload_dummy.hdbtable.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Insert the following content into zfileupload_dummy.hdbtable.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

COLUMN TABLE "ZFILEUPLOAD_DUMMY" (
"MATERIAL_NUMBER" NVARCHAR(18) COMMENT 'MATERIAL_NUMBER',
"BATCH_DATE" DATE COMMENT 'BATCH_DATE',
"MATERIAL_DESCRIPTION" NVARCHAR(1000) COMMENT 'MATERIAL_DESCRIPTION',
"COUNTRY" NVARCHAR(2) COMMENT 'COUNTRY',
"PROCESS_FLAG" NVARCHAR(1) COMMENT 'PROCESS_FLAG',
"RUNID" INTEGER COMMENT 'RUNID')
COMMENT 'ZFILEUPLOAD_DUMMY'  
UNLOAD PRIORITY 5 AUTO MERGE ​

◈ Create a new folder called Procedure to store the stored procedure artifact. And create a new file called insertData.hdbprocedure inside that folder.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Insert the following content into insertData.hdbprocedure.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

PROCEDURE "insertData" ( 
in MATERIAL_NUMBER NVARCHAR(18),
in BATCH_DATE DATE,
in MATERIAL_DESCRIPTION NVARCHAR(1000),
in COUNTRY NVARCHAR(2),
in PROCESS_FLAG NVARCHAR(1),
in RUNID INTEGER
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
--DEFAULT SCHEMA <default_schema_name>
--READS SQL DATA AS
AS -- "READS SQL DATA " removed  
BEGIN
/***************************** 
Write your procedure logic 
 *****************************/
insert into
"ZFILEUPLOAD_DUMMY"
values 
(
MATERIAL_NUMBER,
BATCH_DATE,
MATERIAL_DESCRIPTION,
COUNTRY,
PROCESS_FLAG,
RUNID
);
END;

◈ Build the database module. Right click on db folder and click Build > Build.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Make sure you have built successfully.

Build of /zcsvfileupload/db completed successfully.​

◈ Here is the complete structure of the db module.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

Create XSJS Module


We will create the xsjs module that handles the .csv data that has been uploaded via the front end (UI5) and call the stored procedure to insert the record(s) into the table ZFILEUPLOAD_DUMMY.

◈ Right click on zcsvfileupload folder and select New > Node.js Module.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Give a module name xsjs and click Next.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ In Template Customization, set the main JS file to server.js and tick the Enable XSJS support. Click Next to continue.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Click Finish to complete.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Create file_upload.xsjs in xsjs/lib folder.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◉ Insert the following code into file_upload.xsjs.

var contentType;
var fileContent;

var connection;
var procedureCall;

function insertRow(row) {
if (row === undefined) {
return;
}
var params = row.split(',');
var MATERIAL_NUMBER = params[0].toString();
var BATCH_DATE = params[1];
var MATERIAL_DESCRIPTION = params[2].toString();
var COUNTRY = params[3].toString();
var PROCESS_FLAG = params[4].toString();
var RUNID =  Number(params[5]);
procedureCall(MATERIAL_NUMBER, BATCH_DATE, MATERIAL_DESCRIPTION, COUNTRY, PROCESS_FLAG, RUNID);
}

function loadDataFromFile(file_content) {
try {
var row_index = 1;
var file_rows = file_content.split('\n');

connection = $.hdb.getConnection();
procedureCall = connection.loadProcedure('insertData');

for (row_index = 1; row_index < file_rows.length; row_index++) { // jump header
insertRow(file_rows[row_index]);
}
connection.commit();
connection.close();
$.response.contentType = "text/plain";
$.response.setBody("File imported!!"); // assuming it's in the correct format! 
$.response.returnCode = 200;
} catch (err) {
    $.response.contentType = "text/plain";
    $.response.setBody("Error while executing query: [" + err.message + "]");
    $.response.returnCode = 200;
}
}

// Check Content type headers and parameters
function validateInput() {
if ($.request.method !== $.net.http.POST) {
$.response.status = $.net.http.NOT_ACCEPTABLE;
$.response.setBody("Only POST is supported!!");
return false;
}
var file_entity_index;

// Get entity header which contains the file content
for (file_entity_index = 0; file_entity_index < $.request.entities.length; file_entity_index++) {

if ($.request.entities[file_entity_index].headers.get("~content_name") === "fup_data") {
contentType = $.request.entities[file_entity_index].headers.get("content-type");

if (contentType === 'application/vnd.ms-excel') {
$.response.status = $.net.http.ACCEPTED;
fileContent = $.request.entities[4].body.asString();
return true;
}
}
}

$.response.status = $.net.http.NOT_ACCEPTABLE;
$.response.setBody("File is NOT a CSV!");
return false;
}

// Request process 
function processRequest() {
if (validateInput()) {
loadDataFromFile(fileContent);
}
}
// Call request processing  
processRequest();​

Create Web Module


◉ Right click on zcsvfileupload folder and select New > Basic HTML5 Module.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◉ Give a module name web and click Next.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◉ Click Finish to complete.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◉ Import the UI5 app from my Git into web/resources folder.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◉ Update the content of xs-app.json.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

{
"welcomeFile": "index.html",
"authenticationMethod": "route",
"routes": [{
"source": "(.*)(.xsjs)",
"destination": "xsjs_api",
"csrfProtection": false,
"authenticationType": "xsuaa"
},{
"source": "/node(.*)",
"destination": "xsjs_api",
"csrfProtection": true,
"authenticationType": "none"
}]
}​

◉ Create a file called xs-security.json in the root folder and insert the following content:

{
"xsappname": "zcsvfileupload",
"scopes": [{
"name": "$XSAPPNAME.Display",
"description": "display"
}, {
"name": "$XSAPPNAME.Create",
"description": "create"
}, {
"name": "$XSAPPNAME.Edit",
"description": "edit"
}, {
"name": "$XSAPPNAME.Delete",
"description": "delete"
}, {
"name": "$XSAPPNAME.DataGenerator",
"description": "data generator"
}, {
"name": "xs_authorization.read",
"description": "Read authorization information from UAA"
}, {
"name": "xs_authorization.write",
"description": "Write authorization information to UAA"
}, {
"name": "$XSAPPNAME.ODATASERVICEUSER",
"description": "Enter"
}, {
"name": "$XSAPPNAME.ODATASERVICEADMIN",
"description": "Enter"
}],
"attributes": [{
"name": "client",
"description": "Session Client",
"valueType": "int"
}, {
"name": "country",
"description": "country",
"valueType": "s"
}],
"role-templates": [{
"name": "Viewer",
"description": "View all records",
"scope-references": [
"$XSAPPNAME.Display"
],
"attribute-references": [
"client", "country"
]
}, {
"name": "Editor",
"description": "Edit and Delete records",
"scope-references": [
"$XSAPPNAME.Create",
"$XSAPPNAME.Edit",
"$XSAPPNAME.Delete",
"$XSAPPNAME.Display",
"$XSAPPNAME.DataGenerator",
"$XSAPPNAME.ODATASERVICEUSER",
"$XSAPPNAME.ODATASERVICEADMIN"
],
"attribute-references": [
"client"
]
}]
}​

◈ Create the UAA service for zcsvfileupload in Authorization & Trust Management from HANA XS Advanced Cockpit.

Update .MTA


◈ Open mta.yaml file

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ And update the content.

ID: zcsvfileupload
_schema-version: "2.1"
version: 0.0.1
modules:
  - name: zcsvfileupload-db
    type: hdb
    path: db
    parameters:
      memory: 256M
      disk-quota: 256M
    requires:
      - name: zcsvfileupload-db-hdi-container
  - name: zcsvfileupload-srv
    type: nodejs
    path: srv
    parameters:
      memory: 512M
      disk-quota: 256M
    provides:
      - name: srv_api
        properties:
          url: ${default-url}
    requires:
      - name: zcsvfileupload-db-hdi-container

  - name: zcsvfileupload-xsjs
    type: nodejs
    path: xsjs
    provides:
     - name: xsjs_api
       properties:
          url: '${default-url}'
    requires:
      - name: zcsvfileupload-uaa
      - name: zcsvfileupload-db-hdi-container
      - name: zcsvfileupload-db


  - name: zcsvfileupload
    type: html5
    path: web
    requires:
      - name: zcsvfileupload-uaa
      - name: zcsvfileupload-db-hdi-container
      - name: xsjs_api
        properties:
          name: xsjs_api
          url: '~{url}'
          forwardAuthToken: true
        group: destinations

resources:
  - name: zcsvfileupload-db-hdi-container
    type: com.sap.xs.hdi-container
    properties:
      hdi-container-name: ${service-name}
  - name: zcsvfileupload-uaa
    type: com.sap.xs.uaa-space
    parameters:
      config-path: ./xs-security.json​

Run the Modules


◈ Run the XSJS and web module.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Open the web link and upload the .csv file in this format:

MATERIAL_NUMBER,BATCH_DATE,MATERIAL_DESCRIPTION,COUNTRY,PROCESS_FLAG,RUNID
F000000001,,MATERIAL A,AA,,
F000000002,,MATERIAL B,BB,,
F000000003,,MATERIAL C,CC,,
F000000004,,MATERIAL D,DD,,
F000000005,,MATERIAL E,EE,,​

◈ If the upload is success, you will get the message “File imported!!!”.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

◈ Check the table in the Database Explorer and see if the data has been populated.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Online Exam

No comments:

Post a Comment