Playing with WhatsApp and SAP HANA Cloud Platform

[former_member193140]

Let's do something fun with WhatsApp and SAP HANA Cloud Platform. We will build an app whereby you can take a picture from your smartphone and send it via WhatsApp, save it to HANA database and finally view the picture from the browser. I named it as "Whana" = WhatsApp and Hana :smile:

Prerequisites

• SAP HANA Cloud Platform: SAP HANA Cloud Platform
• SAP HANA Cloud Platform SDK for Java EE 6 Web Profile: https://tools.hana.ondemand.com/sdk/neo-javaee6-wp-sdk-2.40.9.1.zip
• Opensource WhatsApp client - Yowsup Library: https://github.com/tgalal/yowsup
• Dedicated mobile phone number for WhatsApp
• Python: Download Python | Python.org
• Java SE JDK 7: Java SE Development Kit 7 - Downloads | Oracle Technology Network | Oracle

Diagram

Yowsup Installation & Configuration

After you have downloaded the Yowsup library, copy the folder yowsup-master to the Python folder (in my case is C:\Python34). You also need to download the python-dateutil and copy dateutil folder to C:\Python34\Lib.

Now go to https://coderus.openrepos.net/whitesoft/whatsapp_sms to get the WhatsApp code. Enter your dedicated mobile phone number and select SMS.

If no error, you will get the SMS message and note down the number:

WhatsApp code 192-828

Under C:\Python34\yowsup-master\src, copy the config.example to yowsup-cli.config, and indicate the following:

cc=country code; for example: 65

phone=your phone number: for example: 651234567 (without +)

leave the id and password blank at the moment.

Execute the following command to register with WhatsApp:

python C:\Python34\yowsup-master\src\yowsup-cli --register 192-828 --config C:\Python34\yowsup-master\src\yowsup-cli.config

If no error, you will get the following message:

status: ok

kind: free

pw: S1nBGCvZhb6TBQrbm2sQCfSLkXM=

price: 0,89

price_expiration: 1362803446

currency: SGD

cost: 0.89

expiration: 1391344106

login: 651234567

type: new

Copy the password S1nBGCvZhb6TBQrbm2sQCfSLkXM= and paste into pw field in yowsup-cli.config. In the end your yowsup-cli.config will look like this:

cc=65

phone=651234567

id=

password=S1nBGCvZhb6TBQrbm2sQCfSLkXM=

Now let's test to send a message to this phone number 6597312234. Execute the following command:

C:\Python34>python C:\Python34\yowsup-master\src\yowsup-cli --send 6597312234 "Test message" --config C:\Python34\yowsup-master\src\yowsup-cli.config

If no error, you will see the following result and the message will be sent to 6597312234:

Authed 651234567

Sent message

I have added the functions to save the image received from the WhatsApp to the local folder: C:\java\imageWA. Just overwrite the CmdClient.py in the  folder Example and downloader.py in folder Media.

Java Folder Monitoring

On this part, we will create the Java program to monitor the folder where the Yowsup stored the WhatsApp image (i.e., C:\java\imageWA) and check if there is any image file. If there is, the program will read the file and insert into the HANA database.

Below is the snippet of the java code:

static final String IMGFolder = "C:\\java\\imageWA";

String INSERT_PICTURE = "INSERT INTO \"NEO_CG2SX3P5XHHQEO58DKM7BWU0V\".\"p1940803061trial.fd2.data::mytable\" VALUES(?,?)";

public synchronized void insert(String fileName) throws Exception, IOException, SQLException{

  FileInputStream fis = null;

  PreparedStatement ps = null;

  Date currentDate = new Date();

  String s = Long.toString(currentDate.getTime() / 1000);

  System.out.println(s);

  try {

  System.out.println("filename: " + fileName);

  File file = new File(IMGFolder + "\\" + fileName);

  fis = new FileInputStream(file);

  ps = conn.prepareStatement(INSERT_PICTURE);

  ps.setString(1, s);

  ps.setBinaryStream(2, fis, (int) file.length());

  int rowsInserted = ps.executeUpdate();

  conn.commit();

  if (rowsInserted > 0) {

  System.out.println("A new record was inserted successfully!");

  }

  if(file.delete()){

  System.out.println(file.getName() + " is deleted!");

  }

  else{

    System.out.println("Delete operation is failed.");

    }

  } finally {

  ps.close();

  fis.close();

  }

  }

HANA Cloud Platform Setup

I will go through the key important files. You can find the complete source code on GitHub: https://github.com/ferrygun/Whana

mytable.hdbtable

We need to create a table, mytable.hdbtable in SAP HANA database to store the image from the WhatsApp.

