All, there could be many instances where end users would require the ability to upload a file of data to a table in HANA on a day to day basis where the Table Import functionality is not sufficient. In this blog I demonstrate the File upload capability using SAPUI5 and HANA Extended Services. Note: this is on SPS06.
- Create a new SAPUI5 Application Project
- Create a new SAPUI5 Application Project
- Create a hdbtable file which creates the table to upload the data to. Note Batch_id will be a combination of the file name and timestamp. Col1 will store the actual uploaded data.
- Add the following code to WebContent/index.html file
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<script src="/sap/ui5/1/resources/sap-ui-core.js"
id="sap-ui-bootstrap"
data-sap-ui-libs="sap.ui.commons,sap.ui.table"
data-sap-ui-theme="sap_goldreflection" >
</script>
<!-- add sap.ui.table,sap.ui.ux3 and/or other libraries to 'data-sap-ui-libs' if required -->
<script>
sap.ui.localResources("demofileupload");
var view = sap.ui.view({id:"idFileUpload1", viewName:"demofileupload.FileUpload", type:sap.ui.core.mvc.ViewType.JS});
view.placeAt("content");
</script>
<script type="text/javascript" src="/sap/ui5/1/resources/jquery-sap.js"></script>
</head>
<body class="sapUiBody" role="application">
<div id="content"></div>
</body>
</html>
- Add the following code to the view file FileUploader.view.js. This contains a FileLoader control, an Upload button and a table view to display the uploaded data. You will notice I have added a second Upload button. I will explain this further later in the blog but this is to demo how to address the cross-site request forgery security concern.
sap.ui.jsview("demofileupload.FileUpload", {
getControllerName : function() {
return "demofileupload.FileUpload";
},
createContent : function(oController) {
jQuery.sap.require("jquery.sap.resources");
var oPanel = new sap.ui.commons.Panel("Panel",
{text: "File Upload",
height: "750px"
});
var oSplitter = new sap.ui.commons.Splitter("ScreenSplitter",
{splitterOrientation: "Horizontal"});
var oVertLayout = new sap.ui.commons.layout.VerticalLayout("VertLayout");
oSplitter.addFirstPaneContent(oVertLayout);
oPanel.addContent(oSplitter);
/************File Uploader Browse ************/
var oFLTxt = new sap.ui.commons.TextView("FileLoaderText", {text:"Please choose file for upload."});
oVertLayout.addContent(oFLTxt);
var oFileUploader = new sap.ui.commons.FileUploader("FileLoader");
oFileUploader.attachUploadComplete(oController.doFileLoadComplete);
oVertLayout.addContent(oFileUploader);
/************ Upload button*************/
var oButton = new sap.ui.commons.Button({
id : this.createId("UploadButton"),
text : "Upload"
});
oButton.attachPress(oController.doFileUpload);
oVertLayout.addContent(oButton);
//To address cross-site request forgery security concern
var oButton2 = new sap.ui.commons.Button({
id : this.createId("UploadButton2"),
text : "Upload 2"
});
oButton2.attachPress(oController.doFileUpload2);
oVertLayout.addContent(oButton2);
/************ Batch table ************/
var oModel = new sap.ui.model.odata.ODataModel("../Services/MY_FILE_UPLOAD_TABLE.xsodata",false);
var oControl;
var oTable = new sap.ui.table.Table("BatchTable", {tableId: "BatchTableId",visibleRowCount: 10});
oTable.setTitle("Batch file data");
oControl = new sap.ui.commons.TextField().bindProperty("value","BATCH_ID");
oTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "Batch ID"}),
template: oControl,
sortProperty: "BATCH_ID",
filterProperty: "BATCH_ID"
}));
oControl = new sap.ui.commons.TextField().bindProperty("value","COL1");
oTable.addColumn(new sap.ui.table.Column({label:new sap.ui.commons.Label({text: "Column 1"}),
template: oControl,
sortProperty: "COL1",
filterProperty: "COL1"
}));
oTable.setModel(oModel);
oTable.bindRows("/FILE_UPLOAD_TABLE");
oSplitter.addSecondPaneContent(oTable);
return oPanel;
}
});
- Add the following code to the controller file FileUploader.controller.js. The functions doFileUpload and doFileUploadComplete are called when the first “Upload” button is pressed. The function doFileUpload2 is called when the second button “Upload2” is pressed. This is to demo how Cross-site Request Forgery concern can be addressed.
sap.ui.controller("demofileupload.FileUpload", {
/**
* Called when a controller is instantiated and its View controls (if available) are already created.
* Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
*/
// onInit: function() {
//
// },
/**
* Similar to onAfterRendering, but this hook is invoked before the controller's View is re-rendered
* (NOT before the first rendering! onInit() is used for that one!).
*/
// onBeforeRendering: function() {
//
// },
/**
* Called when the View has been rendered (so its HTML is part of the document). Post-rendering manipulations of the HTML could be done here.
* This hook is the same one that SAPUI5 controls get after being rendered.
*/
// onAfterRendering: function() {
//
// },
/**
* Called when the Controller is destroyed. Use this one to free resources and finalize activities.
*/
// onExit: function() {
//
// }
doFileUpload : function(oEvent)
{
var url = "../Services/BatchFileUpload.xsjs";
var fileLoader = sap.ui.getCore().byId("FileLoader");
var fileName = fileLoader.getValue();
if (fileName == "" )
{
jQuery.sap.require("sap.ui.commons.MessageBox");
sap.ui.commons.MessageBox.show("Please choose File.", sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
}
else
{
url = url+"?file_name="+fileName;
fileLoader.setUploadUrl(url);
fileLoader.upload();
}
},
doFileLoadComplete : function(oEvent)
{
jQuery.sap.require("sap.ui.commons.MessageBox");
var sResponse = oEvent.getParameter("response");
sap.ui.commons.MessageBox.show(sResponse, sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
sap.ui.getCore().byId("BatchTable").getModel().refresh();
},
doFileUpload2 : function(oEvent)
{
var fileLoader = sap.ui.getCore().byId("FileLoader");
var fileName = fileLoader.getValue();
jQuery.sap.require("sap.ui.commons.MessageBox");
if (fileName == "" )
{
sap.ui.commons.MessageBox.show("Please choose File.", sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
}
else
{
var uploadUrl = "../Services/BatchFileUpload.xsjs?file_name="+fileName;
var formEle = jQuery.sap.domById("FileLoader");
var form = $(formEle).find("form")[0] ;
var fd = new FormData(form);
$.ajax({
url: uploadUrl,
type: "GET",
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", "Fetch");
},
success: function(data, textStatus, XMLHttpRequest) {
var token = XMLHttpRequest.getResponseHeader('X-CSRF-Token');
$.ajax({
url: uploadUrl,
type: "POST",
processData :false ,
contentType: false ,
data:fd,
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", token);
},
success: function(data, textStatus, XMLHttpRequest)
{
var resptext = XMLHttpRequest.responseText;
jQuery.sap.require("sap.ui.commons.MessageBox");
sap.ui.commons.MessageBox.show(resptext, sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
sap.ui.getCore().byId("BatchTable").getModel().refresh();
},
error: function(data, textStatus, XMLHttpRequest)
{
sap.ui.commons.MessageBox.show("File could not be uploaded.", sap.ui.commons.MessageBox.Icon.ERROR, "Error");
}
});
}} ) ;
}
}
});
- Create server side javascript file ..Services/BatchFileUpload.xsjs to handle the extract of the data from the file and use batch insert to load the data into the table.
function escape(v1)
{
var v2 = v1.replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>');
return v2;
}
$.response.contentType = "text/html";
try
{
var conn = $.db.getConnection();
var filename = $.request.parameters.get("file_name");
var pstmtTime = conn.prepareStatement( "select UTCTOLOCAL(CURRENT_UTCTIMESTAMP,'EST') from dummy");
var rs = pstmtTime.executeQuery();
var batchTimestamp;
if (rs.next())
{
batchTimestamp = rs.getTimestamp(1);
}
var batchId = filename+"_"+batchTimestamp;
var pstmt = conn.prepareStatement( "insert into MURPHP11.\"pm.demo.fileupload2.Tables::MY_FILE_UPLOAD_TABLE\" (batch_id,col1) " +
"values(?,?)" );
if($.request.entities.length>0){
var file_body = $.request.entities[0].body.asString();
var allTextLines = file_body.split(/\r\n|\n/);
var lines;
var entries;
var col;
pstmt.setBatchSize(allTextLines.length-1);
for (lines=0; lines<allTextLines.length; lines++)
{
entries = allTextLines[lines].split(',');
col = entries.splice(0,allTextLines.length);
if ( col[0].length > 0 )
{
col[0] = escape(col[0]);
pstmt.setString(1,batchId);
pstmt.setString(2,col[0]);
pstmt.addBatch();
}
}
pstmt.executeBatch();
}
else
{
$.response.setBody("No Entries");
}
pstmt.close();
conn.commit();
conn.close();
$.response.setBody("[200]:Upload successful!");
}
catch(err)
{
if (pstmt !== null)
{
pstmt.close();
}
if (conn !== null)
{
conn.close();
}
$.response.setBody(err.message);
}
- Create an xsodata file MY_FILE_UPLOAD_TABLE.xsodata to expose the table in odata. This will be used in our view to display the uploaded data to the user.
- Add .xsaccess and .xsapp files.
- Share the project. Right Click on the project and go Team->Share
- Commit the project. Team -> Commit
- Activate the project. Team -> Activate
- Test the application (note: used Chrome as my browser):
- Enter HANA credentials:
- Choose file to upload. (note: file just has 1 column of data – see below)
- Press the “Upload” button
- There are a number of security concerns that are addressed in the SAP HANA Development Guide. One of these is Cross-site Request Forgery.
“To protect SAP HANA XS applications from cross-site request-forgery (XSRF) attacks, make sure you always set the prevent_xsrf keyword in the application-acess (.xsaccess) file to true, as illustrated in the following example:
{ "prevent_xsrf" : true }
The prevent_xsrf keyword prevents the XSRF attacks by ensuring that checks are performed to establish that a valid security token is available for given Browser session. The existence of a valid security token determines if an application responds to the client's request to display content. A security token is considered to be valid if it matches the token that SAP HANA XS generates in the backend for the corresponding session.”
To address this concern I added the { "prevent_xsrf" : true } to the .xsaccess file and the following code to the FileUpload.controller.js file as the standard FileLoader.upload() method does not seem to fetch and check the existence of the XSRF security token.
doFileUpload2 : function(oEvent)
{
var fileLoader = sap.ui.getCore().byId("FileLoader");
var fileName = fileLoader.getValue();
jQuery.sap.require("sap.ui.commons.MessageBox");
if (fileName == "" )
{
sap.ui.commons.MessageBox.show("Please choose File.", sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
}
else
{
var uploadUrl = "../Services/BatchFileUpload.xsjs?file_name="+fileName;
var formEle = jQuery.sap.domById("FileLoader");
var form = $(formEle).find("form")[0] ;
var fd = new FormData(form);
$.ajax({
url: uploadUrl,
type: "GET",
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", "Fetch");
},
success: function(data, textStatus, XMLHttpRequest) {
var token = XMLHttpRequest.getResponseHeader('X-CSRF-Token');
$.ajax({
url: uploadUrl,
type: "POST",
processData :false ,
contentType: false ,
data:fd,
beforeSend: function(xhr)
{
xhr.setRequestHeader("X-CSRF-Token", token);
},
success: function(data, textStatus, XMLHttpRequest)
{
var resptext = XMLHttpRequest.responseText;
jQuery.sap.require("sap.ui.commons.MessageBox");
sap.ui.commons.MessageBox.show(resptext, sap.ui.commons.MessageBox.Icon.INFORMATION, "Information");
sap.ui.getCore().byId("BatchTable").getModel().refresh();
},
error: function(data, textStatus, XMLHttpRequest)
{
sap.ui.commons.MessageBox.show("File could not be uploaded.", sap.ui.commons.MessageBox.Icon.ERROR, "Error");
}
});
}} ) ;
}
- The "Upload 2" button calls the above code.
Note if you try to use the first Upload button it will return an error as we have added the prevent_xsrf : true to the .xsaccess file.
Source: scn.sap.com
No comments:
Post a Comment