Web form with HANA sqlscript stored procedure

In continuation of previous example (see HCP Web form with HANA XSJS backend)
I hope to show usage of stored procedure in order to avoid SQL injection by moving INSERT to stored procedure. Versions respectively HANA 1.00.122.04.1478575636, HCP 1.120.14 and web ide Version: 170216.

From SAP HANA Web-based Development Workbench, navigate to Catalog to create procedure using HANA sqlscript for your purpose

drop procedure "ACTUALS"."actuals.procedures::updateContacts"; create PROCEDURE "ACTUALS"."actuals.procedures::updateContacts"( IN system nvarchar(3), IN client nvarchar(3), IN GUID nvarchar(32), IN FULL_NAME nvarchar(80), IN ORGANIZATION nvarchar(80), IN EMAIL nvarchar(80), IN PHONE nvarchar(80), IN NOTES nvarchar(1024), OUT ev_result nvarchar(80) ) language sqlscript SQL SECURITY INVOKER DEFAULT SCHEMA ACTUALS AS lv_lines integer := 0; BEGIN INSERT INTO ACTUALS.CONTACTS values (system, client, GUID , FULL_NAME , ORGANIZATION, EMAIL, PHONE, NOTES) ; SELECT ::ROWCOUNT INTO lv_lines FROM DUMMY; IF :lv_lines > 0 then ev_result = '1'; END IF; END;

From SAP HANA Web-based Development Workbench, navigate to Editor to call procedure using $.hdb loadProcedure

var Name = $.request.parameters.get("NAME"); var NameObj = JSON.parse(Name); var Org = $.request.parameters.get("ORG"); var OrgObj = JSON.parse(Org); var Email = $.request.parameters.get("EMAIL"); var EmailObj = JSON.parse(Email); var Phone = $.request.parameters.get("PHONE"); var PhoneObj = JSON.parse(Phone); var Notes = $.request.parameters.get("NOTES"); var NotesObj = JSON.parse(Notes); var conn = $.hdb.getConnection(); var sysuuid = conn.executeQuery( 'SELECT SYSUUID FROM DUMMY;' ); var guid = JSON.stringify(sysuuid); var replace2 = "{"0":{"SYSUUID":["; var replace3 = "]}}"; var replace4 = /,/gi; guid = guid.replace(replace2, ''); guid = guid.replace(replace3, ''); guid = guid.replace(replace4, ''); var trun = 32; var guid32 = guid.substring(0,trun); var proc = conn.loadProcedure("ACTUALS", "actuals.procedures::updateContacts"); var results = proc('HDB', '100', guid32, NameObj, OrgObj, EmailObj, PhoneObj, NotesObj); var procstatus = results.ev_result; conn.commit(); $.response.setBody(procstatus);

From internet browser test connectivity and results

From SAP HANA Web-based Development Workbench, navigate to Catalog in order to Confirm record(s) shown in HANA DB

Personally I have really enjoyed working with SAP HANA Cloud Platform and very pleased with new functionality.