SAP HANA- $.hdb stored procedure result set reading

The following example of usage of the SAP HANA XS $.hdb API shows how to connect to the SAP HANA DB and how to read values of the output declaed as table in  HANA Stored procedure.

The example stored procedure already deployed into hana db and it will return OUTPUT_TABLE as a result set

CREATE PROCEDURE "<<SCHEMA_NAME>>"."PROCEDURE_SALES_REPORT"(
            IN DISCOUNT INTEGER,
            OUT OUTPUT_TABLE "<<SCHEMA_NAME>>"."TT_SALES" )
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER 
AS

BEGIN

var1 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT
            FROM SCHEMA_NAME.REGION AS T1
            INNER JOIN
            SCHEMA_NAME.SALES AS T2
            ON T1.REGION_ID = T2.REGION_ID;

var2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT,                   T2.PRODUCT_NAME
            FROM :var1 AS T1
            INNER JOIN
            SCHEMA_NAME.PRODUCT AS T2
            ON T1.PRODUCT_ID = T2.PRODUCT_ID;

OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM( SALES_AMOUNT - 
            (SALES_AMOUNT * :DISCOUNT/ 100)) AS NET_AMOUNT,                 
            PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME
            FROM :var2 
            GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME;

END;

Syntax for Calling Stored procedure and reading data from the result set 


      var body = $.request.body.asString();
      var reqObj = JSON.parse(body);
      var action = reqObj.action;
      var discount = $.request.parameters.get('discount');
      var data=[];
      var rs;
   
                 var conn=$.hdb.getConnection({"sqlcc": "PROC_TEST::cred"});
    var pstm= conn.loadProcedure('<< scheme name >>', '<< procedure name >>');
    rs = pstm(discount);
    var rslength =   (rs["OUTPUT_TABLE"]).length;
       
            for(var i = 0 ; i<rslength-1;i++)
            {
      data.push
      ({
           "SALES_AMOUNT":rs["OUTPUT_TABLE"][i]["SALES_AMOUNT"],
           "NET_AMOUNT":rs["OUTPUT_TABLE"][i]["NET_AMOUNT"],
           "PRODUCT_NAME":rs["OUTPUT_TABLE"][i]["PRODUCT_NAME"]
         
       
      });
            }
conn.commit();
   
   
            $.response.setBody(JSON.stringify(data));
    

SAP HANA XS API for reading data

The following example shows how to use $.db interface to connect to SAP Hana Db and how to read data from db and how to response as json object.

The example values assumes that we have country data in DB .

COUNTRYID	COUNTRYNAME
1	India
2	USA
3	UK

/////if you have sqlcc configuration please use in the get Connection method.

      var conn = $.db.getConnection();

       var query = "SELECT COUNTRYID, COUNTRYNAME "SCHEMA"."table name";

       var pstmt = conn.prepareStatement(query);

       var rs = pstmt.executeQuery();

       while(rs.next())

   {        

       data.push

       ({

            "COUNTRYID":rs.getString(1),

            "COUNTRYNAME":rs.getString(2),

                     

       });

     

   }

Batch Insert example in SAP HANA

The following example of usage of the SAP HANA XS database API shows how to establish a connection with SAP HANA and how to use batch insert for loading bulk data into hana DB.

The example values assumes that we have to insert data into DB as a batch insert instead by record by record.

COUNTRYID	COUNTRYNAME
1	India
2	USA
3	UK

Syntax

HANA 

/////if you have sqlcc file please mentioned in the getConnection method

  

     var conn = $.db.getConnection();

     var st = conn.prepareStatement("insert into tablename(col1,col2) VALUES(?,?)");

     var BACTHSIZE = number of records which you want insert;

   ///////we can use batch only number of records are more than 1

     if(BACTHSIZE>1)

     {

     st.setBatchSize(BACTHSIZE);

     var i;

     for(i=0;i<BACTHSIZE;i++) 

     {

     st.setString(1,input[i].COUNTRYID);

     st.setString(2,input[i].COUNTRYNAME]);

    

     st.addBatch();

     }

     st.executeBatch();

     }

////if number of records are less than 1, we can assume only one record need to insert into db and i ////value should be zero

     else

     {

     st.setString(1,input[0].COUNTRYID);

     st.setString(2,input[0].COUNTRYNAME);

    

     }

     st.close();

     

     conn.commit(); 

     data.push({

        "data": "Record Inserted Successfully..!"

      });

       

      

  }

Calling procedure by using $.hdb

The following example of usage of the SAP HANA XS database API shows how to establish a connection withSAP HANA and return a result set from the specified procedure call.
The example code assumes that a procedure exists with the following signature:

CREATE PROCEDURE "DRACE".USP_TBLUSERS_INSERT(

     IN flavor VARCHAR, 
     IN quantity INTEGER,    
     OUT change DECIMAL)

Syntax: 

var data=[];

//if you don't have sqlcc file for your package, make sure getConnection method properties are empty
//ex:    var conn=$.hdb.getConnection();

 var conn=$.hdb.getConnection({ "sqlcc": "packagename::sqlccfilename"});

var pstm= conn.loadProcedure('Schemaname', 'Procedure Name'); 

var result = pstm(input1, input2);

   data.push({
         
           "status": result[output variable name]
        })
        conn.commit();