cancel
Showing results for 
Search instead for 
Did you mean: 

Manage multi-table insert using OData Service

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (2)

Answers (2)

sreehari_vpillai
Active Contributor
0 Kudos

This message was moderated.

0 Kudos

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

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

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');

}

Former Member
0 Kudos

Hi Thomas,

I would really appreciate your response.

One thing is I am not using UI5. I have Java based application where I am using OData4J to call OData Services.

I am not clear about content-id approach could you please provide more insight on the idea how exactly content-id can be utilized for Insert/Update/Delete?

- Dharmesh

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

>One thing is I am not using UI5.

Neither am I in the example I provided.

>I am not clear about content-id approach could you please provide more insight on the idea how exactly content-id can be utilized for Insert/Update/Delete?

I provided the source code example. Notice in the URLs and the body of the content I don't specify keys for my tables. Instead I use $1 and $2.  In the header of each operation I set a content-id:

  1. body += 'Content-ID: 1\n'

On the server side the $1 and $2 will be replaced by whatever values are generated during the service exit (the XSJSLIB code) processing. There is nothing special that you have to do on the server side or in your XSJSLIB processing to use this functionality.

Former Member
0 Kudos

Hi Thomas,

As you have mentioned above I can utilize Content-ID but for that you have to use XSJS to get benefit of XSJSLIB on server side to call

pStmt = param.connection.prepareStatement('select "sap.hana.democontent.epmNext.data::businessPartnerId".NEXTVAL from dummy'); 


my problem is I am using JAVA and I am implement entire application using OData Service for retrieve ,insert , update, delete. Just to get next sequence value I don't want to use JDBC.


Is there a way I can call hana sequence through OData Service so I can create one Service where I will call hana sequence get the next value as result when I can assign to parent_id likewise you have done using

pStmt = param.connection.prepareStatement('select "sap.hana.democontent.epmNext.data::businessPartnerId".NEXTVAL from dummy'); 


or any other efficient way to do this.


Please advice.


thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

I used XSJS becuase I am using the XSODATA service.  You say you are creating your own OData service using Java, right?  Why not just call the sequence via JDBC just like you are doing any reads or inserts via JDBC?  I don't understand why you want to separate that out into a separate service.

Former Member
0 Kudos

Hi Tomas,

I think have created some confusion here and I am sorry about that.

I am trying to do something like this :

1. Create OData Service with the Association with parent/child table.

service namespace "abc.xyz.service" {
  "ZCUSTSEARCH"."TBL_PERSON" as "PersonBean" navigates ("Address" as "AddressBean");
  "ZCUSTSEARCH"."TBL_ADDRESS" as "AddressBean";
  association "Address" principal "PersonBean"("PERS_ID")
  multiplicity "1" dependent "AddressBean"("PERS_ID") multiplicity "*";
}

2. call this service from Java code to insert record.

In order to insert record I want to generate auto-increment value for PERS_ID (parent table primary key) and ADDRESS_ID (child table primarykey) for that I have created two sequences (person_id_seq  and address_id_seq) in hana.

Now, before inserting records in person and address table I want generate values for PERS_ID and ADDRESS_ID from my java code and for that I am thinking I don't want to use JDBC if I can do it using any other approach (like creating OData service just to call sequence but it seems to me not possible)  Or Is it possible instead of doing it in java side I can do it directly in XSODATA service code?

Regards,

Dharmesh

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

>Now, before inserting records in person and address table I want generate values for PERS_ID and ADDRESS_ID from my java code


Why would you want to generate the IDs within the Java code?  Why not let the XSODATA service generate the IDs?


>Or Is it possible instead of doing it in java side I can do it directly in XSODATA service code?

That's exactly what the example I provided above is doing.

Former Member
0 Kudos

Hi Thomas,

I think now I understand what exactly you are suggesting in your above example.

One more request, Is it possible for you to also provide me table definition of BusinessPartner and Addresses?

I would like to start with you example and than I will start on my complex OLTP requirement.

Thank you

Dharmesh

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

This example is based upon SHINE - so this is content you can get and install into your own system:

http://help.sap.com/hana/SAP_HANA_Interactive_Education_SHINE_en.pdf

That's probably the best way to get those tables.

Former Member
0 Kudos

Hi Thomas,

Thank you for sending me this details.

I have prepared all the necessary artifacts on server side such as xsjslib, xsodata, tables etc.

