How to Post an Image from SAPUI5 and Store in HANA DB as BLOB using XSJS

[jmalla]

Though there are lots of articles on posting attachments through SAPUI5 and parsing them in XSJS, I had to dig through them to find the solution to upload an image and save this as a BLOB in the HANA database.  So I decided to create a simple BLOG so that anyone else, could do this easily in a matter of minutes.

• Here is the database table definition:

CREATE COLUMN TABLE "MYSCHEMA"."IMAGE_STORE"(

	"IMAGE_NAME" NVARCHAR(100),

	"IMAGE_CONTENT" BLOB MEMORY THRESHOLD 1000,

	PRIMARY KEY (

		"IMAGE_NAME"

	)

) UNLOAD PRIORITY 5 AUTO MERGE;

 

 

• On the SAP UI5 side, use the FileUploader control: https://sapui5.hana.ondemand.com/sdk/explored.html#/entity/sap.ui.unified.FileUploader/samples.

Here is my control in my xml page:

 

<u:FileUploader id="fileUploader" name="myFileUpload" uploadUrl="" width="400px" tooltip="Upload your file to the local server" uploadComplete="handleUploadComplete"/>

<Button text="Upload File" press="handleUploadPress"/>

 

• The attachment will be sent as a MIME object through the controller:

		handleUploadPress: function(oEvent) {

			var oFileUploader = this.getView().byId("fileUploader");

			// Combine the URL with the filename....

			oFileUploader.setUploadUrl("../MyXSJS_Listener/SaveImage.xsjs?filename=" + oFileUploader.getValue());

			oFileUploader.upload();

		},

• In XSJS, simply read in the request into an ArrayBuffer and store as a BLOB.  This is the main part - reading the data as an ArrayBuffer and then writing as a BLOB with the setBlob method on the prepared statement:

 

	var schema_name = "MYSCHEMA";

	var filename = $.request.parameters.get('filename');	

	

	

	try {

		var conn = $.db.getConnection();

	

		var pstmt = conn.prepareStatement("INSERT INTO \"MYSCHEMA\".\"IMAGE_STORE\" (IMAGE_NAME, IMAGE_CONTENT) VALUES (?, ?)");

	

	

		if($.request.entities.length>0) {

			

			//  Read in the posted image or binary data as an Array Buffer - you can use this to save as a BLOB

			var file_body = $.request.entities[0].body.asArrayBuffer();

	

			pstmt.setString(1,filename);  // Set the file name

			pstmt.setBlob(2,file_body);   // Set the Blob as the array buffer that has the image data

			pstmt.execute();

	

		}

		else

		{

			$.response.setBody("No Entries in request");

		}

		pstmt.close();

		conn.commit();

		conn.close();

		$.response.contentType = "text/html";

		$.response.setBody("[200]:Upload for file" + filename + " was successful!");

	}

	catch(err)

	{

		if (pstmt !== null)

		{

			pstmt.close();

		}

		if (conn !== null)

		{

			conn.close();

		}

		$.response.contentType = "text/html";

		$.response.setBody("File could not be saved in the database.  Here is the error:" + err.message);

	}	

	

 

 

I hope this helps anyone looking to do something similar.

Thanks,

Jay

 

You can connect with me at - Jay Malla on LinkedIn