Hi 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.
<!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>
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;
}
});
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");
}
});
}} ) ;
}
}
});
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);
}
“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");
}
});
}} ) ;
}
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 |