In order to test my implementation I would need your help. I want to test it from postman and for that I need to create body to submit request. In your UI5 controller event logic your are building request in body variable. Could you please provide me how the body variable looks like after you build complete request that will help me a lot.

Thanks in advance.

- Dharmesh

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

As I'm just concatenating body as a string, what you see in the code is exactly what the body looks like. But if it helps, I grabbed the raw Request body from the Chrome Developer Tools when running the UI5 page.  Here that is:

  1. --batch Content-Type:multipart/mixed;boundary=changeset Content-Transfer-Encoding:binary --changeset Content-Type:application/http Content-Transfer-Encoding:binary Content-ID: 1 POST BusinessPartners HTTP/1.1 Content-Type: application/json Content-Length:82 {"PARTNERID":"0000000000","EMAILADDRESS":"test@sap.com","COMPANYNAME":"Test Comp"} --changeset Content-Type:application/http Content-Transfer-Encoding:binary Content-ID: 2 POST Addresses HTTP/1.1 Content-Type:application/json Content-Length:45 {"ADDRESSID":"0000000000","CITY":"Test City"} --changeset Content-Type:application/http Content-Transfer-Encoding:binary PUT $1/$links/AddRef HTTP/1.1 Content-Type:application/json Content-Length:12 {"uri":"$2"} --changeset-- --batch--
Former Member
0 Kudos

This message was moderated.

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

Have you turned on trace to see what is processing on the server request?  Have you set a break-point in the XSJS exit and walked through the processing?

Former Member
0 Kudos

Hi Thomas,

I don't have previllages to to set trace. and I am setting break-point in the XSJS exit but when I hit my service from postman I am not reaching into XSJSLIB break point.

- Dharmesh

Former Member
0 Kudos

Hi Thomas,

I tried to put break-point in my XSJSLIB file and when refresh my request and try to debug it my request is not coming into XSJSLIB file where I have set my break-point even if I select correct session-id while debug.

Please provide your thoughts.

-Dharmesh

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

That would seem to indicate that the exit isn't even being called. The trace would tell you that for sure. I strongly suggest trying to get access to the trace to see what messages are being produced by the XSOData runtime.

Former Member
0 Kudos

Hi Thomas,

I am asking my admin to provide me access , in order to do that , I am going to asking to provide me access for trace which in Configuration and Monitoring --> open Administration --> Trace Configuration.

Please suggested me what type of trace I should ask him to enable for me from the Trace Configuration Tab, I see Database Trace, User-specific Trace, End-to-End Traces, SQL Trace, Performance Trace, Expensive Statements Trace and Kernel profiler and also suggest me what type of role they should assign me?

Sorry I am asking too much !!!

- Dharmesh

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

You need database trace.  Then you should search for xsa:<your package> and set the trace specific to this application package.

Former Member
0 Kudos

Hi Thomas,

I tried to enable trace as you have suggested. I am able to see trace file of whatever I do in SQL but still I am not able to see trace log of XSODATA.

Any thing I am missing here?

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

I'm not sure what you did or what you are seeing/not seeing; so its impossible for me to comment. I can say that if you activate the database trace for xsa:<your package path>, you will see all kinds of details from the XSODATA framework.  You will see the startup, query, exit processing, etc.

Former Member
0 Kudos

Hi Thomas,

After long time of effort I have decided break it down my task into two

1. test whether XSJSLIB code is working properly

2. test it with batch option

I have made some progress on step 1. I am trying to insert record in Person Table and good thing is I am able to debug my XSJSLIB and sequence is getting generated and record is also getting inserted but not correct values.

Now let me explain about but part of it.

Request body is :

{

  "PERS_ID":"0000000000",

  "FIRSTNAME":"test from service",

  "LASTNAME":"Test from Service",

  "E_MAIL":"testfromservice@gmail.com"

}


XSJSLIB exit code :


