Technical Articles

Rajnish Tiwari

August 3, 2017 7 minute read

Binary content upload and download from HANA DB through Apache Olingo OData in SAP Cloud Foundry

1. Introduction

This blog is about the binary content upload and retrieval in SAP Cloud Platform(SCP) Cloud Foundry(CF) through Apache Olingo OData V2, which instead of storing binary(can be image, doc etc.) in UI application as folder structure or by any other mechanism, In this way we can store the images in the form of binary object in database along with the other information. Which we can retrieve while fetching the other information. Likewise we have requirement that we have products thumbnail needs to upload in real time along with the other product information and also customer can upload new thumbnail images, So instead of storing in the UI folder or in server side, we can upload the same in the database as blob or clob object and use it whenever needs to retrieve along with the other product details.

This blog is divided into two section, the 1^st part deals with the UI development, how image or any binary content can be processed in the UI using the filUpload UI5 components. The 2^nd part deals with the Apache Olingo OData V2 implementation for processing upload and download of binary contents and database designs.

2. Prerequisite Activities

The prerequisites in two part, the 1^st part would be the SAP UI5 app and in the 2^nd part backend services along with the database can be covered.

PART-1: UI5 APP

Create SAPUI5 app:

The UI5 app explained here is being used to upload and fetch the image and to explain through UI, how images feature can be used in UI5 by using the FileUploader control.

In any UI5 application includes 5 files namely

index.html
Component.js – route to given JS view
Controller.controller.js – server call for data manipulation
View.view.xml – View logic and SAP UI5 controls
xs-app.json – application routing from UI to backend services

As mentioned above the code will be shown below along with the web project snapshots

<!DOCTYPE HTML>
<html>
	<head>
		<meta http-equiv="X-UA-Compatible" content="IE=edge">
		<meta charset="utf-8">
		<title>Image Upload & retrieval Solution</title>
		<script id="sap-ui-bootstrap"
			src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"
			data-sap-ui-libs="sap.m"
			data-sap-ui-theme="sap_belize"
			data-sap-ui-xx-bindingSyntax="complex"
			data-sap-ui-preload="async"
			data-sap-ui-compatVersion="edge" 
			data-sap-ui-resourceroots='{"sap.ui.unified.sample.FileUploaderComplex": "./",  "sap.ui.demo.mock": "mockdata"}'>
		</script>
		<!-- Application launch configuration -->
		<script>

			sap.ui.getCore().attachInit(function() {
				new sap.m.App ({
					pages: [
						new sap.m.Page({
							title: "Image Upload & Retrieval through - Apache Olingo OData 2", 
							enableScrolling : true,
							content: [ new sap.ui.core.ComponentContainer({
								name : "sap.ui.unified.sample.FileUploaderComplex"
							})]
						})
					]
				}).placeAt("content");
			});

		</script>
	</head>
	<!-- UI Content -->
	<body class="sapUiBody" id="content" role="application">
	</body>

</html>

index.html

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

	var Component = UIComponent.extend("sap.ui.unified.sample.FileUploaderComplex.Component", {

		metadata : {
			rootView : "sap.ui.unified.sample.FileUploaderComplex.View",
			dependencies : {
				libs : [
					"sap.ui.unified"
				]
			},
			includes : [
				"../style.css"
			],
			config : {
				sample : {
					files : [
						"View.view.xml",
						"Controller.controller.js"
					]
				}
			}
		}
	});
	return Component;
});

component.js

