Tutorial: Creating an OData service based on SAP Gateway, SOAP, JDBC and JPA data sources
Table of Contents
Note (October 21, 2014): I updated the blog to reflect changes in the Eclipse modeling environment. You now need to use the SAP Mobile Platform tools instead of the Gateway Productivity Accelerator (GWPA).
On May 15th, 2014 the SAP Mobile Platform 3.0 became generally available to the public. While it contains many great features, I would like to show you in this blog how it is possible to leverage the Integration Gateway component inside the Mobile Platform.
First, I would like to point you to some resources available to explain what Integration Gateway can do: Integration Gateway is a technology component that provides design-time tools and the associated run-time for modeling, composing and provisioning OData services. These can be based on one or more APIs from various SAP and non-SAP data sources.
The data sources supported as of today are SOAP (Simple Object Access Protocol), JPA (Java Persistence API) and JDBC (Java Database Connectivity) to access non-SAP data sources as well as OData Channel (ODC) for accessing OData services provisioned via SAP Gateway.
For a brief overview, you might want to have a look at this Youtube video.
For this blog we are relying on the Enterprise Procurement Model (EPM)part of NetWeaver starting with release NW 7.02. It is an application intended to be used for demonstration and testing purposes. The business scenario at the core of EPM is that of a web shop run by a retail company called ITelO, a fictitious company that buys and sells computers & accessories.
In this demo scenario a customer will log on with an HTML5 app to purchase some goods from the ITelO store. The app consumes a service published by the Integration Gateway. The underlying data for the service stem from SOAP, JPA, JDBC and ODC data sources demonstrating the integration and mash-up capabilities of Integration Gateway in SMP3.
For recreating this scenario you will need administrative access to an (developer) installation of SAP Mobile Platform 3.0 SP03 (In the rest of this blog I will refer to it as SMP3). If you have the authorizations, you can download the SMP3 installation files from the SAP Software Download Center.
Furthermore you need to register to access the SAP Gateway Service Consumption System.
As mentioned before, we are going to use SOAP, JPA, JDBC and ODC data sources for this tutorial. The SOAP and ODC services will be provided by us. Your developer installation of SMP3 comes with an Apache Derby database. We are going to create a new table in this database which will serve as our JDBC data source. Furthermore, the SMP3 comes preinstalled with a Java Persistency API version of the EPM model. The products stored in this model will serve as our JPA data source.
Your SMP3 installation comes with an Apache Derby database. We now would like to create a new table called DELIVERYTYPES in this database. You can do so by using SquirrelSQL and you can find detailed setup instractions in this blog.
Once you are connected to the Derby database, click on the tab calles “SQL” and paste the following SQL statements in the command line:
create table deliverytypes (deliverytype varchar (3), description varchar (50));
insert into deliverytypes values (‘OVN’, ‘Overnight’);
insert into deliverytypes values (‘2ND’, ‘2nd Business Day’);
insert into deliverytypes values (‘GRD’, ‘Ground (5-7 Business Days)’);
select * from deliverytypes;
Click on the first line and then click on the “execute” (running man) button in order to create a new database table called DELIVERYTYPES with three entries. Using the SELECT command, you can verify that the entries have been created successfully.
For this scenario, you will need to download the SAP Mobile Platform Tools. You can find all the installation instructions at https://tools.hana.ondemand.com/#gateway
Now you will need to set up the connection to the Integration Gateway component on the SMP. To do so, please go to:
Window > Preferences > OData Development > Integration Gateway
URL: <insert path and HTTPS port to your SMP3 server>
User: <Admin username> (default: smpAdmin)
Password: <Admin password> (default: s3pAdmin)
Click on the “Test Connection” button to verify that your connection to the SMP server is working.
Next, you will create an empty Service Implementation Project:
File > New > Others > SAP Mobile Platform > SAP Mobile Platform OData Implementation Project:
Specify the project name “IGW_EPM_Demo” and select “Create OData file manually” and finish the wizard:
Now you will create the OData model. You can do so by right clicking on model > New > Other > OData Development > OData Model and selecting the entry OData service Metadata File. Provide the model name ODataModel:
Attached to this document you can find the file ODataModel.xml. Save this file to your computer and select it in the wizard as follows:
Please save the resulting model and then your ODataModel should look like this:
Now you need to implement the service and select the data source for each entity set. Right click on your OData model and choose “Implement Service”:
Then you will need to select the data source for each entity set in your OData Model. So please repeat this step for every entity set in your OData model:
The data for the entity sets SalesOrderCollectionand SalesOrderLineItemCollectionshould come from the NetWeaver backend provided by us. Therefore we select the data source ODC for these:
For both entity sets, set the following connection details:
The DELIVERYTYPES entity set should be served by a JDBC data source.
You want to provide the Products entity set with data from the JPA ESPM model on the SMP server:
Note: This wizard step was reworked with the SAP Mobile Platform Tools. You now only need to select the WSDL file from your hard drive. Then you may select the operation and port type from the wizard.
Finally, the entity set Stocks should be served with data from a SOAP web service:
The SOAP endpoint is provided on the ES1 development system:
Endpoint: https://sapes1.sapdevcenter.com:443/sap/bc/srt/rfc/sap/z_sepm_gws_stock_get/520/service/bindingNamespace: urn:sap-com:document:sap:soap:functions:mc-style
In this step you will need to map the result from the SOAP web service to the Stocks entity set. To do so, expand the Stocks node and select “Define Response Mapping” from the context menu:
Attached to this blog entry, you can find the file StocksES1.wsdl (Note: Please rename the file from StocksES1.wsdl.xml to StocksES1.wsdl. It is not allowed to attach files in the WSDL format to this blog.). Save this file to your hard drive and then select this file in the wizard:
Now you should perform the mapping as follows:
In this step you need to deploy your service on the SMP. To do so, right click on the Service Implementation Project node and select the entry “Generate and Deploy Integration Content”:
Fill out the values in the popup as follows:
You should now receive the confirmation, that the deployment has been completed for IGW_EPM_Demo.
In this step you are going to set up SMP3. First, you need to log on to your SMP Administration console: https://localhost:8083/Admin/ (Again, you will need to exchange the <localhost> with your server URL.)
Navigate to Settings > Security Profiles > New in order to create a new security profile called sap. This basically means that users will not need to authenticate, when accessing services deployed in the namespace “sap”:
The SOAP web service and ODC service will be provided using a HTTPS connection. However in order for the SMP to trust the certificate provided by the server, you first need to add the certificate to the SMP keystore.
Using your browser, please navigate to the following URL:
You will be asked for your username and password for the SAP Gateway Service Consumption System (ES1). In Google Chrome (in Windows) you now need to click on the green padlock icon > Connection > Certificate Information:
In the Certificate popup please select the tab “details” and click on the “copy to file” button. Follow the wizard using the standard values and export the file with the filename SAPES1.cer.
You need to have access to the directory where the SMP server is running. You then need to download the open source tool Portecle: http://sourceforge.net/projects/portecle/files/latest/download
Unzip the file you just downloaded and open a command window / terminal in the unpacked folder. (In Windows you can do so by holding the shift key and right clicking into the folder with the portecle files.)
Run the protecle application by running the command java –jar portecle.jar. Click on File > Open Keystore File and navigate to the configuration folder on your SMP3 server. Open the file smp_keystore.jks. In my case this is C:\SAP\MobilePlatform3\Server\configuration\smp_keystore.jks. The default password to open the truststore file is s3pAdmin, but can be set to a different value during the installation process.
Click on Tools > Import Trusted Certificateand select the file SAPES1.cer you saved to your hard drive in the previous step. You will see the following warning, but this is ok:
You will now see the certificate details and will be asked if you want to trust this certificate. If you choose to accespt this, please enter the alias name SAPES1 for the new certificate:
Now you should receive the successful confirmation message and should see that a new certificate has been added to your SMP trust store.
The certificate for the ES1 system has been issued by “Starfield Repository”. So please go to the following site (https://certs.starfieldtech.com/anonymous/repository.pki) and download the certificate sf-class2-root.cer. Please follow the same steps as above to import the certificate to the SMP trust store.
To finish please click on the save button and then you can close Portecle. Please restart your server now for this change to take effect.
Now you need to head over to the Gateway Cockpit of your SMP server: https://localhost:8083/gateway/cockpit (Again, you will need to exchange the <localhost> with your server URL.) Navigate to the tab called Destinations. You will now create a destination for each backend system we want to use for our data sources.
Click on “Create New Destination” and fill in the following data:
Destination Name: ES1
Destination Type: HTTP
Destination URL: https://sapes1.sapdevcenter.com:443/sap/iwbep?sap-client=520
Username: <your ES1 username>
Password: <your ES1 password>
TrustStore File: smp_keystore.jks
Truststore Password: <your SMP keystore password> (default s3pAdmin)
You need to provide the TrustStore file and password, because here the certificate for the ES1 server is stored to establish a trusted connection between your SMP server and the ES1 system.
Click on “Create New Destination” and fill in the following data:
Destination Name: JPA
Destination Type: JPA
Persistence Unit: com.sap.espm.model
Click on “Create New Destination” and fill in the following data:
Destination Name: JDBC
Destination Type: DATABASE
Destination URL: jdbc:derby://localhost:1527/D:/SAP/MobilePlatform3/Server/db/derby/smp3
Database Driver: org.apache.derby.jdbc.ClientDriver
Now you will need to add register the ZGWSAMPLE_SRV service coming from the ES1 system. To register, please head to the services tab and click on the “Register a New Service” button. Select the destination “ES1” and search for the service “ZGWS*”. In the result list, please mark the service ZGWSAMPLE_SRV and click on the “Register” button.
Note: In case you get an error message when searching for the service, you might need to set up a proxy server. For information on how to do so, please refer to the Troubleshooting section at the end of this blog.
The last step you need to do, is to assign the destinations to the entity sets. By doing so, you can define which entity set is to be served by which destination/backend system. To assign the destinations, please click on the service name “IGW_EPM_Demo”. This is the service you deployed from your eclipse environment.
In the popup, please select the radio button “Assign Destinations for EntitySets” and then the “Add Destination” button. In the popup, please create the following Entity Sets with the corresponding destinations as listed:
No destination for the Stocks entity set needed to be provided here, as all the information have been provided in the eclipse design time.
This concludes all the steps you need to do to get your service up and running.
To test the service I will show you how to retrieve data from the service you just provisioned on the SMP3 and how to create a sales order on the ES1 backend system.
When testing a service, I like to use the Advanced REST Client extension in Google Chrome. You can download and install the extension in the Chrome Web Store.
To test your service, you might want to try out the following URLs and please feel free to try other OData URI commands. Try to send a GET request and remember to replace the “localhost” with your SMP server URL:
This will return a list with all entity sets contained in your service
This will return the metadata document for your service
This will return a list with all sales orders from the ES1 system
This will return only sales order 500000003 from the ES1 system
This will return all sales order line items for sales order 500000011
· http://localhost:8080/gateway/odata/sap/IGW_EPM_Demo;v=1/SalesOrderLineItemCollection(SoId=’0500000011′, SoItemPos=’0000000020′)
This will return sales order line item 20 of sales order 500000011
· http://localhost:8080/gateway/odata/sap/IGW_EPM_Demo;v=1/Products?$filter=substringof(%27USB%27,ProductId) or substringof(%27USB%27,Name)
This will return all products where the string “USB” is contained either in the field “ProductId” or in the field “Name”
This will return the 3rd and 4th most expensive products
In order to prevent Cross-Site Request Forgery attacks, we are making use of a CSRF-Token. This means, that whenever you are sending a POST request to the SMP3, you will need to provide the CSRF-Token. You can retrieve the token from the SMP3 server by sending a GET request to the service you want to use (in my case this is: http://localhost:8080/gateway/odata/sap/IGW_EPM_Demo;v=1/SalesOrderCollection(‘0500000003’)) and adding a parameter to the request header. The parameter should read as follows “X-CSRF-Token: fetch”. The response header from SMP3 will contain an X-CSRF-Token. You will need this token when sending POST requests to the server. In Advanced REST Client the response would look like this:
The easiest way to create a new sales order, is to copy the response body from your previous GET request (e.g. http://localhost:8080/gateway/odata/sap/IGW_EPM_Demo;v=1/SalesOrderCollection(‘0500000003’)) and to use this as the request body for your POST request. Please add the X-CSRF-Token parameter with the token you received with your GET request to the header of your POST request and set the Content-Type to “application/atom+xml”. Post the request to the SalesOrderCollection entity set: http://localhost:8080/gateway/odata/sap/IGW_EPM_Demo;v=1/SalesOrderCollection
Your request in the Advanced REST Client should then look as follows:
In my case, I used the following payload data for the POST request:
<entry xmlns="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" xml:base="https://localhost:8081/gateway/odata/sap/IGW_EPM_Demo;v=1/"> <id>https://localhost:8081/gateway/odata/sap/IGW_EPM_Demo;v=1/SalesOrderCollection('0500000003')</id> <title type="text">SalesOrderCollection</title> <updated>2014-06-06T16:41:24.454+02:00</updated> <category term="ZGWSAMPLE_SRV.SalesOrder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" /> <link href="SalesOrderCollection('0500000003')" rel="edit" title="SalesOrder" /> <content type="application/xml"> <m:properties> <d:SoId>0500000003</d:SoId> <d:CurrencyCode>EUR</d:CurrencyCode> <d:BuyerName>Asia High tech</d:BuyerName> <d:BuyerId>0100000006</d:BuyerId> <d:Note>EPM DG: SO ID 0500000003 Deliver as fast as possible</d:Note> <d:ChangedByBp /> <d:CreatedByBp /> <d:ChangedAt>2014-05-13T05:00:00.000</d:ChangedAt> <d:ChangedBy>0000000033</d:ChangedBy> <d:CreatedAt>2014-05-13T05:00:00.000</d:CreatedAt> <d:CreatedBy>0000000033</d:CreatedBy> <d:GrossAmount>1405.39</d:GrossAmount> <d:GrossAmountExt>1405.39</d:GrossAmountExt> <d:NetAmount>1181.0</d:NetAmount> <d:NetAmountExt>1181.0</d:NetAmountExt> <d:TaxAmount>224.39</d:TaxAmount> <d:TaxAmountExt>224.39</d:TaxAmountExt> <d:LifecycleStatus>N</d:LifecycleStatus> <d:BillingStatus /> <d:DeliveryStatus /> </m:properties> </content> </entry>
Once you sent the request, the server will reply with the status code “201 Created” and you will be able to find the data of the newly created sales order header in the response body.
In case you are in a corporate network, you might run into some proxy issues when SMP is trying to access the ES1 devcenter system. In this case, you have to set up a proxy server on SMP file system level:
Go to your SMP server directory and open the file “props.ini”. In my case it is located in C:\SAP\MobilePlatform3\Server. In the file you can find some parameters for setting your system proxy. In our scenario this proxy will be used by the SOAP destination. Enter your proxy and port as follows:
After setting the system proxy, please save the props.ini file and restart your SMP server.
In SMP you can set an individual proxy server for each destination. To do so, please navigate to the destinations folder on your SMP server. In my case the complete path is: C:\SAP\MobilePlatform3\Server\config_master\service.destinations\destinations. Here you can find a file called ES1. Open this file with a text editior and add a new line with a parameter called “Proxy=[yourProxy]:[Port]”:
#Thu Jun 05 13:16:15 CEST 2014
Please save the file and the changes apply to the destination without restarting the SMP server.
10.1 Documentation & SAP Notes
· http://service.sap.com/sap/support/notes/1830710 – Gateway Productivity Accelerators Release Note & Constraints
· http://service.sap.com/sap/support/notes/1931374 – Integration Gateway for SAP Mobile Platform 3.0 – Known Constraints
· More information on SMP 3.0, including Integration Gateway is available here: http://help.sap.com/mobile-platform/
· Specific info on SMP 3.0 SP03 is available here: http://help.sap.com/smp303svr/
10.2 Webinar: Integration Gateway in SAP Mobile Platform 3.0
On June 18, 2014 there will be a webinar session called Integration Gateway in SAP Mobile Platform 3.0. In the webinar there will be a live demo using a similar scenario to the one described in this blog. There will also be a Q&A section where you will be able to ask questions. Please use this link to the online registration. The registration will be closed midnight, June 17, 2014 and it is mandatory for the confcall details.