Additional Blogs by Members
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SAP HANA OData Services and JQuery

Former Member
‎09-11-2012 3:41 PM

Introduction

In my previous post I described how I came across with the UI Toolkit for Information Access while building the content of our HANA workshop Get Your Hands-on IT for SAP HANA. I found the UI toolkit to be a very cool feature so I decided to dive deeper and to my surprise I found that HANA implements OData to expose the data in the HANA db to the web. In this post I’m going to describe how to build a simple web application using JQuery that consumes data stored in a HANA db using OData Services.

The Open Data Protocol (OData) is a web protocol that will allow you to query and update data using web technologies like HTTP and JSON. In other words, OData allows you to query a data source over the HTTP protocol and get the results back in JSON format. OData works pretty much the same as ODBC, this means you can perform any operation to manipulate the data in your data source, like Insert new rows, run Queries, Update rows and Delete rows. Each of these operations are mapped to an HTTP verb, see table below:

HTTP Verb

CRUD Method

POST

Create (Insert new rows)

GET

Read (Query)

PUT

Update

DELETE

Delete


We are going to use a HANA Attribute view as the data source, so let’s focus on learning how to query the HANA Attribute View over the HTTP protocol, but before we get started, let’s talk about the prerequisites.

Prerequisites

  • You will need to setup the XS Engine in your SAP HANA host
  • Install the UI Toolkit for Information Access. If you don’t know how to do this you can take a look at my previous post or refer to the official documentation.

Use HTTP Get to Retrieve Data

Like I already said I’m going to use a HANA Attribute view as the data source, so the first thing we need to do is create it. In my case I will use an Attribute View that contains all the different airports from around the world with the corresponding Latitude and Longitude. Once you are done creating the attribute view open the following URL to make sure it has been exposed using the SAP HANA REST service:

http://<HANAhost>:80<instance>/sap/bc/ina/service/v1/

There you will see something similar to the screenshot below and you should be able to find the attribute view that you just created. I highlighted mine:

All the attribute views are exposed using the following naming convention:

<package name>/<attribute view name>Collection

My Attribute View is called ATV_AIRPORTS_COR and it was created inside a package named lucas, so the name of the entity set looks like this:

lucas/ATV_AIRPORTS_CORCollection

Now we are ready to start querying our data source, to do so open the following URL address:

http://<HANAhost>:80<instance>/sap/bc/ina/service/v1/<entityset>

In my case the URL will look like this:

http://<HANAhost>:80<instance>/sap/bc/ina/service/v1/lucas/ATV_AIRORTS_CORCollection

There are multiple query string parameters defined by OData that you can use to query your entity set, not all of them are supported by SAP HANA, but I will outline the most important ones that are supported by SAP HANA:

Operator

Description

Example

Eq

Equal

?$filter=AIRPORTID eq 14895

Ne

Not Equal

?$filter=AIRPORTID ne 14895

Gt

Greater Than

?$filter=AIRPORTID gt 14895

Ge

Greater Than or equal

?$filter=AIRPORTID ge 14895

Lt

Less Than

?$filter=AIRPORTID lt 14895

Le

Less Than or equal

?$filter=AIRPORTID le 14895

And

Logical And

?$filter=AIRPORTID eq 14895 and COUNTRY eq ‘US’

Or

Logical Or

?$filter=AIRPORTID eq 14895 or COUNTRY eq ‘US’


        For further information you can refer to the OData official documentation but remember that not all options were implemented in SAP HANA

You can play around with the different query string options to get familiar or you can refer to the OData official documentation for more information

Building a Simple Web Application using JQuery

JQuery is a JavaScript library that simplifies the creation of HTML documents. JQuery is capable of handling data in JSON format using the getJSON() function. The first thing we need to do is set up a development environment in order to be able to create the web application. If you don’t know how to do this you can refer to my previous post or to the SAP HANA official documentation. Once you are done setting up your development environment create a new HTML document and name it jquerysample.html. The code inside the html document should look like this:

Let’s take a look at the code to understand what we are doing here. First we implement the JQuery library in the header of the page:

Then we create a <div> that will be the placeholder to show the data that we query from the HANA db:

Then we query the data from the HANA db using the getJSON() function. You can use any of the query string options that we discussed above:

Then we create a <table> using JQuery inside the <div> and we loop thru every column and every row that we are bringing from the HANA db:

Once you are done commit the newly created web page back to the HANA server and activate it using the regi commit and regi activate commands. The resulting web page will looks like this:

Here we are showing Airports with the corresponding Airport ID, Latitude and Longitude.  Not very fancy right? Well, I wanted to start with something simple so that you could easily follow it. Now that you understand the concept, let’s do something a little bit more complicated. In our next example we are going to use the Google Maps JQuery library to show the Airports in a map, so you will need to download the Google Maps library from here. Unzip the downloaded file, copy the “UI” folder inside your development environment and create a new web page called gmapssample.html. The code should look like this:

Let’s take a look at the code. First we implement the Google Maps API, the JQuery library and Google Maps JQuery library that we downloaded:

Then we create a <div> element that will be the container for our map:

Then we initialize the map with a hardcoded latitude and longitude:

Then we use the getJSON() function to query the data in the Attribute View, again you can use any of the query string options that I described above, in this case I’m filtering by column COUNTRYID equal 218 (United States):

Then we loop thru the data and add markers in our map for every Airport that we retrieve from the HANA db. We also use the InfoWindow to show the Airport Name:

Again, once you are done commit the newly created web page back to the HANA server and activate it using the regi commit and regi activate commands. The resulting web page will looks like this:

These are two very basic examples of how to use JQuery with SAP HANA OData Services, following this same concept you can build more complicated things and even build mobile applications. I hope you liked it and don’t forget to drop by our Get Your Hands-on IT for SAP HANA page to see what the workshop is about, you won’t find any other offering like this!

Follow me on Twitter: @LukiSpa

4 Comments
Popular Blog Posts