Introduction
When using Node.JS in SAP BTP Cloud Foundry, you may have the need to store/retrieve file attachments in your NodeJS Application (backend) and SAPUI5 (front end). There are several approaches, of these the main would be:
In this article/tutorial I will explain how to perform CRUD (Create, Read Update Delete) actions to handle the File Attachments using Node.JS in the backend and SAPUI5 on the front end.
Note: Some parts are missing, I assume you guys know your stuff and the scope of this article is to show how to write and retrieve the BLOB to / from the HANA DB
🙂
1. Create the BLOB table
In Database Explorer or HANA Studio, create a table, i.e.:
CREATE COLUMN TABLE "MY_SCHEMA"."TBL_ATTACHMENT"(
"ATTACHMENT_ID" INTEGER CS_INT NOT NULL,
"FILE_NAME" VARCHAR(256),
"FILE_DESC" VARCHAR(256),
"FILE_TYPE" VARCHAR(100),
"FILE_CONTENT_BIN" BLOB MEMORY THRESHOLD 1000
)
For this example we will also use a SEQUENCE, to generate the ID:
CREATE SEQUENCE "MY_SCHEMA"."TBL_ATTACHMENT_SEQ" START WITH 1001;
2. Implement the Node.JS backend Code:
# Note: I will not enter the whole code here, just what is needed to make it work, for more extensive samples please check the official documentation
https://github.com/SAP/node-hdb
We will use
Multer which allows temporarily storing the attachment into memory
in your nodejs code add:
const hana = require('@sap/hdbext');
const multer = require('multer');
const multerMemoryStore = multer({ storage: multer.memoryStorage() });
In this example, we will use two steps, first we write the File Information ( ID, File Name, Description, File Type), and then we will update the created record by writing the BLOB only
CREATE / UPDATE:
This function is used to create the sequential ID and to use it for the Update to upload the File Attachment
function getNewAttachmentId() {
return new Promise(function (resolve, reject) {
let q = `SELECT
"MY_SCHEMA"."TBL_ATTACHMENT_SEQ".NEXTVAL
AS ID FROM DUMMY`;
hana.createConnection(config.db, function (err, client) {
if (err) {
reject(err);
return;
}
client.prepare(q, (err, statement) => {
if (err) {
reject(err);
client.close();
return;
}
statement.exec([],
function (err, rs) {
if (err) {
reject(err);
client.close();
return;
}
resolve(rs[0].ID);
client.close();
});
});
});
});
}
The following function handles the creation of the record with the File Information, and will return, if successful, the Attachment Id that will be used for inserting the BLOB with the Binary File
app.post('/api/v1/postattachmentinfo', [Auth Stuff here], function (req, res) {
getNewAttachmentId()
.then((iAttachmentId) => {
let query = `INSERT INTO "MY_SCHEMA"."TBL_ATTACHMENT" ` +
`(
"ATTACHMENT_ID",
"FILE_TYPE",
"FILE_NAME",
"FILE_DESC"
) ` +
`VALUES (
?, ?, ?, ?)`;
let params = [
iAttachmentId,
req.body.FILETYPE,
req.body.FILENAME,
req.body.FILEDESCRIPTION
];
hana.createConnection(config.db, function (err, client) {
if (err) {
debug(err);
res.status(503).send(err);
return;
}
client.prepare(query, (err, statement) => {
if (err) {
debug(err);
res.status(400).send(err);
return;
}
statement.exec(params,
function (err, rs) {
if (err) {
debug(err);
res.status(400).send(err);
return;
}
console.log(rs);
res.status(200).send({ iAttachmentId });
});
});
});
})
.catch((err) => {
res.status(500).send("An error occurred. " + err);
});
});
The following code handles the update of the FIle Information record with the BLOB with the Binary File
app.put('/api/v1/attachfilebin/:attachmentId',
[Auth Stuff Here], multerMemoryStore.single('att'), (req, res) => {
// multerMemoryStore.single('att') :
// the parameter must have the same value as in the file uploader control property name,
// in this example the Name of the FileUploader control is 'att'
// if there is a mismatch, multer will throw an error
return new Promise(function (resolve, reject) {
let query = `UPDATE "MY_SCHEMA"."TBL_ATTACHMENT"
SET "FILE_CONTENT_BIN" =?
WHERE ATTACHMENT_ID=${req.params.attachmentId}`;
let buff = req.file.buffer;
hana.createConnection(config.db, function (err, client) {
if (err) {
res.status(400).send(err);
reject(err);
return;
}
client.prepare(query, (err, statement) => {
if (err) {
res.status(400).send(err);
reject(err);
return;
}
statement.exec([buff], function (err, rs) {
if (err) {
res.status(400).send(err);
reject(err);
return;
}
res.status(200).send("OK");
resolve();
});
});
});
});
});
READ:
app.get('/api/v1/getAttachmentbin/:attachmentId', function (req, res) {
return new Promise(function (resolve, reject) {
let q = `SELECT
"FILE_CONTENT_BIN" FROM "MY_SCHEMA"."TBL_ATTACHMENT"
WHERE "ATTACHMENT_ID" = '${req.params.attachmentId}' `;
hana.createConnection(config.db, function (err, client) {
if (err) {
res.status(400).send(err);
reject(err);
return;
}
client.prepare(q, (err, statement) => {
if (err) {
res.status(400).send(err);
reject(err);
client.close();
return;
}
statement.exec([],
function (err, rs) {
if (err) {
res.status(400).send(err);
reject(err);
client.close();
return;
}
let bufDec = rs[0].FILE_CONTENT_BIN;
res.status(200).send(bufDec);
resolve();
client.close();
});
});
});
});
});
DELETE:
Here is the code to delete the full record :
app.delete('/api/v1/removeattachment/:attachmentId',
[Auth Stuff Here], function (req, res) {
var q = `DELETE FROM "MY_SCHEMA"."TBL_ATTACHMENT" ` +
`WHERE "ATTACHMENT_ID" = ?`;
var params = req.params.attachmentId;
hana.createConnection(config.db, function (err, client) {
if (err) {
res.status(400).send(err);
return;
}
client.prepare(q, (err, statement) => {
if (err) {
res.status(400).send(err);
return;
}
statement.exec([params], function (err, rs) {
if (err) {
res.status(400).send(err);
return;
}
res.status(200).send("OK");
return;
});
});
});
});
Front End with SAPUI5 - FileUploader
You can use
this sample using the FileUploader component
The missing part here is the additional fields for the File Description etc..
XML View:
<mvc:View
controllerName="sap.ui.unified.sample.FileUploaderBasic.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="att" // this is the same name in multer parameters ... if they don't match you will get an error
uploadUrl="" // we will build the uploadUrl at runtime
httpRequestMethod="PUT"
maximumFileSize = 10 // more than this can crash the nodejs app
tooltip="Upload your file to the local server"
multiple=false
sendXHR=true,
useMultipart=true,
uploadComplete="handleUploadComplete"/>
<Button
text="Upload File"
press="doAddAttachmentInfo"/>
</l:VerticalLayout>
</mvc:View>
Controller
This is the code triggered by the upload button
doAddAttachmentInfo: async function (oEvent) {
let that = this;
let sFileName = sap.ui.getCore().getControl("fileUploader").getValue();
let sFileType = sap.ui.getCore().getControl("FileType").getFileType();
let sFileDescription = sap.ui.getCore().getControl("FileDescription").getValue(); // this control is not in the sample 🙂
if (sFileName === '') {
return;
}
let oFormData = {};
oFormData.FILETYPE = sFileType;
oFormData.FILENAME = sFileName;
oFormData.FILEDESCRIPTION = sFileDescription;
var token = getCSRFToken();
$.ajax({
type: "POST",
url: "/api/v1/attachmentinfo",
beforeSend: function (request) {
request.setRequestHeader('x-csrf-token', token);
},
data: oFormData,
success: function (res) {
let sAttachmentId = res.iAttachmentId;
that.doAddAttachFile(sAttachmentId);
},
error: function (jqXHR, textStatus, errorThrown) {
sap.m.MessageToast.show("Error: " + textStatus + " - " + errorThrown);
}
});
},
And the doAttachFile function that sends the PUT request
doAddAttachFile: async function() {
let oFileUploader = sap.ui.getCore().getControl("fileUploader");
let sUploadUrl = `/api/v1/attachfilebin`;
let token = getToken();
let headerParma = new sap.ui.unified.FileUploaderParameter();
headerParma.setName('x-csrf-token');
headerParma.setValue(token);
oFileUploader.addHeaderParameter(headerParma);
oFileUploader.setUploadUrl(sUploadUrl);
oFileUploader.upload();
},
And in case you need the getToken function...
function getToken() {
var token = null;
$.ajax({
url: '/api/v1/token',
type: "GET",
async: false,
beforeSend: function(xhr) {
xhr.setRequestHeader("X-CSRF-Token", "Fetch");
},
complete: function(xhr) {
token = xhr.getResponseHeader("X-CSRF-Token");
}
});
return token;
}
For simplicity, I omit the front end code to download or delete the record, you will just have to call a GET or DELETE request with the url you indicate in the backend Node.js application