function person_create_before_exit(param) { 

     let after = param.afterTableName; 

      var pStmt; 

      try { 

       pStmt = param.connection.prepareStatement('select "ZCUSTSEARCH"."PERSON_ID_SEQ".NEXTVAL from dummy'); 

       var rs = pStmt.executeQuery(); 

       var person_id = ''; 

       while (rs.next()) { 

       person_id = rs.getString(1); 

       } 

       pStmt.close(); 

    

      

       pStmt = param.connection.prepareStatement('update "' + after 

         //  + '" set PERS_ID = "' + person_id

          // + ' " ,' + 

       + '" set "PERS_ID" = ?,' + 

            '  FIRSTNAME = ?, ' + 

            '  LASTNAME = ?,' + 

            '  E_MAIL = ?');

       pStmt.setString(1, person_id); 

       pStmt.setString(2, 'DHARMESH'); 

       pStmt.setString(3, 'PUROHIT'); 

       pStmt.setString(4, 'DHARMESH.PUROHIT@GMAIL.COM'); 

       pStmt.execute();

       pStmt.close(); 

      } 

      catch (e) {

    

      } 

    }

Now issue is when I insert record sequence is getting generated properly but while inserting record other values are not getting inserted what I have in request body. I am seeing values which I have in my xsjslib code :  which is as following

      pStmt.setString(1, person_id); 

       pStmt.setString(2, 'DHARMESH'); 

       pStmt.setString(3, 'PUROHIT'); 

       pStmt.setString(4, 'DHARMESH.PUROHIT@GMAIL.COM');

any idea what am I missing?

Regards,

Dharmesh

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

>Now issue is when I insert record sequence is getting generated properly but while inserting record other values are not getting inserted what I have in request body. I am seeing values which I have in my xsjslib code

Of course you are - you are changing the values in the exit to these new values.  What would you expect to happen?

Former Member
0 Kudos

Great, I am passing values from the request body

{

  "PERS_ID":"0000000000",

  "FIRSTNAME":"test from service",

  "LASTNAME":"Test from Service",

  "E_MAIL":"testfromservice@gmail.com"

}

I want to insert that values in Person Table expect PERS_ID. PERS_ID I am generating in exit which need to be inserted along with other values which are in request body.

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

So then only update the PERS_ID in the after table.  No reason to change any of the other values if you want what is passed into the service. The XSODATA framework will already use any of the input values from the service request unless you override the value in the after table.

Former Member
0 Kudos

Hi Thomas,

Thank you I did what you have suggested and I am able to insert record.

Now I am working on batch request.

Request header in Postman :

Authorization : Basic ZHAxMTMxNzpBcXVlbnQwNQ==

Content-Length : 673

X-CSRF-Token : Fetch

DataServiceVersion : 2.0

MaxDataServiceVersion : 2.0

Content-Type : multipart/mixed; boundary=batch

Request body in Postman:

--batch  

Content-Type:multipart/mixed; boundary=changeset

Content-Transfer-Encoding:binary

      

--changeset

Content-Type:application/http

Content-Transfer-Encoding:binary

Content-ID: 1

      

POST PersonBean HTTP/1.1

Content-Type: application/json

{"PERS_ID":"0000000000","FIRSTNAME":"test from service","LASTNAME":"Test from Service","E_MAIL":"testfromservice@gmail.com"}

Content-Length: 500 

--changeset 

Content-Type:application/http

Content-Transfer-Encoding:binary    

Content-ID: 2

POST AddressBean HTTP/1.1

Content-Type:application/json

{"ADDRESS_ID":"0000000000","ADDRESS_TEXT1":"Test address1","ADDRESS_TEXT2":"Test address2","CITY":"Test city","STATE":"Test state","COUNTRY":"Test country","ZIP":"Test zip","PERS_ID":"1"}

Content-Length:500

--changeset--      

     

--batch--

When I hit this request I am getting following error

--2A9AD779874CC08DD7FE41139044E60B0

Content-Type: application/http

Content-Length: 302

content-transfer-encoding: binary

HTTP/1.1 400 Bad Request

Content-Type: application/xml;charset=utf-8

Content-Length: 208

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<error

    xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

    <code/>

    <message xml:lang="en-US">Invalid multipart entry.</message>

</error>

--2A9AD779874CC08DD7FE41139044E60B0--

In addition to this I am not sure how to get X-CSRF-Token in postman.

Please advice.

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

>not sure how to get X-CSRF-Token in postman.

You have to perform a GET request against the service or another service in the same package in order to retrieve the CSRF Token.

Former Member
0 Kudos

Hi Thomas,

I am trying it with the get request first to get X-CSRF Token. when I hit the GET request I am getting 200 ok response code but in response header

I only see following headers:

  • cache-control →no-cache
  • content-encoding →gzip
  • content-language →en-US
  • content-length →675
  • content-type →application/xml;charset=utf-8
  • expires →Thu, 01 Jan 1970 00:00:00 GMT

