Skip to Content

How to Use OData Adapter with Dynamic Query Calls

NetWeaver Process Orchestration with Connectivity Add-on installed, will benefit from OData and SFSF (Successfactor) Adapters. This document explains how to use OData adapter with dynamic queries.

The target OData capable receiver is HANA sidecar instance. OData service can be built easily with HANA Sidecar.

This will be useful in integrating OData services with OData adapter like with any other common adapters used in Process Orchestration.

Test Scenario

  1. 1. Build OData service in HANA Sidecar:

This example uses a custom table ZMAKT created in HANA side car which resembles MAKT table in ECC.

Definition of ZMAKT table in HANA:

1HANA.JPG

Contents of the table:

2HANA.JPG

Create an OData Service in HANA Sidecar

First a project is created in project explorer perspective in HAN Developer Studio. We call it ProjectTest.

This should be linked to the System Library where the tables are defined. This can be achieved by accessing Team/Share option by clicking on the ProjectTest folder and linking to the workspace defined for your objects.

Screenshot:

3HANA.JPG

Create 2 files as .xsaccess and .xsapp  in this folder.

Add the following to the .xsaccess file .

{

“exposed”: true

}

Leave .xsapp empty.

Create another folder called services in ProjectTest.

Create a file (the OData service)  as zmakt.xsodata in the “services” folder.

Define the service as follows:

service namespace “your-namespace” {

“YOUR_SPACE”.”ZMAKT” as “Market”;

}

“your-namespace” and “YOUR_SPACE” should be filled with appropriate entries. The namespace is just an arbitrary entry.  “YOUR_SPACE” is the table qualifier, could be probably the user ID you logged in or any other custom defined space.

Screenshot:


4HANA.JPG

Team commit and activate the objects.

This service can be tested in your browser by entering the following URL. The path of the URL can be taken from the properties of service “zmakt.xsodata”.

The typical URL will be:

http://<host>:<port>/ProjectTest/services/zmakt.xsodata/Market?$format=xml

“Market” is the name given to the service at the definition time.

It is followed with OData syntax query arguments.

Market?$filter=startswith(MATNR , ‘1’) is a filter condition to query ZMAKT for materials where number starts with character ‘1’.

Results:

<feed xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices” xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata“xmlns=”http://www.w3.org/2005/Atom” xml:base=”http://10.88.121.25:8080/ProjectTest/services/zmakt.xsodata/”>

<title type=”text”>Market</title>

<id>

http://10.88.121.25:8080/ProjectTest/services/zmakt.xsodata/Market

</id>

<author>

<name/>

</author>

<link rel=”self” title=”Market” href=”Market”/>

<entry>

<id>

http://10.88.121.25:8080/ProjectTest/services/zmakt.xsodata/Market(MANDT=’100′,MATNR=’100′,SPRAS=’DE’)

</id>

<title type=”text”/>

<author>

<name/>

</author>

<link rel=”edit” title=”Market” href=”Market(MANDT=’100′,MATNR=’100′,SPRAS=’DE’)”/>

<category term=”hana.test.bfusa.com.MarketType” scheme=”http://schemas.microsoft.com/ado/2007/08/dataservices/scheme“/>

<content type=”application/xml”>

<m:properties>

<d:MANDT m:type=”Edm.String”>100</d:MANDT>

<d:MATNR m:type=”Edm.String”>100</d:MATNR>

<d:SPRAS m:type=”Edm.String”>DE</d:SPRAS>

<d:MAKTX m:type=”Edm.String”>Test</d:MAKTX>

</m:properties>

</content>

</entry>

<entry>

<id>

http://10.88.121.25:8080/ProjectTest/services/zmakt.xsodata/Market(MANDT=’100′,MATNR=’100′,SPRAS=’ES’)

</id>

<title type=”text”/>

<author>

<name/>

</author>

<link rel=”edit” title=”Market” href=”Market(MANDT=’100′,MATNR=’100′,SPRAS=’ES’)”/>

<category term=”hana.test.bfusa.com.MarketType” scheme=”http://schemas.microsoft.com/ado/2007/08/dataservices/scheme“/>

<content type=”application/xml”>

<m:properties>

<d:MANDT m:type=”Edm.String”>100</d:MANDT>

<d:MATNR m:type=”Edm.String”>100</d:MATNR>

<d:SPRAS m:type=”Edm.String”>ES</d:SPRAS>

<d:MAKTX m:type=”Edm.String”>TEST</d:MAKTX>

</m:properties>

</content>

</entry>

<entry>

<id>

http://10.88.121.25:8080/ProjectTest/services/zmakt.xsodata/Market(MANDT=’100′,MATNR=’100′,SPRAS=’FR’)

</id>

<title type=”text”/>

<author>

<name/>

</author>

<link rel=”edit” title=”Market” href=”Market(MANDT=’100′,MATNR=’100′,SPRAS=’FR’)”/>

<category term=”hana.test.bfusa.com.MarketType” scheme=”http://schemas.microsoft.com/ado/2007/08/dataservices/scheme“/>

<content type=”application/xml”>

<m:properties>

<d:MANDT m:type=”Edm.String”>100</d:MANDT>

<d:MATNR m:type=”Edm.String”>100</d:MATNR>

<d:SPRAS m:type=”Edm.String”>FR</d:SPRAS>

