Skip to Content
Author's profile photo Former Member

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.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.