I am not getting X-CSRF Token

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

Be sure to send the X-CSRF-Token header with the value Fetch. Then you should get a CSRF token back in the response header.

Former Member
0 Kudos

Hi Thomas,

Yes, I tried X-CSRF-Token = Fetch and I am not getting X-CSRF-Token is there anything else I need to check?

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

Nope that's what is required.  Is you get request not requiring authentication perhaps? It must require authentication to generate an CSRF token.

Former Member
0 Kudos

Yes, I am doing basic authentication for my GET request so my request headers are as following:

Authorization = Basic ZHAxMTMxNzpBcXVlbnQwNQ==

x-csrf-token = Fetch

leigh_mason
Employee
Employee
0 Kudos


Hey Thomas,

This is a bit late, but what I've found interesting is that I have a similar requirement at a customer in Australia working with Microsoft package System.Data.Services.Client ( .net application forming up the odata calls ) where they cannot create the deep entity structure like we do in say, a Fiori app with JSON.

When the call hits the Gateway ( SP08 ABAP 740 ) it ALWAYS fails, I've been trying to find a definitive answer on Content ID reference support in $batch requests, but this code from standard SAP ( below ) tells me it's not yet supported as of SP08.

I'm trying to hunt down an official statement regarding this functionality as it's obviously valuable in some scenarios.  Can you add any more info here?

Cheers

Leigh

/IWFND/CL_SODATA_PROCESSOR

METHOD check_content_reference.



 
DATA: lv_char                TYPE char1,

        lv_path_translated    
TYPE string,

        lx_mgw_busi_exception 
TYPE REF TO /iwfnd/cx_mgw_busi_exception.


* PATH_TRANSLATED contains '$' as first character

  lv_path_translated
= io_request->get_header_field( if_http_header_fields_sap=>path_translated ).

  lv_char
= lv_path_translated.

 
IF lv_char <> '$'. EXIT. ENDIF.


* Create and raise a Business Exception

 
TRY.

     
CREATE OBJECT lx_mgw_busi_exception

       
EXPORTING

          textid                
= /iwfnd/cx_mgw_tech_exception=>rfc_system_error

          remote_error_unlimited
= 'Referencing requests in a changeset not supported'. "#EC NOTEXT



     
RAISE EXCEPTION lx_mgw_busi_exception.

   
CATCH /iwfnd/cx_mgw_busi_exception INTO lx_mgw_busi_exception.

      raise_business_error
( lx_mgw_busi_exception ).

 
ENDTRY.


ENDMETHOD.

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

This whole thread is about XSODATA services in HANA not NetWeaver Gateway services. My comments have nothing to do with support for batch operations in NetWeaver Gateway. I couldn't begin to comment on features of NetWeaver Gateway as that is a completely different product than what I cover.

leigh_mason
Employee
Employee
0 Kudos

Yes I know, should have probably put this into the NWGW forum, it's interesting that XSODATA supports content ID referencing but NWGW doesn't.

Former Member
0 Kudos

Would it be too much to post the getCSRFToken() definition?  I have the SHINE-SP8 edition and don't see it in there.  I need to call from and SAPUI5 javascript controller.

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

In SHINE its in the /ui/shared/csrf.js.  But here is the source code in this library:

$.ajaxSetup({

     beforeSend: function(xhr,settings) {

       if (settings && settings.hasOwnProperty('type')

           && settings.type !== 'GET'){

       var token = getCSRFToken();

         xhr.setRequestHeader("X-CSRF-Token", token);

       }

     },

     complete: function(xhr,textStatus) {

         var loginPage = xhr.getResponseHeader("x-sap-login-page");

         if (loginPage) {

             location.href = loginPage + "?x-sap-origin-location=" + encodeURIComponent(window.location.pathname);

         }

     }

  });

  function getCSRFToken() {

     var token = null;

     $.ajax({

         url: "/sap/hana/democontent/epm/ui/shared/csrf.xsjs",

         type: "GET",

         async: false,

         beforeSend: function(xhr) {

             xhr.setRequestHeader("X-CSRF-Token", "Fetch");

         },

         complete: function(xhr) {

             token = xhr.getResponseHeader('X-CSRF-Token');

         }

     });

     return token;

  }

Former Member
0 Kudos

Hi Thomas,

