EDUCAÇÃO E TECNOLOGIA

Using HANA DB BLOB fields to store/retrieve File Attachments using Node.JS and SAPUI5


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