Android – HANA Database integration

Hi Everyone,

I would like to share to you a test application I designed on Android platform that fetches data from HANA database provisioned on HCP (HANA Cloud Platform).

This is a very simple application and I should say it is very basic ! I will try to work more on to find what could be established to the maximum.

So, lets start looking at what all I did

Implementation scenario:

Fig: Environment of the implementation scenario

Tools:

1. Android Studio
2. HANA Cloud Platform (SAP HANA Web-based Development Workbench)

Part 1: Creation of a sample table and rendering data in JSON format

1. Creating a table <TEST_TABLE1> in INFTEST schema already created in HANA database

                                      Fig: Sample database created in HANA Cloud Platform

Clicking on the Development Tools: SAP HANA Web-based Development Workbench will display this page.

Fig: Links for ide editor

Now in catalog of the workbench tool we will create Schema, table and feed test data.

Create a new schema in the catalog.

Now, create a table in the schema created using the SQL Console

Code for creating a table:

CREATE COLUMN TABLE TEST_TABLE1 (

    ID INTEGER,

    NAME VARCHAR(10),

    DESC VARCHAR(100),

    PRIMARY KEY (ID)

);

You will find the table created here

We will now feed test data into the table created

Code to insert data in the SQL Console

INSERT INTO “INFTEST”.”TEST_TABLE1″ VALUES(

                /*ID <INTEGER>*/,

                ”/*NAME <VARCHAR(10)>*/,

                ”/*DESC <VARCHAR(100)>*/

);

Test data in the database

Now, we need to write XSJS service to render the data present in this table to JSON format.

Mobile application utilizes the JSON returned and presents it on the UI end.

We create a new package in the SAP HANA Web-based Development Workbench: Editor

In the new package created under the Content menu, we create three files. A XSJS file which reads the data from the database table and returns in JSON format, a .xsaccess file and a .xsapp file.

Code: INFTEST1.xsjs

//function Start close

function close(closables) {

var closable;

var i;

for (i = 0; i < closables.length; i++) {

closable = closables[i];

if (closable) {

closable.close();

}}}

//function End close

//Query

var que_sla_tab = ‘SELECT * FROM “INFTEST”.”TEST_TABLE1″‘;

//End of Query

//function to get data getData()

function getData() {

var conn = $.db.getConnection();

var stat = null;

var resSet = null;

var dataListTable = [];

try {

stat = conn.prepareStatement(que_sla_tab);

resSet = stat.executeQuery();

var data;

while (resSet.next()) {

data = {};

data.ID = resSet.getString(1);

data.NAME = resSet.getString(2);

data.ROLE = resSet.getString(3);

dataListTable.push(data);

} //End of while

} //End of try

finally {

close([resSet, stat, conn]);

}

return dataListTable;

}

//End of function to get data getData()

//function doget

function doGet() {

try {

$.response.contentType = “application/json”;

$.response.setBody(JSON.stringify(getData()));

$.response.setBody(JSON.stringify(getData()));

} catch (err) {

$.response.contentType = “text/plain”;

$.response.setBody(“Error while executing query []: [” + err.message + “]”);

$.response.returnCode = 200;

}}

doGet();

//End of function doget

Code: .xsaccess

{“prevent_xsrf” : false,

“exposed” : true,

“authentication” :

{

“method”: “Basic”

}

}

Code: .xsapp

{}

When this XSJS service is called we receive data in JSON format.

Part 2: Designing Android application in Android studio

In this scenario we will just present the JSON data onto a TextView in android application. We can design the UI according to our requirement and bind the data accordingly.

Create a new android application with a blank activity on Create Activity Screen

In Android Studio go to File -> New -> New Project

In the new project files created, we mostly work on the highlighted files here

To make an Android application to run or communicate with the Internet we need to add this line of code in AndroidManifest.xml file

<uses-permission android:name=“android.permission.INTERNET”/>

Code: AndroidManifest.xml

<?xml version=”1.0″ encoding=”utf-8″?>
<manifest xmlns:android=”http://schemas.android.com/apk/res/android”
package=”com.example.infosson.app2″>

<application
android:allowBackup=”true”
android:icon=”@mipmap/ic_launcher”
android:label=”@string/app_name”
android:supportsRtl=”true”
android:theme=”@style/AppTheme”>
<activity android:name=”.MainActivity”>
<intent-filter>
<action android:name=”android.intent.action.MAIN” />