Thanks for the reply.  Is this still the recommended way to create/update entities with many-to-many parent/child associations?  I am using the trial landscape (SPS 8.5?). One thing I noticed, I don't see a CSRF token in response to a GET request to my service. I am assuming this is why I am getting null for my token in getCSRF().   

Here is my data model:

    Entity Resource

    {

        key ID: String(10);

        NAME: SString not null;

        DESCRIPTION: SString;

    };

    Entity ComplianceRequirement

    {

        key ID: String(10);

        NAME: SString not null;

        DESCRIPTION: SString;

        NOTES: String(500);

        RENEWAL_CYCLE: association to ComplianceModel.RenewalCycle;

    };

    Entity ResourceComplianceRequirement

    {

  key RESOURCE: association to ComplianceModel.Resource;

  key COMPLIANCE_REQUIREMENT: association to ComplianceModel.ComplianceRequirement;

    };

Here is my service definition:

service {

  "yyy.zzz.ComplianceTracker.data::ComplianceModel.Resource"

  as "Resources"

  navigates ("ToResourceComplianceRequirements" as "ResourceComplianceRequirements")

  create events(before "yyy.zzz.ComplianceTracker.services:resourceComplianceRequirements.xsjslib::createResourceBeforeExit")

  ;

  "yyy.zzz.ComplianceTracker.data::ComplianceModel.ComplianceRequirement" as "ComplianceRequirements";

  "yyy.zzz.ComplianceTracker.data::ComplianceModel.ResourceComplianceRequirement" as "ResourceComplianceRequirements";

  association "ToResourceComplianceRequirements"

  principal "Resources"("ID") multiplicity "*"

  dependent "ComplianceRequirements"("ID") multiplicity "*"

  over "yyy.zzz.ComplianceTracker.data::ComplianceModel.ResourceComplianceRequirement"

  principal ("RESOURCE.ID")

  dependent ("COMPLIANCE_REQUIREMENT.ID");

}

Here is the request payload I am envisioning for create:

{

  "ID": "0",

  "NAME": "TESTNAME",

  "DESCRIPTION": "TESTDESC",

  "ResourceComplianceRequirements":[

{"RESOURCE.ID": "0", "COMPLIANCE_REQUREMENT.ID": "1"},

{"RESOURCE.ID": "0", "COMPLIANCE_REQUREMENT.ID": "3"},

{"RESOURCE.ID": "0", "COMPLIANCE_REQUREMENT.ID": "5"},

{"RESOURCE.ID": "0", "COMPLIANCE_REQUREMENT.ID": "7"}

   ]

}

Thanks and Regards,

Neal

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

>I don't see a CSRF token in response to a GET request to my service.

Did you request XSRF protection in the XSACCESS file?  If not then you won't get a token.

Former Member
0 Kudos

Thomas,

Thanks.  I did not have that enabled.  I enabled it, however I am still not able to hit my service.  I am getting the following error:

"Error processing request stream. The request should be a valid top-level resource object."

Here is my request payload:

  1. --batch
  2. Content-Type:multipart/mixed;boundary=changeset
  3. Content-Transfer-Encoding:binary

  4. --changeset
  5. Content-Type:application/http
  6. Content-Transfer-Encoding:binary
  7. Content-ID: 1

  8. POST Resources HTTP/1.1
  9. Content-Type: application/json
  10. Content-Length:20

  11. {"ID":"0","NAME":""}
  12. --changeset
  13. Content-Type:application/http
  14. Content-Transfer-Encoding:binary
  15. Content-ID: 2

  16. POST ResourceComplianceRequirements HTTP/1.1
  17. Content-Type:application/json
  18. Content-Length:2

  19. []
  20. --changeset
  21. Content-Type:application/http
  22. Content-Transfer-Encoding:binary

  23. PUT $1/$links/ResourceComplianceRequirements HTTP/1.1
  24. Content-Type:application/json
  25. Content-Length:12

  26. {"uri":"$2"}
  27. --changeset--

  28. --batch--

  29. I know that this is a little different than the example from above.  Do you see anything that would cause the error above? I am expecting it to call my xsjslib exit method and give an error for empty name field.

  30. Thanks & Regards,
  31. Neal
thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

Based upon that error you are either packing the request object incorrectly or failing to set one of the required request headers. You haven't posted any of that code, so I can't really comment further. I suggest you further study the SHINE example. It is a complete and working example of exactly this functionality.

Former Member
0 Kudos

