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

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