sap.ui.define(['jquery.sap.global', 'sap/m/MessageToast', 'sap/ui/core/mvc/Controller'],
    function(jQuery, MessageToast, Controller) {
        "use strict";

        var ControllerController = Controller.extend("sap.ui.unified.sample.FileUploaderComplex.Controller", {
            imageId: 0,
            fileData: null,
            handleUploadComplete: function(oEvent) {
                var sResponse = oEvent.mParameters;
                if (sResponse) {

                    if (sResponse.status == 200) {
                        MessageToast.show("Done");
                    } else {
                        MessageToast.show("Failed");
                    }


                }
                sap.ui.core.BusyIndicator.hide();
            },

            handleUploadPress: function(oEvent) {
                var ref = this;
					ref.imageId = Math.floor(Math.random() * 1000);
					

                $.ajax({
                    url: "/",
                    type: "GET",
                    headers: {
                        "X-Requested-With": "XMLHttpRequest",
                        "Content-Type": "application/json",
                        "X-CSRF-Token": "Fetch"
                    },
                    dataType: "json",
                    async: false,
                    complete: function(data) {
                        $.ajax({
                            "url": "/sample.svc/v1/ImageStorages",
                            "data": JSON.stringify({
                                "ImageId": ref.imageId+"",
                                "ImageMimeType": "image/jpeg",
                                "ImageBinary": btoa(ref.fileData)
                            }),
                            "processData": false,
                            "headers": {
                                "X-Csrf-Token": data.getResponseHeader('X-Csrf-Token'),
                                "Content-Type": "application/json"
                            },
                            "method": "post"
                        })

                    }
                })


            },

            handleTypeMissmatch: function(oEvent) {
                var aFileTypes = oEvent.getSource().getFileType();
                jQuery.each(aFileTypes, function(key, value) { aFileTypes[key] = "*." + value });
                var sSupportedFileTypes = aFileTypes.join(", ");
                MessageToast.show("The file type *." + oEvent.getParameter("fileType") +
                    " is not supported. Choose one of the following types: " +
                    sSupportedFileTypes);
            },

            handleValueChange: function(oEvent) {
                var file = oEvent.getParameters().files[0];
                var reader = new FileReader();
                var ref = this;
                reader.onload = function(readerEvt) {
                    ref.fileData = readerEvt.target.result;
                    //console.log(btoa(binaryString));
                };

                reader.readAsBinaryString(file)
                MessageToast.show("Press 'Upload File' to upload file '" +
                    oEvent.getParameter("newValue") + "'");
            },
            handleLoadPress: function(oEvent) {


                  var img = this.byId("img");
				  var imgId = this.imageId+"";
					$.get("/sample.svc/v1/ImageStorages('"+imgId+"')?$format=json",function(data){
					img.setSrc("data:image/jpg;base64,"+data.d.ImageBinary)
				  })


            }
        });

        return ControllerController;

    });

Controller.control.js

As you can see above in the handle press event, needs to set the image src type to jpg. So that the content can be uploaded with the defined format along with imageId which is primary key in my case. In the upload file method, we need to call the method to fetch the xsrf token. Which will be passed along with the OData API call on attach press event.

<mvc:View controllerName="sap.ui.unified.sample.FileUploaderComplex.Controller"
	xmlns:l="sap.ui.layout" xmlns:u="sap.ui.unified" xmlns:mvc="sap.ui.core.mvc"
	xmlns="sap.m" class="viewPadding">
	<l:VerticalLayout>
		<u:FileUploader id="fileUploader" name="file"
			uploadUrl="/rest/api/v1/internal/uploadImage" width="400px"
			tooltip="Upload your file to the local server" uploadComplete="handleUploadComplete"
			change="handleValueChange" style="Emphasized" fileType="jpg"
			placeholder="Choose a file for Upload...">
		</u:FileUploader>
		<Button text="Upload File" press="handleUploadPress" />

		<Button text="Load File" press="handleLoadPress" />
		<Image id="img" src="/" densityAware="false" width="500px" height="500px" ></Image>
 	</l:VerticalLayout>
</mvc:View>

View.view.xml

Here the Fileuploader ui component is being used.

{
  "welcomeFile": "index.html",
  "logout":  {
    "logoutEndpoint":  "/do/logout"
  },
  "routes": [
{
    "source": "/sample.svc/v1/(.*)",
    "destination": "java"
  },
  {
    "source": "^/(.*)",
    "localDir": "resources"
  }
]
}

xs-app.json

In the above code, if you have noticed that we have routing the URL for JAVA application. which is being routes to given Java application. The above file can be part of one web folder basically in the resource folder and the xs-app.json file be part of the main project which will be used to route the request of server for uploading and retrieving of the images or thumbnails images. You can check the details project structure and files being used in the application in the github account. The apps can be deployed in SAP CP platform as the mentioned app is being developed and evaluated in SAP CP account.

PART-2: Java Services & Database

3. Create and build JPA Project: You can refer my previous log series regarding project setup blog series.