Can you point me to the correct one that I should be using with a Trial Cloud account?  On GitHub, I see SAP/hana-shine and SAP/cloud-hana-shine-sp8.  I figured I should be using the SAP/cloud-hana-shine-sp8 since SAP/hana-shine is for SPS10 and the trial landscape uses SPS8.5.  I tried to install SAP/hana-shine according to the directions using the trial landscape instance and it tells me I have insufficient privileges to perform Import Server operation. I don't see anything like this in the SAP/cloud-hana-shine-sp8 repo but I will review again.

Former Member
0 Kudos

Thanks, Thomas!  I figured out I needed to use the SAP/cloud-hana-shine-sp8 repo for the trial account.  I was impatient before and didn't figure out how to rename all instances of {{PACKAGE_NAME}}, not just in the .hdbrole file.  That and a few hangups with the naming of projects including dashes and I am off to the races!

Also, I am able to call my service now.  Not sure what I did differently, but I renamed my Entity from Resource to ProtectedResource just in case there was a potential reserved word conflict.  That and restarting my computer seemed to do it.

Former Member
0 Kudos

My bad...I was able to call my service because I switched it to use an .hdbprocedure instead of a .xsjslib for my exit modification.  I must not be doing someting right when using .xsjslib for this.

Former Member
0 Kudos

I was able to *mostly* solve my problem.  The main thing was to specify a 1-to-1 for my association even though it is a many-to-many.  This gets the service working for create, update and delete operations.  However, it gives the error "Multiple resources found. Inconsistency between data model and service description found." when navigating to the URIs of the child collections.  I created a second service just for reads, but I am now stumped on how to handle the inline editing since a table can only have one service(model).  Here is my service definition for the write side:

service {

  "xxx.yyy.ComplianceTracker.data::ComplianceModel.ProtectedResource"

  as "ProtectedResources"

  navigates ("ToProtectedResourceComplianceRequirements" as "ProtectedResourceComplianceRequirements")

  create events(before "xxx.yyy.ComplianceTracker.services:protectedResourceComplianceRequirements.xsjslib::createProtectedResourceBeforeExit")

  delete using "xxx.yyy.ComplianceTracker.procedures::deleteProtectedResource"

  ;

  //"xxx.yyy.ComplianceTracker.data::ComplianceModel.ComplianceRequirement" as "ComplianceRequirements";

  "xxx.yyy.ComplianceTracker.data::ComplianceModel.ProtectedResourceComplianceRequirement" as "ProtectedResourceComplianceRequirements"

  //create events(before "xxx.yyy.ComplianceTracker.services:protectedResourceComplianceRequirements.xsjslib::createProtectedResourceComplianceReqsBeforeExit")

  ;

  association "ToProtectedResourceComplianceRequirements"

  principal "ProtectedResources"("ID") multiplicity "1"

  //dependent "ComplianceRequirements"("ID") multiplicity "*"

  dependent "ProtectedResourceComplianceRequirements"("PROTECTED_RESOURCE.ID") multiplicity "1"

  over "xxx.yyy.ComplianceTracker.data::ComplianceModel.ProtectedResourceComplianceRequirement"

  principal ("PROTECTED_RESOURCE.ID")

  dependent ("PROTECTED_RESOURCE.ID")

  update using "xxx.yyy.ComplianceTracker.services:protectedResourceComplianceRequirements.xsjslib::createAssociationBeforeExit"

  ;

}

former_member354211
Discoverer
0 Kudos

Hi Thomas,

I know this post is quite old, but I was wondering if you might be helpful here.

First of all - thanks for all you comments here, it was a massive help with my app and parent/child associations inserts, it works nicely now. I do however have one problem at the moment - it seems that the  XMLHttpRequest in JSON format does not accept special characters like e.g. trademarks, copyrights (® © ™) etc.

I have also an update service which is done using the ui5 odata model batch operations and this works fine for the special characters, but any other insert that is sent through the XMLHttpRequest does not. Would you know if there's anything I could implement in the request to support these kind of characters?

I can see that the data is passed as a different content type through the odata model batch call and the xml http request

XMLHttpRequest (built following your comments and HANA tutorials)

Request payload

--changeset

Content-Type:application/http

Content-Transfer-Encoding: binary

Content-ID: 4

POST productinformation HTTP/1.1

Content-Type: application/json

Content-Length:266

