HANA Deep Insert

[brad_smith5]

I have been very impressed with HANA and the ease in which you can expose an OData service for your entities and views. Since starting work on the platform I have found the need to store my entities using a deep insert. I thought surely this was possible, but like many I have been disappointed to find that this is not supported.

Currently the solution to this problem is to place the creates into a batch in the front end. My main issue with this is the parent Id is not returned to me to place into the child objects. I am then forced to place the child create in the success handler of the parent object create, losing the transaction functionality.

I would like to share my current solution to this shortcoming. It is a work in progress but the pattern is in place and functional. I have created a dynamic xsjs service that behaves in a way that you would expect from xsodata. Specific conventions must be met, but these can easily be customized to suit your scenario.

To get started, I have setup some basic tables with a parent / child relationship

 

DeepInsert.hdbdd

namespace dev;

@Schema : 'DEMO'



context "DeepInsert"

{

    entity "Parent"

    {

        key Id:Integer not null; 

        Description:String(50);

        Temperature:DecimalFloat;

        Timestamp:UTCTimestamp;

    }

    entity "Child"

    {

        key Id:Integer not null;  

        ParentId:Integer not null;

        Description:String(50);

    }

}

 

As I am using integers for my Id values I now need some sequences to cater for them.

 

SequenceParent.hdbsequence

 

schema= "DEMO";

start_with=1;

nomaxvalue=true;

minvalue=1;

cycles=false;

increment_by=1;

depends_on_table="dev::DeepInsert.Parent";

 

SequenceChild.hdbsequence

 

schema= "DEMO";

start_with=1;

nomaxvalue=true;

minvalue=1;

cycles=false;

increment_by=1;

depends_on_table="dev::DeepInsert.Child";

 

We now have our tables and a mechanism to create our Id values. To put this all together I have written an xsjs file. It is dynamic, relying on naming conventions to get the job done. It will work for any table in your application so long as you adhere to the following:

• Your Id column is an Integer with the name “Id”


• Your child foreign key is an Integer and named as a concatenation of the parent table name and “Id”


• The sequence name for each table must be a concatenation of “Sequence” and the Table name of the entity

• Your payload is JSON using the following structure:

{

	"Object":

	{

	    "Parent":{

	        "Id":-1,

	        "Description":"Deep Insert Parent Object",

	        "Temperature":25.2,

	        "Timestamp":20170511183353,

	        "Child":[{

	            "Id":-1,

	            "ParentId":-1,

	            "Description":"Deep Insert Child Object 1"

	        },

	        {

	            "Id":-1,

	            "ParentId":-1,

	            "Description":"Deep Insert Child Object 2"

	        }]

	    }

	}

}

• The element “Object” is required as the root level


• The entity name must match the Table name it is stored in


• The child entity name in the parent object must also match the table name of the child record. This must also be an array, even when only one entity is present

 

This is where the magic happens. DeepInsert.xsjs. The service uses reflection, recursion and naming conventions to provide the deep insert functionality. This will work for children, grandchildren and further down the line if need be.

The service parses the payload, reading all properties preparing a list of objects to place in a batch create. The Id values are generated in the preparation phase with the help of the defined sequences. The objects are listed in order with all keys in place so that no foreign key violations will occur during insert. Finally the objects are written to the database via sql query.

DeepInsert.xsjs

 

/*---------------------------------------------------------------------*

* Procedure: DeepInsert 

*----------------------------------------------------------------------*

* Author: Bradley Smith

*----------------------------------------------------------------------*

* Description: Deep Insert Pattern

*----------------------------------------------------------------------

Parameter: Object

Format:

{

	"Object":

	{

	    "Parent":{

	        "Id":-1,

	        "Description":"Deep Insert Parent Object",

	        "Temperature":25.2,

	        "Timestamp":20170511183353,

	        "Child":[{

	            "Id":-1,

	            "ParentId":-1,

	            "Description":"Deep Insert Child Object 1"

	        },

	        {

	            "Id":-1,

	            "ParentId":-1,

	            "Description":"Deep Insert Child Object 2"

	        }]

	    }

	}

}

*/



var objectsToCreate = [];

var detail = '';

function processObject(objectName, objectInstance, parentIdFieldName, parentId)

{

    try

    {

        //copy the object to prepare it for batch create

        var objectToCreate = JSON.parse(JSON.stringify(objectInstance));

        

        //get the new Id from relevant sequence

        var recordId = -1;

        var queryStr = 'select "dev::Sequence' + objectName + '".NEXTVAL as Id from dummy;';

        var conn = $.db.getConnection();

        var pstmt = conn.prepareStatement(queryStr);

        var rs = pstmt.executeQuery();

        while (rs.next()) {

               recordId = rs.getInteger(1);

        }

        rs.close();

        pstmt.close();

        conn.close();

        objectToCreate.Id = recordId;

        

        //detect and update parent id field

        if(parentIdFieldName && parentId)

        {

            objectToCreate[parentIdFieldName] = parentId;

        }

        

        var childObjectNames = [];

        var objectProperties = Object.getOwnPropertyNames(objectInstance);

        for(var objectPropertyIndex = 0;objectPropertyIndex < objectProperties.length; objectPropertyIndex++)

        {

            var propertyName = objectProperties[objectPropertyIndex];

            var propertyType = typeof objectInstance[propertyName];



            if(propertyType === 'object')

            {

                if(Array.isArray(objectToCreate[propertyName]))

                {

                    //relationship 1..*

                    childObjectNames.push(propertyName);

                    

                    //remove from the new object as it will prevent odata insert

                    delete objectToCreate[propertyName];   

                }

            }

        }



        //queue new object ready for batch create

        objectsToCreate.push({ "ObjectName": objectName, "Object": objectToCreate });

        

        //process child objects One To Many

        for(var childObjectNameIndex = 0; childObjectNameIndex < childObjectNames.length;childObjectNameIndex++)

        {

            var childObjectName = childObjectNames[childObjectNameIndex];

            var childObjectCollection =  objectInstance[childObjectName];

            for(var childObjectIndex = 0; childObjectIndex < childObjectCollection.length; childObjectIndex++)

            {

                var childObject = childObjectCollection[childObjectIndex];

                processObject(childObjectName, childObject, objectName + 'Id', objectToCreate.Id);

            }

        }

    }

    catch(e)

    {

        //catch all error condition

        $.response.status = $.net.http.INTERNAL_SERVER_ERROR;

        $.response.contentType = "application/json";

        $.response.setBody(e.toString());

    }

}