mytable.hdbtable
table.schemaName = "NEO_CG2SX3P5XHHQEO58DKM7BWU0V"; table.tableType = COLUMNSTORE; table.description = "My Table"; table.columns = [           {name = "File_Name"; sqlType = NVARCHAR; nullable = true; length=20;},           {name = "File_Content"; sqlType = BLOB; nullable = true;} ];

GetImage.xsjs

Create the GetImage.xsjs to retrieve the image binary stream from the HANA database:

  var id = $.request.parameters.get('id');

    var conn = $.db.getConnection();

    try {

        var query = "Select \"File_Content\" From \"NEO_CG2SX3P5XHHQEO58DKM7BWU0V\".\"p1940803061trial.fd2.data::mytable\" Where \"File_Name\" = " + id;

        var pstmt = conn.prepareStatement(query);

        var rs = pstmt.executeQuery();

        rs.next();

        $.response.headers.set("Content-Disposition", "Content-Disposition: attachment; filename=filename.jpg");

        $.response.contentType = 'image/jpg';

        $.response.setBody(rs.getBlob(1));

        $.response.status = $.net.http.OK;

    } catch (e) {

        $.response.setBody("Error while downloading : " + e);

        $.response.status = 500;

    }

    conn.close();

GetFileName.xsjs

Create the GetFileName.xsjs to list down all the image file names so user can select  from the SAPUI5 table:

var query = "Select \"File_Name\" From \"NEO_CG2SX3P5XHHQEO58DKM7BWU0V\".\"p1940803061trial.fd2.data::mytable\" ";

function close(closables) {

    var closable;

    var i;

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

        closable = closables[i];

        if (closable) {

            closable.close();

        }

    }

}

function getFileName() {

    var FNameList = [];

    var connection = $.db.getConnection();

    var statement = null;

    var resultSet = null;

    try {

        statement = connection.prepareStatement(query);

        resultSet = statement.executeQuery();

        while (resultSet.next()) {

            var fname = {};

            fname.file_name = resultSet.getString(1);

            FNameList.push(fname);

        }

    } finally {

        close([resultSet, statement, connection]);

    }

    return FNameList;

}

function doGetFileName() {

    try {

        $.response.contentType = "application/json";

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

    } catch (err) {

        $.response.contentType = "text/plain";

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

        $.response.returnCode = 200;

    }

}

doGetFileName();

Embed an Image in HTML Code

In order to view the image, we will call the function GetImage.xsjs and embed the picture into the HTML code with base64 encoding using the following tag:

<img src="data:{mime};base64,{data}" alt="My picture"/>

where {Mime} is in ‘image/imagetype’ format (eg. ‘image/jpg’ or image/png) and {data} is the base64 encoded

Here is an example when I viewed the source code of the "WhatsApp" image in the Chrome browser:

The actSearch function in the view controller will call the GetImage.xsjs based on the given image ID (filename) and encode the content in the base64 format.

actSearch: function(fname) {

        var xmlHTTP = new XMLHttpRequest();

        xmlHTTP.open('GET', '../fd2/image/GetImage.xsjs?id=' + fname, true);

        xmlHTTP.responseType = 'arraybuffer';

        xmlHTTP.onload = function(e) {

            var arr = new Uint8Array(this.response);

            var raw = String.fromCharCode.apply(null, arr);

            var b64 = btoa(raw);

            var dataURL = "data:image/jpeg;base64," + b64;

            document.getElementById("image").src = dataURL;

        };

        xmlHTTP.send();

    }

Running the App

Firstly you need to open the tunnel to the SAP HANA Cloud Platform. Under folder neo-javaee6-wp-sdk, execute the following command:

neo open-db-tunnel -h "hanatrial.ondemand.com" -u "p1940803061" -a "p1940803061trial" --id "fd2"

Replace "p1940803061" with your HANA trial account ID and "fd2" with your HANA instance.

If successfull, you will get the the password: Df2sxp5HH8H6BGv and user: DEV_5GKYRR9GKKT9NTV5RD7U0DGMG. Update the pwd and user value in the config.properties:

user=DEV_5GKYRR9GKKT9NTV5RD7U0DGMG

pwd=Df2sxp5HH8H6BGv

Run the Yowsup. Execute below command:

python C:\Python34\yowsup-master\src\yowsup-cli --interactive <phone_number> --wait --autoack --keepalive --config C:\Python34\yowsup-master\src\yowsup-cli.config

And finally run the Java Folder Monitoring app:

java FolderMonitor

Demo Video

Summary

In other case, I have also built a CCTV home security and upload the video clip to the DropBox (or HANA). Here is the video:

Hope your are enjoy "Whana" and thank you for reading my blog. Please let me know if you have any question/comment.