3.1 Get dependent Library: To get required library refer the blog series

4. Configuration and Implementation

4.1 Configure persistence.xml in JPA model: Refer to my previous blog series for this section.

4.1.1 Configure resource.xml in JPA model: Refer to my previous blog series for this section

5. Create Required Java Classes

You need to implements two method of OnJPAWriteContent class. But before that to enable write on such properties using OData JPA Processor Library, an additional access modifier is required to be added to the JPA entities. Following is the proposal on how OData JPA Processor Library handles java.sql.Blob and java.sql.Clob during metadata generation and runtime processing.

EDM Generation

Based on the JPA entity property type, the pseudocode for generating the EDM is as below.

For java.sql.Blob:

Check if JPA entity property is of type byte[] or of type java.sql.Blob and annotated with @Lob annotation.
If Step 1 is true, then generate an EDM property with type as Edm.Binary

public class OnDBWriteContent implements OnJPAWriteContent {
	final static Logger LOGGER = LoggerFactory.getLogger(OnDBWriteContent.class);
	@Override
	public Blob getJPABlob(byte[] binaryData) throws ODataJPARuntimeException {
		 try {
			 
			 LOGGER.error("IN getJPABlob");
	          return new JDBCBlob(binaryData);
	        } catch (SerialException e) {
	          ODataJPARuntimeException.throwException(ODataJPARuntimeException.INNER_EXCEPTION, e);
	        } catch (SQLException e) {
	          ODataJPARuntimeException.throwException(ODataJPARuntimeException.INNER_EXCEPTION, e);
	        }
	        return null;	
	        }

	@Override
	public Clob getJPAClob(char[] characterData) throws ODataJPARuntimeException {
		try {
			 LOGGER.error("IN getJPAClob");
	          return new JDBCClob(new String(characterData));
	        } catch (SQLException e) {
	          ODataJPARuntimeException.throwException(ODataJPARuntimeException.INNER_EXCEPTION, e);
	        }
	          return null;
	        }	

}

As you can see above in the screen shots that you need to implement the jpaBlob, jpaClob method.

5.1 Entity class added for the JPA implementations

@Entity
@Table(name="\"com.sap.sample::IMAGE_MANAGEMENT.IMAGE_STORE\"")
public class ImageStorage implements Serializable{

	private static final long serialVersionUID = 4829386985899147977L;

	@Id
	@Column(name="IMAGE_ID")
	private String imageId = null;

	@Lob
	@Column(name = "IMAGE_BINARY", length=100000000)
	private byte[] imageBinary = null;
	
	@Column(name = "IMAGE_MIME_TYPE")
	private String imageMimeType = null;

	public String getImageId() {
		return imageId;
	}

	public void setImageId(String imageId) {
		this.imageId = imageId;
	}

	public String getImageMimeType() {
		return imageMimeType;
	}

	public void setImageMimeType(String imageMimeType) {
		this.imageMimeType = imageMimeType;
	}

	public static long getSerialversionuid() {
		return serialVersionUID;
	}

	public byte[] getImageBinary() {
		return imageBinary;
	}

