Skip to Content
author's profile photo Former Member
Former Member

Manage multi-table insert using OData Service

Hi all,

I would like to understand how can I manage multi-table inserts using odata service.

Problem :

For example I have two tables :

1. Customer (Customer_ID as primary key)

2. Orders (Order_ID as primary key and Customer_ID as foreign key)

Customer Table has one to many relationship Order Table.

I would like to create OData Service where I can manage insert/update/delete transactions.

I have little idea that I can use "Associations" for joining to tables but I have not sure about following points:

1. Is "Associations" support CRUD operations?

2. How can I manage auto-generated primary key creations for Customer_ID and Order_ID?

Please advice.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Aug 18, 2014 at 11:53 AM

    Yes Associations are supported for CRUD operations. You should use a batch operation to insert the parent/child/association link record all in one batch. You use content-id ($ and then the content-id value) as the placeholder for the auto-generated primary keys in the parent/child records.

    Unfortunately you can't use the UI5 OData model for content-id batch operations. You have to hand code the request body. Here is an example of this from the upcoming Next Steps in HANA Development openSAP course which will launch in October.

    The XSODATA Service Definition:

    service  { 
      
     "sap.hana.democontent.epmNext.data::EPM.MasterData.BusinessPartner"
      as "BusinessPartners"
      navigates ("ToAddresses" as "AddRef")
       create events(before "sap.hana.democontent.epmNext.services:businessPartnersAddresses.xsjslib::bp_create_before_exit");
    
     "sap.hana.democontent.epmNext.data::EPM.MasterData.Addresses"
      as "Addresses"
      create events(before "sap.hana.democontent.epmNext.services:businessPartnersAddresses.xsjslib::address_create_before_exit");
    
     association "ToAddresses" principal "BusinessPartners"("ADDRESSES.ADDRESSID")
       multiplicity "1" 
       dependent "Addresses"("ADDRESSID") multiplicity "1" 
       over "sap.hana.democontent.epmNext.data::EPM.MasterData.Addresses" 
               principal("ADDRESSID") dependent ("ADDRESSID")
               update using "sap.hana.democontent.epmNext.services:businessPartnersAddresses.xsjslib::assocation_create_exit";
    }  
    

    The XSJS Exit logic:

    $.import("sap.hana.democontent.epmNext.services", "session");
    var SESSIONINFO = $.sap.hana.democontent.epmNext.services.session;
    
    
    /**
    @param {connection} Connection - The SQL connection used in the OData request
    @param {beforeTableName} String - The name of a temporary table with the single entry before the operation (UPDATE and DELETE events only)
    @param {afterTableName} String -The name of a temporary table with the single entry after the operation (CREATE and UPDATE events only)
     */
    
    
    function bp_create_before_exit(param) {
    
    
      let after = param.afterTableName;
      var pStmt;
      try {
    
    
      pStmt = param.connection.prepareStatement('select "sap.hana.democontent.epmNext.data::businessPartnerId".NEXTVAL from dummy');
      var rs = pStmt.executeQuery();
      var PartnerId = '';
      while (rs.next()) {
      PartnerId = rs.getString(1);
      }
      pStmt.close();
    
    
      pStmt = param.connection.prepareStatement('update "' + after
      + '" set PARTNERID = ?,' + 
       '  PARTNERROLE = ?, ' +
       '  "HISTORY.CREATEDBY.EMPLOYEEID" = ?,' +
       '  "HISTORY.CHANGEDBY.EMPLOYEEID" = ?,' +
       '  "HISTORY.CREATEDAT" = now(),' + 
       '  "HISTORY.CHANGEDAT" = now(),' + 
       '  "CURRENCY" = ?');
      pStmt.setString(1, PartnerId);
      pStmt.setString(2, '01');
      pStmt.setString(3, '0000000033');
      pStmt.setString(4, '0000000033');
      pStmt.setString(5, 'EUR');
    
      pStmt.execute();
      pStmt.close();
    
    
    
      }
      catch (e) {
    
    
      }
    
    
    }
    
    
    function address_create_before_exit(param) {
    
    
      let after = param.afterTableName;
    
      var pStmt;
      try {
    
      pStmt = param.connection.prepareStatement('select "sap.hana.democontent.epmNext.data::addressId".NEXTVAL from dummy');
      var rs = pStmt.executeQuery();
      var AddressId = '';
      while (rs.next()) {
      AddressId = rs.getString(1);
      }
      pStmt.close();
    
    
      pStmt = param.connection.prepareStatement('update "' + after
      + '" set "ADDRESSID" = ?,' +
       'ADDRESSTYPE = ?,' +
       '"VALIDITY.STARTDATE" = TO_DATE(' + "'2000-01-01', 'YYYY-MM-DD'),"  +
       '"VALIDITY.ENDDATE" = TO_DATE(' + "'9999-12-31', 'YYYY-MM-DD')" );
      pStmt.setString(1, AddressId);
      pStmt.setString(2, '02');
      pStmt.execute();
      pStmt.close();
    
      }
      catch (e) {
    
    
      }
    
    
    }
    
    
    /**
    @param {connection} Connection - The SQL connection used in the OData request
    @param {principalTableName} String - The name of a temporary table with the entity type at the principal end of the association
    @param {dependentTableName} String -The name of a temporary table with the dependent entity type
     */
    
    
    
    
    function assocation_create_exit(param){
      let princ = param.principalTableName;
      let dep = param.dependentTableName;
    
    
    
    
      var pStmt = param.connection.prepareStatement('select * from "' + princ + '"');
      var Principal = SESSIONINFO.recordSetToJSON(pStmt.executeQuery(), 'Details');
      pStmt.close();
    
      var pStmt = param.connection.prepareStatement('select * from "' + dep + '"');
      var Dependent = SESSIONINFO.recordSetToJSON(pStmt.executeQuery(), 'Details');
      pStmt.close();
    
      $.trace.debug(JSON.stringify(Principal));
      $.trace.debug(JSON.stringify(Dependent));
      var pStmt = param.connection.prepareStatement('update "SAP_HANA_EPM_NEXT"."sap.hana.democontent.epmNext.data::EPM.MasterData.BusinessPartner" ' +
         ' SET "ADDRESSES.ADDRESSID" = ? WHERE "PARTNERID" = ? ');
      pStmt.setString(1, Dependent.Details[0].ADDRESSID);
      pStmt.setString(2, Principal.Details[0].PARTNERID);
      pStmt.execute();
      pStmt.close();
    
    }
    

    And the UI5 controller event logic to build the request:

    callCreateServiceBackup : function() {
    
    
    
    
    var oModel = sap.ui.getCore().byId("idodataDeep").getController().oModel;
    var oBusinessPartner = {};
    oBusinessPartner.PARTNERID = "0000000000";
    oBusinessPartner.EMAILADDRESS = sap.ui.getCore().byId("email").getValue();
    oBusinessPartner.COMPANYNAME = sap.ui.getCore().byId("CompanyName").getValue();
    
    
    var oAddress = {};
    oAddress.ADDRESSID = "0000000000"; 
    oAddress.CITY = sap.ui.getCore().byId("City").getValue();
    
    
    var oLink = {};
    oLink.uri = "$2";
    
    
     var xhr = new XMLHttpRequest();
    
     xhr.open("POST", '/sap/hana/democontent/epmNext/services/businessPartnersAddresses.xsodata/$batch', true);
    
     var token = getCSRFToken();
         xhr.setRequestHeader("X-CSRF-Token", token);
    
     xhr.setRequestHeader("Accept", 'application/json'); 
     xhr.setRequestHeader("Content-Type", 'multipart/mixed;boundary=batch'); 
     xhr.setRequestHeader("DataServiceVersion", '2.0'); 
     xhr.setRequestHeader("MaxDataServiceVersion", '2.0');
    
     var body = '';
    
     body += '--batch' + '\n'; 
         body +='Content-Type:multipart/mixed;boundary=changeset' + '\n';     
         body +='Content-Transfer-Encoding:binary'+ '\n';
         body +='\n';
         
         body += '--changeset' + '\n';     
         body += 'Content-Type:application/http' + '\n';
     body += 'Content-Transfer-Encoding:binary\n';     
     body += 'Content-ID: 1\n';
         body +='\n';
         
     body += 'POST BusinessPartners HTTP/1.1\n';
     body += "Content-Type: application/json\n";
     var jsonBP = JSON.stringify(oBusinessPartner);
     body += "Content-Length:" + jsonBP.length +'\n';
         body +='\n';
     body += jsonBP + '\n';
         body += '--changeset' + '\n';
    
         body += 'Content-Type:application/http' + '\n';
     body += 'Content-Transfer-Encoding:binary\n';     
     body += 'Content-ID: 2\n';
         body +='\n';
    
     body += 'POST Addresses HTTP/1.1\n';
     body += "Content-Type:application/json\n";
     var jsonAdd = JSON.stringify(oAddress);
     body += "Content-Length:" + jsonAdd.length +'\n';
         body +='\n';
    
     body += jsonAdd + '\n';
         body += '--changeset' + '\n';     
    
         body += 'Content-Type:application/http' + '\n';
     body += 'Content-Transfer-Encoding:binary\n';
         body +='\n';
         
     body += 'PUT $1/$links/AddRef HTTP/1.1\n';
     body += "Content-Type:application/json\n";
     var jsonLink = JSON.stringify(oLink);
     body += "Content-Length:" + jsonLink.length +'\n';
         body +='\n';
         
     body += jsonLink + '\n';
    
         body += '--changeset' + '--\n';     
         body +='\n';
        
     body += '--batch' + '--\n';   
         
             xhr.onload = function() { }
     xhr.send(body);
     alert('Business Partner created');
    
    
    
    
    }
    
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 18, 2014 at 12:30 PM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Srihari,

      1)===>>>Multiple table CRUD operations are not possible, but we can give read operations for UI5 and we can able to create multiple table by using NAVIGATE and ASSOCIATION........and i was refer many blocks for CRUD operations but no solutions so i decided and created one procedure which is for CREATE / UPDATE procedure .the given procedure is execute both CREATE and UPDATE

      operations i was tested in ODATA its working fine but conditions should be based on primary keys and related bussiness logic.

      2) CREATE/UPDATE Procedure for ODATA SERVICES?????

      Pr_Create_Update procedure

      PROCEDURE "shema"."folder.subfolder.procedures::prcedurename_create_update" (

      IN row "catalog table which you want",

      OUT error "table_type for error message::error")

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER

      DEFAULT SCHEMA "schema name"

      AS

      BEGIN

      /*****************************

      Description : This proc will be called from the OData to

      Update Master Vehicle Details from UI

      *****************************/

      DECLARE i_a NVARCHAR(20);

      DECLARE i_b NVARCHAR(20);

      DECLARE i_c NVARCHAR(30);

      DECLARE i_d NVARCHAR(3);

      DECLARE i_e NVARCHAR(3);

      DECLARE I_f NVARCHAR(3);

      DECLARE i_a_COUNT nvarchar;

      DECLARE i_b_COUNT nvarchar;

      DECLARE EXIT HANDLER FOR SQLEXCEPTION

      BEGIN

      error = select ::SQL_ERROR_CODE as http_status_code,

      ::SQL_ERROR_MESSAGE as error_message,

      'SQL EXCEPTION' as detail from dummy;

      END;

      SELECT a,b,c,d,e,f into i_a,i_b,i_c,i_d,i_e,i_f FROM :row;

      select count(*) into i_a_COUNT from "table name" where a = :i_a;

      select count(*) into i_b_COUNT from "table name" where b = :i_b;

      if( (:i_a_COUNT = 0 and :I_b_COUNT > 0) or (:I_a_COUNT > 0 and :I_b_COUNT = 0)) then

      error = select 500 as http_status_code,

      'Invalid Input' as error_message,

      'Invalid a number/b Number' as detail from dummy;

      elseif(:I_a_COUNT = 0 and :I_b_COUNT = 0) then

      INSERT INTO "table name "

      ("a","b","c","d","e","f") VALUES

      (I_a, i_b,i_c,i_d,i_e,i_f);

      elseif( :I_a_count > 0 and :I_b_COUNT > 0) then

      UPDATE "schema name"."folder.subfolder::table name" SET "a" =:i_a,i_b,i_c,i_d,i_e,i_f WHERE a = :I_a AND b = :I_b;

      end if;

      END;



      thanks & regards,

      SUNNY

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.