This documentation is for WSO2 Enterprise Integrator version 6.1.1 . View documentation for the latest release in the 6.x.x family and the latest release in the 7.x.x family.

All docs This doc
Skip to end of metadata
Go to start of metadata

Note that WSO2 EI is shipped with the following changes to what is mentioned in this documentation:

  • <PRODUCT_HOME>/repository/samples/ directory that includes all Data Integration samples is changed to <EI_HOME>/samples/data-services/.
  • <PRODUCT_HOME>/repository/samples/resources/ directory that includes all artifacts related to the Data Integration samples is changed to <EI_HOME>/samples/data-services/resources/.

Exposing a database as an OData service eliminates the need to define queries, operation etc. to access the information in the database. OData (Open Data Protocol) is an OASIS standard that defines the best practice for building and consuming RESTful APIs. You can easily expose databases as an OData service using WSO2 Enterprise Integrator (WSO2 EI).

About the sample

This OData sample was generated to work with the default h2 database (DATA_SERV_SAMP.h2.db), which is used for samples in the WSO2 EI product. This database is stored in the <EI_HOME>/samples/data-services/dbs/odata directory. The details of this database are as follows:

Driver class: org.h2.Driver
JDBC URL: jdbc:h2:file:./samples/database/DATA_SERV_SAMP 

Tables in the DB:

  • CUSTOMERS
  • ACCOUNTS
  • PAYMENTS
  • ORDERS
  • PRODUCTLINES
  • FILES
  • PRODUCTS
  • FILERECORDS
  • EMPLOYEES
  • OFFICES
  • DEPARTMENT
  • ORDERDETAILS

You can find the DDL of these sql tables in the <EI_HOME>/samples/data-services/sql/h2 directory.

Prerequisites

The possibility of reading details from database views is available as a WUM update for WSO2 EI 6.1.1. This WUM update is effective from the 25th of December, 2017. For instructions on getting updates for WSO2 EI, see Updating WSO2 Products.

To demonstrate the functionality of reading detail from database views, let's add a view called 'USACustomers' to the H2 database that is used by this sample: Update the CreateTables.sql file (located in the <EI_HOME>/samples/data-services/sql/h2/ directory) with the SQL command shown below. 

CREATE VIEW USACustomers AS SELECT * FROM Customers WHERE country = 'USA';

Building the sample

The sample data service named ODataSampleService should be deployed, and the server should be started using the instructions in Samples Setup.

Executing the sample

You can easily invoke the service using HTTP requests. See the following examples.

If you want to invoke the OData services using the HTTPS protocol, use the 8243 port. For example, use the following URL: https://localhost:8243/odata/ODataSampleService/default

  • To get the service document:

    GET http://localhost:8280/odata/ODataSampleService/default 
    Accept : application/json
  • To get the meta data of the service:

    GET http://localhost:8280/odata/ODataSampleService/default/$metadata
    Accept : application/xml
  • To read customer details:

    GET http://localhost:8280/odata/ODataSampleService/default/CUSTOMERS
    Accept : application/json
  • To read customer details from the 'USACUSTOMERS' view in the databasesend the GET request as shown below. 

    Note the following:

    • This option is available only as a WUM update for WSO2 EI 6.1.1. See the prerequisites for details.
    • Database views can only be used with GET requests.
    GET http://localhost:8280/odata/ODataSampleService/default/USACUSTOMERS
    Accept : application/json
  • To add accounts details, send the HTTP request as shown below. In this example, we will be creating an account ID named "1".

    POST http://localhost:8280/odata/ODataSampleService/default/ACCOUNTS
    Accept: application/json
    Content-Type: application/json
    Prefer : return=representation
    { "BALANCE" : 12.22 ,"ACCOUNTID" : 1 }
  • To delete account details, send the HTTP request as shown below. In this example, we will be deleting the account ID "1".

    DELETE http://localhost:8280/odata/ODataSampleService/default/ACCOUNTS(1)
    Accept: application/json
  • To update account details:

    PUT http://localhost:8280/odata/ODataSampleService/default/ACCOUNTS(1)
    Accept: application/json
    { "BALANCE" : 12.22 }
  • To invoke a particular query, send the HTTP request as shown below. In this example, we will receive the city and phone numbers of customers.

    GET http://localhost:8280/odata/ODataSampleService/default/CUSTOMERS?$select=CITY,PHONE
    Accept: application/json
  • To filter information based on a query, send the HTTP request as shown below. In this example, we will receive the employee details connected to the 'Nantes' city.

    GET http://localhost:8280/odata/ODataSampleService/default/CUSTOMERS?$filter=CITY eq 'Nantes'
    Accept: application/json
  • No labels