	public void setImageBinary(byte[] imageBinary) {
		this.imageBinary = imageBinary;
	}

In the above snapshots, you could see that data type byte [], defining the length (default length is set to 256), While loading the content. It was throwing the error of length, so to fix the issue needs to set the length for retrieving or uploading the binary content into the Database via ImageStorage Entity. I have taken an example of image content. This implementation will deal with any type of binary content, It can be doc, pdf etc. The Entity class needs to be added into the persistence.xml file. The annotation @Lob has to added for the column which mapped to byte [] columns as it bind with the binary content.

5.2 Class override in the main JPA Service factory class

The above class needs to be set it to the writeJPAContent method of SampleCustomOdataJpaServiceFactory (refer section 3.3.2) class as shown below

setOnWriteJPAContent(onDBWriteContent);

6. Database design

As shown above the IMAGE_BINARY fields stores the image object in the blob format. The above database field might change based on the requirement. In my case I have tried to show only for the image content save as blob object, it can be added for any no. of fields and any type of content that can be stored as binary content.

7. Conclusion

This blog is about implementation of binary content and retrieval through Apache Olingo OData from database, Of course there is a documentation about the implementation but there’s no such working example and details blog or technical examples mentioned anywhere. With the above approach, we can implement how to store any image content in the form of binary or blob object into database through Olingo OData V2 implementations.

Few of my other blogs:-

15 Comments

You must be Logged on to comment or reply to a post.

Rajat Arora

August 4, 2017 at 8:17 am

Very well written BLOG. Thanks

Rajnish Tiwari

Blog Post Author

August 9, 2017 at 3:06 pm

Thanks a lot for your feedback Rajat 🙂

Ashok Kumar M

August 22, 2017 at 8:52 pm

Hi,

Is it recommended to store binary data(esp large volume of images, video) in HANA?

Best Regards,

Ashok.

Rajnish Tiwari

Blog Post Author

August 23, 2017 at 7:38 am

Yes, it would possible but I have not evaluated as such. my max evaluation was for 25 MB file.

Thanks,

Rajnish

Ashok Kumar M

August 24, 2017 at 4:55 pm

Thanks Rajnish.

Might come across as a silly question – why not use String (with similar length as above) as data type instead of byte[]?

Something like this:

	@Lob
	@Column(name = "IMAGE_DATA", length = 10000000)
	private String imageData;

That way there is no need to implement JPABLOB or JPACLOB methods right?

Best Regards,

Ashok.

Rajnish Tiwari

Blog Post Author

August 25, 2017 at 6:14 am

No we can not do, If you noticed above the method setOnWriteJPAContent(onDBWriteContent),

Which basically executed only when @Lob annotation defined and if you defined as String. oData

implementation will not invoke the OnDBWriteContent class’s method even though the annotation is

defined. You may use char[] instead of byte[] method which will behave in same way for uploading or retrieving the images or any kind of files.

But you can try it out.

Best Regards,

Rajnish

Naman Jain

February 11, 2019 at 8:08 am

Thanks Rajnish for this blog, I had a question.

The JDBCBlob() object returned in getJPABlob() function is part of org.hsqldb.jdbc.JDBCBlob; right?

I'm following similar steps for handling Blobs but still facing errors.

Rajnish Tiwari

Blog Post Author

February 11, 2019 at 10:43 am

Hi Naman,

JDBCBlob() method is generic to handle any blob datatype.

Can you please let me know the exact error which you are facing.

Thanks,

Rajnish

Naman Jain

February 13, 2019 at 6:47 am

Hi Rajnish,

Thanks for your reply. I am using SerialBlob() from javax.sql.rowset.serial.SerialBlob and it's working fine.

Rajnish Tiwari

Blog Post Author

February 13, 2019 at 6:52 am

Okay that's great then, let me know, if you need any further info or approach. Happy to help

Thanks,

Rajnish

Naman Jain

February 14, 2019 at 7:30 am

Hi Rajnish,

I have an entity class (let's call it A) which is exposed through OData using Olingo.

At client side I get read this entity using GET requests on this entity set. My question is the following: Is there any way at client side by which I can get an object of this entity A.

Rajnish Tiwari

Blog Post Author

February 14, 2019 at 8:30 am

Yes you can read the image object data in the client side, as shown in Controller.control.js snapshots. see the section handleLoadPress method and variable data.d.ImageBinary . which read from OData Api i.e. JPA class defined.

Is that answer your question? or you have different queries.

Thanks,

Rajnish

Naman Jain

February 21, 2019 at 5:31 am

Hi Rajnish,

If we have an ODataEntry at client, can be get the Entity Object from it somehow at client side?

Rajnish Tiwari

Blog Post Author

February 21, 2019 at 8:15 am

Hi Naman,

i didn't get the issue which your facing can you elaborate it with example or the exact issue which you are facing.

-Thanks, Rajnish

Naman Jain

March 1, 2019 at 8:37 am

Hi Rajnish,

I get the following error when I follow the above steps.

The metadata constraints '[Nullable=true, MaxLength=255]' do not allow to format the value '[B@23442e67' for property 'Orblk'.

Can you please point me in right direction.

thanks,

Naman Jain

Technical Articles

Binary content upload and download from HANA DB through Apache Olingo OData in SAP Cloud Foundry

Assigned Tags