function createObjects()

{

    var conn = $.db.getConnection();

    try

    {

        var parentId = 0;

        

        for(var objIndex = 0;objIndex < objectsToCreate.length; objIndex++)

        {

            var fieldsStr = "";

            var valuesStr = "";

            

            var objectName = objectsToCreate[objIndex].ObjectName;

            var objectInstance = objectsToCreate[objIndex].Object;

            if(objIndex === 0)

            {

                parentId = objectInstance.Id;

            }



            var objectProperties = Object.getOwnPropertyNames(objectInstance);

            for(var objectPropertyIndex = 0;objectPropertyIndex < objectProperties.length;objectPropertyIndex++)

            {

                var propertyName = objectProperties[objectPropertyIndex];



                fieldsStr += '"' + propertyName + '"';

                valuesStr += '?';

                

                if(objectPropertyIndex < objectProperties.length - 1)

                {

                    fieldsStr += ',';

                    valuesStr += ',';

                }

            }

            

            var createQueryStr = 'INSERT INTO "dev::DeepInsert.';

            createQueryStr += objectName;

            createQueryStr += '" (';

            createQueryStr += fieldsStr;

            createQueryStr += ') values (';

            createQueryStr += valuesStr;

            createQueryStr += ');';

            

            

            var st = conn.prepareStatement(createQueryStr);

            

            //value loop to prevent sql injection 

            for(objectPropertyIndex = 0;objectPropertyIndex < objectProperties.length;objectPropertyIndex++)

            {

                propertyName = objectProperties[objectPropertyIndex];

                var propertyValue = objectInstance[propertyName];

                var propertyType = typeof objectInstance[propertyName];

                

                if(propertyType === 'string')

                {

                    st.setString(objectPropertyIndex + 1, propertyValue);

                }

                else

                {

                    st.setString(objectPropertyIndex + 1, propertyValue.toString());

                }

            }

            

            st.execute();

        }

       

        conn.commit();

        conn.close();

        

        $.response.status = $.net.http.OK; 

        $.response.contentType = "application/json";

        $.response.setBody(JSON.stringify({ "RESULT":"SUCCESS", "Id": parentId } ));

    }

    catch(e)

    {

        conn.rollback();

        conn.close();

        

        $.response.status = $.net.http.INTERNAL_SERVER_ERROR;

        $.response.contentType = "application/json";

        $.response.setBody(e.toString() + ' : ' + detail);

    }

}



try

{

    var reqParams = $.request.body.asString();

    reqParams = JSON.parse(reqParams);

    var object = reqParams.Object;



    //detect the parent object and begin processing

    var props = Object.getOwnPropertyNames(object);

    for(var i = 0;i < props.length;i++)

    {

        var parentObjectName = props[i];

        var parentObject = object[props[i]];

        var typeAttr = typeof object[props[i]];

        if(typeAttr === 'object')

        {

            //top level object detected. do not pass parent params as this is the top level

            processObject(parentObjectName, parentObject);

        }

    }



    createObjects();

}

catch(e)

{

     //catch all error condition

    $.response.status = $.net.http.INTERNAL_SERVER_ERROR;

    $.response.contentType = "application/json";

    $.response.setBody(e.toString());

}

 

Calling the service from UI5.

 

Instead of an OData create, we make a simple ajax call to the service. The authorization header is only required if calling from a remote system but included in the following example, also remember to handle your CORS appropriately

 

var sPayload = {

	"Object":

	{

	    "Parent":{

	        "Id":-1,

	        "Description":"Deep Insert Parent Object",

	        "Temperature":25.2,

	        "Timestamp":20170511183353,

	        "Child":[{

	            "Id":-1,

	            "ParentId":-1,

	            "Description":"Deep Insert Child Object 1"

	        },

	        {

	            "Id":-1,

	            "ParentId":-1,

	            "Description":"Deep Insert Child Object 2"

	        }]

	    }

	}

};



var sUrl = 'https://acct.hanatrial.ondemand.com/dev/DeepInsert.xsjs';

$.ajax({

	url:sUrl,

	type:'POST',

	data:JSON.stringify(sPayload),

	dataType:'json',

	contentType:'application/json',

	headers: {

		"Authorization": "Basic " + btoa('username:password')

	  },

	success: function(data) {

		alert(data.Id);

	},

	error: function(error) {

		alert(error.responseText ? error.responseText : error.statusText);

  }

});

 

We now have a dynamic deep insert that supports a single transaction. This can be easily customized to suit your own naming conventions and scenarios. For example using guids instead of Integers for Id fields.

 

It should be noted that Id values are generated whether the insert is successful or fails. These values may be wasted. Unfortunately this cannot be avoided with the current limitations.