Home Services Partners Company

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

RESTify Your Database

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. We recommend starting with the Progress hosted service for a proof of concept, and you can sign up for a trial DataDirect Cloud account. (Note: if you prefer to start with a self-hosted option, download the Hybrid Data Pipeline)
  2. Login into Progress console and you should be seeing a page as shown below.

     

    Login into Progress console

  3. Click on Connect Data button on Progress DataDirect Cloud banner that you see on the page to open the DataDirect Cloud console.
  4. As mentioned earlier with DataDirect Cloud, 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 On Premise connector and go to step 5. If not, please go through the below section about configuring On Premise connector to access your database that is On Premise.

    4.1 Configuring On Premise connector

    1. On the left side of the DataDirect Cloud console, under quick links you will find a Downloads link. Click on that to reveal the downloads page.
    2. On the Downloads Page you will find the On-Premise Connector Installer for Windows. Download the relevant architectural version of installer according to your OS.
    3. 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.
    4. While installing the On-premise Connector, you will come across the form where it asks for your DataDirect username, password and a connector label that you would like to have. Provide your DataDirect Cloud credentials and provide any name for connector label as you wish. Below is screenshot for the form that I am talking about for your reference.

       

      Provide your DataDirect Cloud credentials

    5. Once the installation completes, exit the installer and in a few seconds you should see a configuration tool that opens and automatically tests for the connection to DataDirect Cloud. You should see that On-premise connector successfully connects with all the DataDirect Cloud services as shown below.

       

      Configuration tool

  5. On the DataDirect Cloud dashboard, go to Data Sources and click on New Data Sources button to see the latest data stores presently supported by DataDirect Cloud.

     

    Click on New Data Sources

     

  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.  If you need sample data, you can download this DDL script which will create sample tables and insert data into them.
  7. If the database is accessible from the public cloud, change the default radio button from “On-Premise” to “Cloud” and disregard the Connector ID.

     

    Change the default radio button from “On-Premise” to “Cloud”

  8. 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-Premise connector behind the firewall in the previous steps. Choose that connector as a part of configuring the connection.

     

  9. Click on the “Test Connection” button and it should return a message saying Connection was successfully as shown in above screenshot.

     

  10. 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.

     Click on the OData tab

  11. 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.

     

    Click on the Configure Schema button

  12. 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 OData page as shown below.

     

    OData page

  13. Copy the OData Access URI and Save the data source.
  14. 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 DataDirect Cloud credentials and when you provide them and proceed ahead, you should see the list of tables returned by OData API.
That’s it! You have successfully created a REST API for your Oracle data source within 15 mins.

What does this mean to you?

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 Cloud. You just need to know how you can use OData API, with which you likely have experience.

Binding Kendo UI to OData

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 DataDirect Cloud OData service which 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.

Telerik Dojo

Next Steps

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. Now there were always Users/Organizations that gave feedback to us that they would like to deploy the DataDirect Cloud service in their own environment due to various privacy laws that may be of company policies or their respective laws for their country.

To accommodate such users, we released a self-hosted version of DataDirect Cloud called Hybrid Data Pipeline. You can deploy this service on Cloud or On-Premises and still be able to get all the features provided by DataDirect Cloud and this can be controlled by your own IT/Organization. Feel Free to try this solution by visiting here.

If you are unsure about which solution to use, visit this page to learn more about these two solutions and their differences.
OData, Cloud and Hybrid TUTORIAL

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

jdbc

DataDirect
ODBC connectors

Real-Time Access to XML Data

community

DataDirect
ODBC Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers