REST API for SQL Server, Oracle, or Postgres via OData

To help you get started, I will walk through on how you can RESTify an Oracle database behind a firewall and use that to bind it to several Kendo UI components.

1. Install Hybrid Data Pipeline, available with a free 90 day trial.
2. Open the Progress console and you should see a login page as shown below. Login as the Administrator, as specified during the Hybrid Data Pipeline installation.

    

   
   

3.  You will be able to access the database even if it’s On Premises behind a firewall. If your database is publicly accessible, you can skip the below section about the On-Premises Connector and go to step 5. If not, please go through the below section about configuring the On-Premises Connector to access your database that is On-Premises.
4.  Configuring On-Premises Connector
   • Run the installer to start installing the On-Premise Connector. Follow the instructions on the installer and proceed ahead as most of the installation is pretty straight forward.
     
   • While installing the On-Premises Connector, you will come across a form where it asks for your DataDirect username, password and a connector label that you would like to have. Provide your DataDirect Hybrid Data Pipeline credentials (the same as specified during the Hybrid Data Pipeline Server installation) and provide any name for connector label as you wish. Below is screenshot for the form that I am talking about for your reference.

      

     
     

   • Once the installation completes, exit the installer and open the Hybrid Data Pipeline On-Premises configuration tool from Start Menu and go to status tab and click on Test Connection. You should see that On-Premises Connector successfully connects with all the DataDirect services as shown below.

      

     
     

5. On the Hybrid Data Pipeline dashboard, go to Data Sources and click on New Data Sources button to see the latest list of supported data stores.

    

   
   

    

6. Choose Oracle and edit the required connection information to your database such as hostname, port, and Connector ID, as shown below on the configuration screen. Make sure you scroll down to see all the data source fields. If you need sample data, you can download this DDL script which will create sample tables and insert data into them.
7. If connecting to an On-Premises Oracle database, you will see a Connector ID value having the same name that was specified when installing the On-Premises connector behind the firewall in the previous steps. Choose that connector as a part of configuring the connection.

    

8. Click on the “Test Connection” button on the right side and it should return a message saying Connection was successful. If you get a connection error, you must resolve that before proceeding. Check that your Oracle user name, password, Port Number, etc are correct for the database you are trying to reach.

    

9. Now go to OData tab that you see on the same page to enable OData access to entities that you would like to use. Click on the OData tab you should see a view as shown in below screenshot. Note that I have blocked out my server name for security reasons. Your URI should be visible.

    

10. Click on the Configure Schema button, which reveals a page where you would have to select the schema of the database that you would like to configure. On selecting the Schema, you will be redirected to page where you will see all the tables that you have in your schema as shown below.

     

    
    

11. Select all the tables that you would like to access through OData. Note that you would have to choose a primary key if the table doesn’t have any on the above dashboard. After you have chosen the tables click on Save and Close which will update the settings and you should be back on the main OData page.

     

12. Copy the OData Access URI and Save the data source.
13. To test the API, Paste the OData Access URI for your data source and paste it in your browser. When you browse that URL, it will prompt for your Hybrid Data Pipeline credentials and when you provide them and proceed ahead, you should see the list of tables returned by OData API.

With DataDirect Hybrid OData services, you can access your cloud data sources like Salesforce, Google Analytics, Eloqua or relational databases like Oracle, SQL Server, Postgres or Big Data sources like EMR Hive, CDH, Apache Hadoop, Greenplum etc. Access all of these sources using a single protocol — OData REST API. OData isn’t a proprietary API like others, it’s an open protocol standard that was started at Microsoft, which became an OASIS standard and is widely being adopted by SAP, Salesforce, IBM, Tableau and others. You don’t have to worry about learning/developing different API’s when you use DataDirect. You just need to know how you can use OData API, with which you likely have experience.

Kendo UI for jQuery provides you more than 70 components to build responsive, beautiful, modern web applications 50% faster than any other product. Data being an integral part of any website, Kendo UI lets you bind to your local, remote or XML data using the DataSource Framework. Most of the time, you have to deal with accessing the data either in the cloud or in a relational database on-premises. You can find below how you can access any of these databases from KendoUI through simple clicks.

1.  Now that we have generated OData API for your database, you can use this with the DataSource Framework of Kendo UI, which has an amazing support for OData. I have compiled couple of examples on Telerik Dojo on how to do this for various components of Kendo UI using a DataDirect sample OData service. (Note that this test service is not guaranteed to be available.)
   1. Charts
      1. Area Chart
      2. Range Bar Chart
   2. Data Management
      1. Grid
   3. Navigation
      1. Tree view
   4. Editors
      1. AutoComplete
      2. DropDownList
      3. ComboBox
2. Once you open those links, you will see Telerik Dojo, where you have the code used for the sample on the left side and result on right side. Here is a screenshot for your reference on how this will look.

We hope this tutorial was helpful to you in RESTifying your database and binding it to your Kendo UI components for rich, elegant and real time web application.

Hybrid Data Pipeline is a self-hosted service which can be deployed in the Cloud or On-Premises, while meeting security and data residency requirements. Feel Free to try this solution by visiting here.