<category android:name=”android.intent.category.LAUNCHER” />
</intent-filter>
</activity>        <meta-data
android:name=”com.google.android.gms.version”
android:value=”@integer/google_play_services_version” />
</application>
<uses-permission android:name=”android.permission.INTERNET”/>
</manifest>

Now we will write code in the MainActivity.java which loads the data with an URL call to the HANA Database connecting to the internet. Also we will create a very simple TextView which shows the data in JSON format converted to string and display on the screen in activity_main.xml file.

Code: activity_main.xml

<?xml version=”1.0″ encoding=”utf-8″?>
<RelativeLayout xmlns:android=”http://schemas.android.com/apk/res/android”
xmlns:tools=”http://schemas.android.com/tools”
android:id=”@+id/activity_main”
android:layout_width=”match_parent”
android:layout_height=”match_parent”
android:paddingBottom=”@dimen/activity_vertical_margin”
android:paddingLeft=”@dimen/activity_horizontal_margin”
android:paddingRight=”@dimen/activity_horizontal_margin”
android:paddingTop=”@dimen/activity_vertical_margin”
tools:context=”com.example.infosson.app2.MainActivity”>

<TextView
android:id=”@+id/MyTextResponse”
android:layout_width=”wrap_content”
android:layout_height=”wrap_content”
android:text=”Hello World!” />
</RelativeLayout>

We will use this TextView to show the updated JSON data.

Code: MainActivity.java

package com.example.infosson.app2;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
/*importing all the necessary libraries*/
import android.widget.TextView;
import android.os.AsyncTask;
import android.util.Base64;

import com.google.android.gms.appindexing.Action;
import com.google.android.gms.appindexing.AppIndex;
import com.google.android.gms.appindexing.Thing;
import com.google.android.gms.common.api.GoogleApiClient;

import java.net.URL;
import java.net.URLConnection;
import java.io.BufferedReader;
import java.io.InputStreamReader;

public class MainActivity extends AppCompatActivity {

public String getOdata() {
String JASONrs = “Hi”;
try {
URL myhana = new URL(“https://hanapracc5232886trial.hanatrial.ondemand.com/INFTEST1/INFTEST1.xsjs?$format=json”);

URLConnection hanacon;
hanacon = myhana.openConnection();
hanacon.setReadTimeout(10000);
hanacon.setConnectTimeout(10000);

String userpass = “SYSTEM” + “:” + “##########”;
String basicAuth = “Basic ” + new String(Base64.encode(userpass.getBytes(), 0));
hanacon.setRequestProperty(“Authorization”, basicAuth);

BufferedReader in = new BufferedReader(new InputStreamReader(hanacon.getInputStream()));
String inputLine;

while ((inputLine = in.readLine()) != null) JASONrs += inputLine;
in.close();
} catch (Exception e) {
e.printStackTrace();
return “Some Problem”;
}
return JASONrs;
}

private class ShowDialogAsyncTask extends AsyncTask<Void, Void, String> {
@Override
protected String doInBackground(Void… args) {
return getOdata();
}

@Override
protected void onPostExecute(String result) {
TextView tv = (TextView) findViewById(R.id.MyTextResponse);
tv.setText(result);
}
}

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

ShowDialogAsyncTask aTask = new ShowDialogAsyncTask();
aTask.execute();
}

}

Just to keep it short, in the MainActivity.java we call the URL of the XSJS we designed previously and pass login credentials. Once the login is authorized we get the XSJS called and JSON data returned.

In the code above we called the XSJS service this way –

https://hanapracc5232886trial.hanatrial.ondemand.com/INFTEST1/INFTEST1.xsjs?$format=json

Once we receive the JSON from XSJS service, android pastes it on to the TextView in the UI end.

But, we can create numerous varied UI elements according to our requirement. In this scenario I just created a simple TextView just to test this whole environment features.

Part 3: Deploying the application in AVD or Mobile device

Note: If you are working in Corporate networks, AVD may not receive the JSON data due to firewall and proxy settings.

But, it works perfect when you install the app on your android device and connect to the internet.

You can directly install the app on your mobile device running on android by connecting the USB and enabling the developer feature. Once USB debugging is done, the app installs on your mobile device when Run through the Android Studio.

Connect to the internet and open the app installed on your mobile device

The JSON data is successfully acquired from the HANA database hosted on HCP and is shown onto the TextView of Android UI.

We can also call XSJS services with Inserting data through the parameters, indeed we can perform all the activities possible to build a mobile application according to our requirement.

Another example application designed to work on MySql database hosted in Internet.

Part 4: Extension Scenarios

Leveraging the HANA Database technology we can build high end efficient apps.