Skip to Content
Technical Articles

Sample Fiori Application showcase Attachment Uploading Capabilities of SAPUI5 using XSODATA and HANA TABLE

Hello Developers,

This is my first blog post on SAP.

I have spend a lot time with SAPUI5 but one thing I always stuck was with Attachments be it JPEG or PDF.

I wanted to create an application which will upload a file to hana table using xsodata.

After browsing through various blog post and website I tried to manage a complete end to end code that will help with to upload and manage a file to HANA TABLE using SAPUI5 Application

 

Prerequisite that I took before creating this project:

  1. I am using Database “demodb” in SAP Cloud Platform Cockpit
  2. I have logged SAP HANA Web-Based Development Workbench with user other than ADMIN
  3. I have logged using “DEMOUSER” as my other user
  4. I have set destination to connect HANA DB to SAPUI5 application
  5. I have SAP WEBIDE TRIAL ACCOUNT

So, let’s get started…..

First part is to create a Table in HANA.

Create a HANA TABLE ATTACHMENT with Columns:

(ATTACHMENTID, ATTACHMENTNAME & ATTACHMENTCONTENT)

SCHEMA: DEMOUSER

TABLE NAME: ATTACHMENT

Goto CATALOG –> DEMOUSER(My SCHEMA) –> TABLE (right click) –>  Open SQL Console

Type in below SQL  and press F8

CREATE COLUMN TABLE "DEMOUSER"."ATTACHMENT"(
	"ATTACHMENTID" INTEGER CS_INT,
	"ATTACHMENTNAME" NVARCHAR(100),
	"ATTACHMENTCONTENT" CLOB MEMORY THRESHOLD 1000000,
	PRIMARY KEY (
		"ATTACHMENTID"
	)
) UNLOAD PRIORITY 5 AUTO MERGE;

HANA Table should look like below

Note: Attachment content datatype is CLOB which is Character Long Object as we are going to push BASE64 data.

 

We need to expose this table using XSODATA

    • GOTO EDITOR

  • Right click on content and Create Application “attachment”

Below is my initial folder structure

Create new File “attachment.xsodata”

service namespace "application" 
{ 
    "DEMOUSER"."ATTACHMENT" as "ATTACHMENT";
    
}

Run this file to check weather your XSODATA file is working or not(Click F8 after saving the XSODATA file)

 

 

Create a new file “ATTACHMENT.hdbschema”

schema_name="DEMOUSER";

We now need to provide privileges  to perform CRUD on the HANA TABLE

Create new file “attachment.hdbrole”

Under Object Privileges. Click on Plus Sign

Provide privileges as below

Similarly perform privileges for Table and Schema

Upon saving file, it might give you some error (ignore theme)

 

Modify .xsaccess file as below

{
    "exposed": true,
    "authentication": [{
        "method": "Basic"
    }],
    "force_ssl": false,
    "enable_etags": true,
    "prevent_xsrf": false,
    "anonymous_connection": null,
    "cors": [{
        "enabled": false
    }],
    "cache_control": "no-cache, no-store"
}

We have successfully completed our first task. Let’s go to main UI where we need to upload a file.

 

SAPUI5 Application

Create a new SAPUI5 Application from Template

Create a Simple Form with File Uploader and Submit Button

Find below the controller code View1.controller.js

sap.ui.define([
	"sap/ui/core/mvc/Controller"
], function (Controller) {
	"use strict";

	return Controller.extend("attachment.attachment.controller.View1", {
		onInit: function () {
			var oStorage = jQuery.sap.storage(jQuery.sap.storage.Type.local);
			oStorage.clear();
		},
		seePreview: function (oEvent) {
			var oStorage = jQuery.sap.storage(jQuery.sap.storage.Type.local);
			if (oStorage.get("oAttachment") !== null) {
				this.getView().byId("img").setSrc(oStorage.get("oAttachment").ATTACHMENTCONTENT);
			} else {
				sap.m.MessageToast.show("Kindly Upload the Attachment First");
			}

		},
		handleUploadPress: function (oEvent) {
			var fileUploader = this.getView().byId("fileUploader");
			var id = this.getView().byId("input").getValue();
			var attachid = parseInt(id);
			var fileStore = jQuery.sap.domById(fileUploader.getId() + "-fu").files[0];
			this.upload(fileStore, fileStore.name,attachid);
		},
		upload: function (fileStore, docName,attachid) {
			this.attach = {};

			if (fileStore) {
				var fileReader = new FileReader();
				var selectedFile = fileStore;
				fileReader.onload = function (oEvent) {
					var base64 = oEvent.target.result;
					var oStorage = jQuery.sap.storage(jQuery.sap.storage.Type.local);
					var attachment = {
						"ATTACHMENTID": attachid,
						"ATTACHMENTNAME": docName,
						"ATTACHMENTCONTENT": base64
					};
					this.attach = attachment;
					oStorage.put("oAttachment", attachment);
				};
				fileReader.readAsDataURL(selectedFile);
				sap.m.MessageToast.show("File upload successfully");

			} else {
				sap.m.MessageToast.show("File upload has been failed");
			}

		},
		onSubmit: function () {
			var oModel = this.getView().getModel();
			var oStorage = jQuery.sap.storage(jQuery.sap.storage.Type.local);
			var attachment = oStorage.get("oAttachment");
			oModel.create("/ATTACHMENT", attachment, {
				success: function (oData, response) {
					sap.m.MessageToast.show("Attacment Uploaded Successfully to XS Backend");
				},
				error: function (oError) {
					sap.m.MessageToast.show("Upload to backend Failed");
				}
			});
		}

	});
});

 

We are now done with the second part

Last part is testing out our application:

Give Attachment ID: 1, Browse a photo from your drive, and click on upload.

 

On clicking Upload, file data is now stored in an array.

To check if the correct file is uploaded, click on preview attachment.

 

Click on Submit to send image to backend:

We can now see an attachment in Upload Collection

 

 

 

CONCLUSION 

Above blog post helps a new developer with limited knowledge on SAPUI5 and HANA to upload a file(JPEG/PNG/PDF) from UI and View and finally Store in HANA DB using minimal coding and efforts.

As this is a big blog post with lots of steps involved, it might raise a lot of questions. So any questions regarding the my blog post are very much welcomed.

2 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    Thanks for the wonderful post :). It helped me a lot.

    I would like to know how to read back the attachments from the HANA table and display in UI5?

    • Hello Prakash,

      Thanks for your comment.

      I have 2 answers to your question:

      1. To read images, you directly get the base64 data from backend and set it as source for your image
      2. To read PDF, kindly follow this blog. I have already tried and it worked in my case.

      Kindly let me know if you face any trouble.

       

      Thanks,

      Pallavkumar Jadav