京东云_云主机管理平台_怎么样

数据库 虚拟云 浏览

小编:In this tutorial, we will create a simple SAPUI5 app to upload an Excel file (.xlsx) into SAP HANA XS Advanced table. We will create the multi-target-application that consists of database, NodeJS and SAPUI5 module. There are three main mo

In this tutorial, we will create a simple SAPUI5 app to upload an Excel file (.xlsx) into SAP HANA XS Advanced table. We will create the multi-target-application that consists of database, NodeJS and SAPUI5 module.

There are three main modules we need to create in SAP Web IDE SAP HANA XS Advanced: database, NodeJS and web module.

Create database module db. Inside db folder, create a folder called src. Inside src folder, create two folders data and sequence. Under data, create a file called zxlsfileupload_dummy.hdbtable with this content: COLUMN TABLE "ZXLSFILEUPLOAD_DUMMY" ( "SEQ_NO" INTEGER CS_INT NOT NULL COMMENT 'Running Sequence Number', "DATE" DATE CS_DAYDATE COMMENT 'Date', "COUNTRY_CODE" NVARCHAR(2) COMMENT 'Country Code', "COMPANY_CODE" NVARCHAR(10) COMMENT 'Company Code', "AMOUNT" SMALLDECIMAL COMMENT 'Amount', PRIMARY KEY ("SEQ_NO")) COMMENT 'ZXLSFILEUPLOAD_DUMMY' UNLOAD PRIORITY 5 AUTO MERGE ​ Under sequence, create a file called zxlsfileuploadSeqId.hdbsequence with this content: SEQUENCE "zxlsfileuploadSeqId" START WITH 100 RESET BY SELECT IFNULL(MAX("SEQ_NO"), 100) + 1 FROM "ZXLSFILEUPLOAD_DUMMY"​ Now create two files respectively under db folder, called data-model.cds and zxlsfileupload.cds. data-model.cds using from '../db/zxlsfileupload'; entity Base { key ID : Integer; }​ zxlsfileupload.cds namespace zxlsfileupload; @cds.persistence.exists entity Dummy { key SEQ_NO : Integer not null; DATE : Date; COUNTRY_CODE : String(2); COMPANY_CODE : String(10); AMOUNT : Decimal(15,2); };​ The final structure of db module would be like this: We are done with db module. You can go ahead to build it by doing a right click on the db folder and select Build.