{"PRODUCT_INFORMATION_ID":"000000000000000","COMPONENT_NAME":"special characters test","TARGET_AUDIENCE_NAME":"special characters test","SUMMARY":"™","SPECIAL_INSTRUCTIONS":"","WHATS_NEW":"","TERMIN_CHANGES":"","ABBREVATIONS":"","LEFT_IN_ENGLISH":"","GLOSSARIES":""}

and the error it throws for the special characters

--BCD8EDA97AE351390F414D8D9D0DF8FA0

Content-Type: application/http

Content-Length: 216

content-transfer-encoding: binary

HTTP/1.1 400 Bad Request

Content-Type: application/json

Content-Length: 135

{ "error": { "code": "", "message": { "lang": "en-US", "value": "Error processing request stream. JSON text specified is not valid."}}}

--BCD8EDA97AE351390F414D8D9D0DF8FA0--

update request using the ui5 odata model batch operations (successful)

--changeset_2e86-352a-f521

Content-Type: application/http

Content-Transfer-Encoding: binary

PUT productinformation(342) HTTP/1.1

x-csrf-token: xxx

Accept-Language: en

Accept: application/atom+xml,application/atomsvc+xml,application/xml

MaxDataServiceVersion: 2.0

DataServiceVersion: 2.0

Content-Type: application/atom+xml

Content-Length: 627

<a:entry xmlns:a="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"><a:author><a:name/></a:author><a:content type="application/xml"><m:properties><d:PRODUCT_INFORMATION_ID>342</d:PRODUCT_INFORMATION_ID><d:COMPONENT_NAME>special characters test</d:COMPONENT_NAME><d:TARGET_AUDIENCE_NAME>special characters test</d:TARGET_AUDIENCE_NAME><d:SUMMARY>™©®</d:SUMMARY><d:SPECIAL_INSTRUCTIONS/><d:WHATS_NEW/><d:TERMIN_CHANGES/><d:ABBREVATIONS/><d:LEFT_IN_ENGLISH/><d:GLOSSARIES/></m:properties></a:content></a:entry>

--changeset_2e86-352a-f521

former_member609888
Discoverer
0 Kudos

Hi Thomas,

I followed your example and the request payload also was generated by your javascript. However, the third operation "PUT $1/$links/AddRef HTTP/1.1" results in error.

--batch_1556831878269_0

Content-Type: application/http

Content-Transfer-Encoding: binary

HTTP/1.1 400 Bad Request Content-Type: application/json;charset=utf-8

{"error":{"code":400,"message":{"lang":"en-US","value":"Syntax error in resource path at position ."}}}

--batch_1556831878269_0--

if i remove the whole changeset of the third operation, the request payload will success creating BusinessPartner and Address records in database, either using SAPUI5 or Postman. I am not the only one to face this issue. here is another link i found.

https://answers.sap.com/questions/358862/deepmultiple-insert-on-xsodata.html

Could you have a look at it to see what's the cause? Thanks.

Lester

sanjoy0308
Active Participant
0 Kudos

Hi thomas.jung,

When I use odata model V2 and the UI5 controller event logic to build the request:

var oBusinessPartner = {
"PARTNERID": "0000000000",
"EMAILADDRESS": "ABC@GMAIL.COM",
"COMPANYNAME": "ABC"
},
oAddress = {
"ADDRESSID": "0000000000",
"CITY": "AMSTERDAM"
},
oModel = this.oView.getModel(),
deferredGroupId = "createBPWithAddress";
oModel.setDeferredGroups([deferredGroupId]);
oModel.create("/BusinessPartners", oBusinessPartner, {
headers: {
"content-ID": 1
}
});
oModel.create("/Addresses", oAddress, {
headers: {
"content-ID": 2
}
});
var oLink = {};
oLink.uri = "$2";
oModel.update("/$1/$links/AddRef", oLink);
oModel.submitChanges({
"groupId": deferredGroupId,
success: function(oData) {
MessageToast.show("SUCCESS");
},
error: function(oError) {
MessageToast.show("ERROR");
}
});

I can see the Request Payload in browser is exactly same as you have. But I get error "Resource not found for the segment '$1' at position 0.".

Could you please help me and confirm whether odata model can support this links way of deep create request?

Kind Regards,

Sanjoy

thomas_jung
Developer Advocate
Developer Advocate

sanjoy.saha3 - My answer from 5 years ago didn't have anything to do with the odata model in UI5. I really would suggest you ask that as a fresh question in the appropriate UI5 tag.