Web form with HANA sqlscript stored procedure

[Private_Member_141614]

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.