Skip to Content
Technical Articles
Author's profile photo Jay Malla

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

 

Assigned Tags

      19 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Moya Watson
      Moya Watson

      Really great! Love how you figured it out and shared your knowledge with the community! Thanks Jay.

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Thanks for your nice comments!  I have been an SAP Netweaver and PI integration consultant since 2000 but now focusing on HANA and UI5.  It's great stuff!  I am really amazed at SAP's new platform and vision.  I am building an application for eye doctors and optometrists to collaborate.  Now it's possible to develop applications for startups using SAP HANA.  Looking forward to posting more.  I also started a meetup group called SAP HANA Enthusiasts and hoping to set up the first meetup here in San Francisco and network with other developers and collaborate  🙂

      Author's profile photo Pankaj Kumar
      Pankaj Kumar

      Hi Jay,

      Good to see you blogging. I am sure this can be used to post other blobs like PDF and word docs.

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Hi Pankaj - good to connect with you again.  I know that you are the expert in this domain.  I'm really enjoying this HANA development with UI5.  I will post another BLOG on UI5, HANA, Twitter, and Facebook integration with geo-location integration 🙂   You should join my meetup group - I will schedule a meetup in April.

      Author's profile photo Former Member
      Former Member

      I have successfully done by this, I have done to retrieved single image at a time in XSJS but I can't able to get a list (combined all images) of record.

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Not quite sure of your question...

      Author's profile photo Former Member
      Former Member

      Hi Jay,

      I am getting this error when I follow your example - Failed to load resource: the server responded with a status of 403 (Forbidden)

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Hi Nitin,

      Check your permissions for the user in terms of the database access.

      Also check your .xsaccess file.  Here is mine:

      {

           "exposed" : true,

           "cache_control" : "must-revalidate",

           "cors":  {

            "enabled":true,

            "allowMethods": ["GET","POST","DELETE","PUT"],

            "allowOrigin": ["*"],

            "maxAge":"3600"

            },

           "enable_etags" : false,

           "force_ssl" : false,

           "prevent_xsrf" : false

      }

       

      Hope that helps.

      Regards,

      Jay

      Author's profile photo Former Member
      Former Member

      Perfect! that works. I have one more question for you. How can I debug xsjs file using eclipse? I tried couple of links from blog and other resources but no success. I want to debug request sent to xsjs from ui5 but I am not able to figure it out.

       

      Thanks

      Nitin

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      You have to get the session id cookie from Chrome and use that in Eclipse.  It's painful but you can get it to work.

      Author's profile photo Former Member
      Former Member

      Thanks for reply Jay! I am ableto use eclipse for debugging.  I am having very tough time to access data on hana sent via ajax post. I am sending some objects array using ajax post but I dont see anything on hana except query parameters.

       

      $.ajax({ url: uploadUrl,

      type: “POST”,

      data:{“first_name”:”Nitin”},

      I cant figure out to get the first_name value on hana side. I have done this lot of times but for some reason not able to do it here

      Author's profile photo Damian Zurawski
      Damian Zurawski

      woah, why prevent_xsrf is set to false? It should prevent our application from cross site scripting forgery.

       

      From SAP Documentation:

      It is highly recommended to always use the prevent_xsrf keyword to help protect your
      application against attacks that use cross-site request forgery.
        [...] There is no good reason why you would explicitly set this keyword to false

      which means, that we should always set this parameter to true. Be careful with this.

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Sure.... you're right.  But did you get the image processing to work as per the BLOG?

      Author's profile photo Former Member
      Former Member

      Hello Jay and thank you for this great Blog,

      i have a question, do you have a way of retrieving the Image once it is stored ?

      i have seen this: https://blogs.sap.com/2016/09/01/upload-and-retrieve-image-using-sap-hana-xs-sap-ui5/

      but i only get errors (500)  when trying to retrieve a .png file i had previously stored using your method.

      Thanks a lot in advance.

      Good wishes to you

      Author's profile photo Former Member
      Former Member

      UPDATE:
      Current situation (me and my coworker are working on):
      https://answers.sap.com/questions/298420/load-blob-into-sapui5.html

      Author's profile photo Jorge Sanchez
      Jorge Sanchez

      I tried to implement your code, but when i run my code i received this :

      "Post (URL of my saveImage.xsjs file) Method Not Allowed"

      mi code is the same that your, obviously en "MySchema" i changed it for the name of my schema in hana, also i added the sufficient permitis, well i have some questions:

      This URL:
      ../MyXSJS_Listener/SaveImage.xsjs

      did you create a folder in your project called "MyXSJS_Listener" and then in this folder you created a file called "SaveImage.xsjs" ?? is it correct? i did it, but i dont know if it is correct.

      please, help me

      Regards

      Author's profile photo David azur
      David azur

      Can you send how to retrieve image from Database

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Here is my code for the GetImageByFilename.xsjs:

       

      /*	  var fileImageData = '';
            var filename = $.request.parameters.get('FILENAME');
            var conn = $.db.getConnection();
            var query = "select * from \"MYSCHEMA\".\"IMAGE_STORE\" where IMAGE_NAME = '" + filename + "'"  ;
      
       
            var pstmt = conn.prepareStatement(query);
            var fileImageResult = pstmt.executeQuery();
            if (fileImageResult.next()) {// User Image retrieved
                  fileImageData = fileImageResult.getString(2);
            }
            $.response.setBody(fileImageData);
            $.response.contentType = 'text/plain';
            $.response.status = $.net.http.OK;
      */      
            
            
            var schema_name = "MYSCHEMA";
            var filename = $.request.parameters.get('filename');
            var conn = $.db.getConnection();
      
            try {
                var query = "Select IMAGE_CONTENT From " + schema_name + ".IMAGE_STORE Where IMAGE_NAME = '" + filename + "'";
      
                var pstmt = conn.prepareStatement(query);
                var rs = pstmt.executeQuery();
                if(rs.next()){
                    $.response.headers.set("Content-Disposition", "Content-Disposition: attachment; filename=filename.jpg");
                    $.response.contentType = 'image/jpg';
                    $.response.setBody(rs.getBlob(1));
                    $.response.status = $.net.http.OK;
                }
                else{
                	$.response.contentType = 'text/html';
                    $.response.setBody("Image not found");
                    $.response.status = 404;
                }
      
            } catch (e) {
                $.response.setBody("Error while downloading : " + e);
                $.response.status = 500;
            }
            conn.close();      

       

      I hope this helps you.

      Regards,

      Jay

      Author's profile photo Mohammad Shafiullah Mysore
      Mohammad Shafiullah Mysore

      This method is not working in SAP XS Advance.