Create NodeJS module srv. Inside srv folder, create folders: lib, lib > handlers, router, router > routes as shown below: We will define the oData custom exit on the "create" function for the oData zxlsfileupload_dummy.We’ll get the running number from the zxlsfileuploadSeqId and replace the data.SEQ_NO from UI5 with this running number. Go ahead to create index.js Inside lib > handler: /*eslint no-console: 0, no-unused-vars: 0, no-undef:0, no-process-exit:0, new-cap:0*/ /*eslint-env node, es6 */ "use strict"; const uuid = require("uuid/v4"); const cds = require("@sap/cds"); module.exports = function (entities) { const { catalog } = entities; const adm = "ODATASERVICEADMIN"; this.before("CREATE", "zxlsfileupload_dummy", async(User) => { console.log("** Create zxlsfileupload_dummy **"); const { data } = User; console.log(data); const dbClass = require(global.__base + "utils/dbPromises"); var client = await dbClass.createConnection(); let db = new dbClass(client); const statement = await db.preparePromisified( `SELECT \"zxlsfileuploadSeqId\".NEXTVAL AS SEQ_NO FROM DUMMY`); const dataResults = await db.statementExecPromisified(statement, []); console.log(dataResults[0].SEQ_NO); data.SEQ_NO = dataResults[0].SEQ_NO; return data; }); };​ These are the helper functions to get the user information: WhoAmI andUserInfo.  We are not really using it in the UI5 app, but I will just put it here for future use. You can call it from https://:/node/WhoAmIGo ahead to create myNode.js inside router > routes: /*eslint no-console: 0, no-unused-vars: 0, no-shadow: 0, newcap:0*/ /*eslint-env node, es6 */ "use strict"; var express = require("express"); var async = require("async"); module.exports = function () { var app = express.Router(); var userScope = null; app.get("/WhoAmI", (req, res) => { var scope = `${req.authInfo.xsappname}.Create`; if (req.authInfo && !req.authInfo.checkScope(scope)) { userScope = "usr"; } else { userScope = "adm"; } var result = JSON.stringify({ userScope: userScope }); res.type("application/json").status(200).send(result); }); app.get("/getSessionInfo", (req, res) => { var userContext = req.authInfo; var result = JSON.stringify({ userContext: userContext }); res.type("application/json").status(200).send(result); }); app.get("/userinfo", function(req, res) { let xssec = require("@sap/xssec"); let xsenv = require("@sap/xsenv"); let accessToken; let authWriteScope = false; let authReadScope = false; let controllerAdminScope = true; let userInfo = { "name": req.user.id, "familyName": req.user.name.familyName, "emails": req.user.emails, "scopes": [], "identity-zone": req.authInfo.identityZone }; function getAccessToken(req) { var accessToken = null; if (req.headers.authorization && req.headers.authorization.split(" ")[0] === "Bearer") { accessToken = req.headers.authorization.split(" ")[1]; } return accessToken; } accessToken = getAccessToken(req); let uaa = xsenv.getServices({ uaa: { tag: "xsuaa" } }).uaa; xssec.createSecurityContext(accessToken, uaa, function(error, securityContext) { if (error) { console.log("Security Context creation failed"); return; } console.log("Security Context created successfully"); userInfo.scopes = securityContext.scopes; console.log("Scope checked successfully"); }); return res.type("application/json").status(200).json(userInfo); }); return app; };​ If the path /node is called, then we route to myNode() function that we defined earlier.Inside router, create index.js: /*eslint-env node, es6 */ "use strict"; module.exports = (app, server) => { app.use("/node", require("./routes/myNode")()); };​ We will define the oData structure. Under srv folder, create my-service.cds: //tables using zxlsfileupload.Dummy as dummy from '../db/data-model'; service CatalogService { //** App ** // entity zxlsfileupload_dummy @( title: '{i18n>zxlsfileupload_dummyService}', Capabilities: { InsertRestrictions: {Insertable: true}, UpdateRestrictions: {Updatable: true}, DeleteRestrictions: {Deletable: true} } ) as projection on dummy; }​ We also need to create a folder called util under srv folder and create file dbPromises.js. /*eslint no-console: 0, no-unused-vars: 0, no-shadow: 0, new-cap: 0, dot-notation:0, no-use-before-define:0 */ /*eslint-env node, es6 */ "use strict"; module.exports = class { static createConnection() { return new Promise((resolve, reject) => { const xsenv = require("@sap/xsenv"); let options = xsenv.getServices({ hana: { tag: "hana" } }); var hdbext = require("@sap/hdbext"); options.hana.pooling = true; hdbext.createConnection(options.hana, (error, client) => { if (error) { console.log(error); reject(error); } else { resolve(client); } }); }); } constructor(client) { this.client = client; this.util = require("util"); this.client.promisePrepare = this.util.promisify(this.client.prepare); } preparePromisified(query) { console.log(query); return this.client.promisePrepare(query); } statementExecPromisified(statement, parameters) { statement.promiseExec = this.util.promisify(statement.exec); return statement.promiseExec(parameters); } loadProcedurePromisified(hdbext, schema, procedure) { hdbext.promiseLoadProcedure = this.util.promisify(hdbext.loadProcedure); return hdbext.promiseLoadProcedure(this.client, schema, procedure); } callProcedurePromisified(storedProc, inputParams) { return new Promise((resolve, reject) => { storedProc(inputParams, (error, outputScalar, ...results) => { if (error) { console.log(error); reject(error); } else { if (results.length < 2) { resolve({ outputScalar: outputScalar, results: results[0] }); } else { let output = {}; output.outputScalar = outputScalar; for (let i = 0; i < results.length; i++) { output[`results${i}`] = results[i]; } resolve(output); } } }); }); } };​ Finally update server.js with this content: /*eslint no-console: 0, no-unused-vars: 0, no-undef:0, no-process-exit:0*/ /*eslint-env node, es6 */ "use strict"; const port = process.env.PORT || 3000; const server = require("http").createServer(); const cds = require("@sap/cds"); //Initialize Express App for XSA UAA and HDBEXT Middleware const xsenv = require("@sap/xsenv"); const passport = require("passport"); const xssec = require("@sap/xssec"); const xsHDBConn = require("@sap/hdbext"); const express = require("express"); global.__base = __dirname + "/"; //logging var logging = require("@sap/logging"); var appContext = logging.createAppContext(); //Initialize Express App for XS UAA and HDBEXT Middleware var app = express(); //Compression app.use(require("compression")({ threshold: "1b" })); //Helmet for Security Policy Headers const helmet = require("helmet"); // ... app.use(helmet()); app.use(helmet.contentSecurityPolicy({ directives: { defaultSrc: 'self'"], styleSrc: 'self'", "sapui5.hana.ondemand.com"], scriptSrc: 'self'", "sapui5.hana.ondemand.com"] } })); // Sets "Referrer-Policy: no-referrer". app.use(helmet.referrerPolicy({ policy: "no-referrer" })); passport.use("JWT", new xssec.JWTStrategy(xsenv.getServices({ uaa: { tag: "xsuaa" } }).uaa)); app.use(logging.middleware({ appContext: appContext, logNetwork: true })); app.use(passport.initialize()); var hanaOptions = xsenv.getServices({ hana: { tag: "hana" } }); hanaOptions.hana.pooling = true; app.use( passport.authenticate("JWT", { session: false }), xsHDBConn.middleware(hanaOptions.hana) ); //CDS OData V4 Handler var options = { driver: "hana", logLevel: "error" }; //Use Auto Lookup in CDS 2.10.3 and higher //Object.assign(options, hanaOptions.hana, { // driver: options.driver //}); cds.connect(options); var odataURL = "/odata/v4/zxlsfileupload.CatalogService/"; // Main app cds.serve("gen/csn.json", { crashOnError: false }) .at(odataURL) .with(require("./lib/handlers")) .in(app) .catch((err) => { console.log(err); process.exit(1); }); // Redirect any to service root app.get("/", (req, res) => { res.redirect(odataURL); }); app.get("/node", (req, res) => { res.redirect(odataURL); }); //Setup Additonal Node.js Routes require("./router")(app, server); //Start the Server server.on("request", app); server.listen(port, function () { console.info(`HTTP Server: ${server.address().port}`); });​

To upload an Excel file, I am using the library from SheetJS. And I have done a little modification to meet our requirement.

文章来源:www.vmchk.com

 
你可能喜欢的: