Using SAP SQL Anywhere with the SAP HANA Cloud Platform for the Internet of Things

[former_member199076]

At SAPPHIRE 2015 SAP announced the SAP HANA Cloud Platform for the Internet of Things, a set of services on top of SAP's cloud platform that extend its capabilities towards a full-fledged device cloud. As part of that announcement, SAP released a starter kit of sample code to let you start playing with the new platform capabilities right away.

Presently, the IoT edge devices can communicate with the cloud services using either HTTPS or websockets. This provides a lot of flexibility because anything that is capable of producing HTTP requests can become an IoT device for this service. The starter kit provides sample snippets for creating a sample device in Python.

In this blog post, we will create an IoT device using SAP SQL Anywhere. SAP SQL Anywhere is a small-footprint, embedded relational database that is ideally suited for environments running on the edge of a network. Despite its relatively small footprint, it packs in a tremendous amount of features including spatial data support, full-text indexing, a sophisticated query processor, and most importantly for this blog post, the ability to produce and consume web services.

You will need to do a couple of things before starting this demo:

1. Setup a trial account on the HANA Cloud Platform and complete all of the Getting Setup in the Cloud steps from the starter kit
2. Download and install the SAP SQL Anywhere Developer Edition.

Start by opening a terminal and creating a new directory, C:\SQLAIOT. (Note, SQL Anywhere is supported on a wide variety of platforms including Linux, Linux on ARM (Raspberry Pi), and OSX. These instructions assume Windows)

You can create a new database using the dbinit utility. We will create a new database called iotdevice.db.

dbinit iotdevice.db

Once the database is created, you can start a server running that database.

dbsrv16 iotdevice.db

According to the documentation, SAP HANA Cloud Platform requires HTTPS. This means that SQL Anywhere will need access to the SAP HANA Cloud Platform's root certificate in order to communicate with the service. If we use a browser to inspect the certificate chain for the SAP HANA Cloud Platform, we see that it uses the GTE CyberTrust Global Root certificate.

You likely already have this certificate on your machine. Export this certificate in Base-64 encoded X.509 format into a file called C:\SQLAIOT\gte.cer.

Even though the certificate is already installed in your operating system, SQL Anywhere has its own built-in certificate store. This is useful in an embedded context because you cannot always count on a specific root certificate being available on an embedded platform. To add the root certificate to the database, we need to issue some SQL. To do this, we will launch SQL Anywhere's graphical query tool, Interactive SQL. Run:

dbisql

(or, you can run it using Start -> SQL Anywhere 16 -> Administration Tools -> Interactive SQL)

Once it has launched, we need to connect to our iotdevice database.

The default username is dba, and the default password is sql. The database name is iotdevice. Because we did not declare a server name, it defaults to the name of the first database started, which is iotdevice. Enter the following SQL statement, and press F5 to execute it.

CREATE CERTIFICATE gte FROM FILE 'C:\\SQLAIOT\gte.cer';

This loads the GTE CyberTrust Global Root certificate into the database's own certificate store, using the name gte.

Next we will create a generic procedure for posting device messages. According to the documentation, we need to post to a URL of the form:

<protocol>://<host>/com.sap.iotservices.mms/v1/api/http/data/<device id>

The <protocol> that we will be using is HTTPS. The <host> will depend on which HANA Cloud Platform landscape you are using. To find the host, take a look at the URL of your instance of the IoT Cockpit; the host will be the same as what you need to use here.

Before a device can post messages to the IoT services, the device must be setup in the IoT Cockpit (you should have done this as part of the initial setup following the starter kit). When you setup a new device you should get two things:

• A unique security token
• A device id

We finally have all the pieces to create a generic SQL procedure to post message updates.

CREATE OR REPLACE PROCEDURE push_iot_messages (

  IN host LONG VARCHAR,

  IN device LONG VARCHAR,

  IN token LONG VARCHAR,

  IN body LONG VARCHAR)

URL 'https://!host/com.sap.iotservices.mms/v1/api/http/data/!device'

TYPE 'HTTP:POST:application/json'

HEADER 'Authorization: Bearer !token'

--PROXY 'http://myproxy' -- Uncomment this if you need to go through a proxy

CERTIFICATE 'certificate_name=gte'

There is a lot going here, so let's walk through it line-by-line:

Line 2,3,4 - Input parameters representing the host, device id, and security token for the request

Line 5 - The body is a JSON string representing the payload of the request

Line 6 - The URL of the request. !host and !device will be replaced with the input variables of the same name at runtime. This allows us to create flexible, generic web procedures.

Line 7 - Set the HTTP method to POST, and the content-type to application/json.

Line 8 - Set the OAuth authentication token as described in the documentation. !token will be replaced by the input variable of the same name at runtime.

Line 9 - Uncomment this line if you need to go through a proxy, and set the proxy address

Line 10 - Set the HTTPS certificate to the GTE CyberTrust Global Root we added to the database

Now we have everything we need to push messages to the IoT Services. The message body is a JSON formatted document that corresponds to the Message Type you setup for your device in the IoT Cockpit. In my case, I only have a single message type (1), and it accepts a single field: an integer called Temperature. More information on the message format can be found in the documentation. In this example, I am sending two temperature readings (15 and 30) in a single message.

CALL push_iot_messages(

  host='iotmmsiXXXXXXtrial.hanatrial.ondemand.com',

  device='my-device-id',

  token='my-token',

  body='{

     "messageType": 1,

     "messages": [

       {"Temperture": 15},

       {"Temperture": 30}

     ]

  }'

);

Execute this SQL. The results of the HTTP POST will be displayed in the results window. If successful, you should get a 202 Accepted response, along with a message telling us that two messages were received.

If we go back to the IoT Cockpit and browse the stored messages, we can see our messages have been successfully delivered.

There we go! We have successfully used a standalone SQL Anywhere embedded database as a remote device for the SAP HANA Cloud Platform for the Internet of Things.

In this example we hand-coded the JSON message document. Of course, things get far more interesting (and useful) when you start generating the JSON documents directly out of the database tables. This may be the focus of a future blog post.