<d:MAKTX m:type=”Edm.String”>Test</d:MAKTX>

</m:properties>

</content>

</entry>

</feed>

  1. 2. Build the interface in Process Orchestration to consume the OData service


One of the pre-requisite to use OData services in PO is that it should have “Connectivity Add-on” installed. This component adds OData and SFSF adapters into the Advanced Adapter Engine.

This is Adapter Metadata screenshot with Connectivity Add-on Installed.

5PO.JPG

In ESR:

6PO.JPG

We will be using OData adapter to build our interface to query with the HANA OData service. It should be able to send dynamic queries as shown in the URL above.

The interface will be a SOAP to OData scenario, so that we can test it with “wsnavigator” tool in Process Orchestration.

The outbound step is simple with building data type, message type and service interface as follows:

Connectivity Add-on delivered a standard data type QueryPayload and external definition QueryStringOptions. Please refer to the SAP Note: 2052093 on QueryStringOptions.

First these two can be copied to your namespace.

7PO.JPG

We also need the schema for HANA service output.

This is the schema:

<xs:schema xmlns:xs=”http://www.w3.org/2001/XMLSchema” attributeFormDefault=”unqualified” elementFormDefault=”qualified”>

   <xs:element name=”Market”>

      <xs:complexType>

         <xs:sequence>

            <xs:element name=”MarketType” maxOccurs=”unbounded” minOccurs=”0″>

               <xs:complexType>

                  <xs:sequence>

                     <xs:element type=”xs:string” name=”SPRAS” />

                     <xs:element type=”xs:string” name=”MANDT” />

                     <xs:element type=”xs:string” name=”MAKTX” />

                     <xs:element type=”xs:string” name=”MATNR” />

                  </xs:sequence>

               </xs:complexType>

            </xs:element>

         </xs:sequence>

      </xs:complexType>

   </xs:element>

</xs:schema>

You may copy this schema and adjust to your needs.

Now our basic objects are:

ED_Market  (HANA object schema External Definition)

QueryPayload (Standard datatype)

QueryStringOptions (Standard External Definition)

Service Interface Outbound Synchronous: (SI_QueryOData_Out)

8PO.JPG

    

Service Interface Inbound Synchronous:

9PO.JPG

   

Message Mapping for the Request:

10PO.JPG

This is a simple to build query with OData filter function “startswith” and assigned to the filter parameter on the target.

Basically it builds a string like “startswith(MATNR,’1’)”.

 

Message Mapping for the Response:

11PO.JPG

The source and the target are the same and it is one-to-one mapping to display the contents as it is.

Operation Mapping: (Request)

12PO.JPG

Response:

13PO.JPG

Now we are ready to build the IFLOW.

14PO.JPG

The IFLOW is defined between 2 business components: BC_ESOA_Service and BC_HANA.

The service interface is SI_QueryOData_Out defined previously.

Sender channel is a SOAP channel, Synchronous and Best Effort.

15PO.JPG

16PO.JPG

The inbound service interface is SI_Query_In, and the Receiver channel is OData channel pointing to HANA Sidecar.

In the receiver channel, General tab is like below:

17PO.JPG

In the “Adapter-Specific” tab, Address points to the URL under “General” sub-tab:

http://<host>:<port>/ProjectTest/services/zmakt.xsodata/ and login credentials are provided.

18PO.JPG

In the processing sub-tab:

Operation is “QUERY” and Resource Path is Market?$filter=filter in which the standard variable $filter will be assigned with value “filter” defined in the target structure “QueryStringOptions” in the mapping.

Keep payload format as “Atom”. Page Size is default.

19PO.JPG

In “Advanced” sub-tab:

Check the “Use Additional Settings” and add the parameter “dynamicQuery” and assign value “true”.

Refer to the SAP Note: 2052093 for more details.

20PO.JPG

Finally add the operation and the mapping to the IFLOW.

Activate and deploy the IFLOW.

  1. 3. Testing the Interface:


We are going to use “wsnavigator” to run the scenario.

In NWDS, under PI Runtime Explorer perspective, highlight the IFLOW and right-mouse click and take “Show Run-time properties” option.

21PO.JPG

Copy the WSDL highlighted:

Goto the “wsnavigator” panel of PO:

http://<host>:<port>/wsavigator

Paste the WSDL url and hit Next:

22WS.JPG

Select the service and hit next:

(Note that the screen will pop-up for login credential at first time).

23WS.JPG

Uncheck Key Attributes, and enter “MATNR” in the Name field and “1” in Value field.

It will build the query : startswith(MATNR,’1’).  (Materials starting with character 1).

Hit Next.

24WS.JPG

Query Output with Materials starting with character ‘1’:

25PO.JPG

Another Query : (startswith(SPRAS,’ES’))

26PO.JPG

Conclusion:

This is a simple example for how the OData dynamic query option is used in Process Orchestration. It is quite possible to build more flexibility around this feature to add various OData functionality. The example is just limited to “startswith” function, but queries can be built more dynamically depending on the needs.

Also the standard options allows 3 standard OData query parameters “skip”, “top” and “filter”. We can use all these in query combinations.

In addition to that, it is allowed to add more parameters not in the standard XSD provided.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

    1. Amber Badam

      Excellent Post Amith. Appreciate it.

      I was searching for this info for a while now.

      Could you update the tags for this blog, so it could easily searchable

      (0) 

Leave a Reply