Skip to Content

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.

rootcert.PNG

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.

Capture.PNG


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.

Capture.PNG

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

Capture.PNG

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.

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Nicolas Picot

    Dear Eric,

    It’s a very interesting blog.

    There is something that I don’t understand : how do you manage the data to synchronize ?

    I mean : you are offline, the data are stored in the SQL Anywhere database and then the Raspberry PI go online. What’s happen when you call push_iot_messages ?

    Does the SQL Anywhere db send to HCP only the last temperature or automaticaly send the temperature that was stored when offline ?

    Do you have to manage a flag to know what was send to HCP or not ?

    Regards

    Nicolas

    (0) 
    1. Eric Farrar Post author

      Hi Nicolas,

      You are absolutely right, there is nothing in there to synchronize. If you are offline, the call will fail. The solution, as you say, is to manage a flag to know if it was sent to HCP or not.

      That is the subject of a follow-up blog post. Let me know if you have any questions after taking a look at that solution.

      Regards,

      – Eric

      (0) 
  2. Danny Van der Steen

    Hi Eric,

    trying to implement this but getting an error when executing the stored procedure.

    Could not execute statement.

    The secure connection to the remote host failed: The TLS handshake

    failed, error code 0x14090086

    SQLCODE=-990, ODBC 3 State=”HY000″

    File: “SendIOT.sql” on line 1, column 1

    CALL push_iot_messages( 

      host=’iotmmss000xxxxxxtrial.hanatrial.ondemand.com’, 

      device=’b216e4ca-7xxx-40e3-a3b7-9d88f103c27f’, 

      token=’6b79xxxxxx7xx245864e9bd83693a7′, 

      body='{ 

         “messageType”: 1, 

         “messages”: [ 

           {“Temperture”: 15}, 

           {“Temperture”: 30} 

         ] 

      }’ 

    )

    The xxx’s in the message hide the correct hex values they where correct.

    I am using Sql Anywhere 17 patch level 1211 also tried on SQL Anywhere 16 patch level 2127 but also same error.

    Any suggestions ?

    Greetings Danny.

    (0) 
    1. Eric Farrar Post author

      Hi Danny,

      The error you are getting is on the TLS handshake, which means there may be something wrong with the certificates.

      I took a look, and it turns out that HCP trial landscape has changed its certificate signing authority since I wrote this article. If you inspect the certificate chain you will now see that they use the Baltimore CyberTrust Root certificate.

      Try again using this certificate, and see if it works. Thanks.

      – Eric

      (0) 
  3. Pradnesh Dange

    Hi Eric,


    Thanks for the informative Blog.


    While Implementing this , i am getting following error


    Could not execute statement.

    Unable to connect to the remote host specified by

    https://iotmmspxxxxxxtrial.hanatrial.ondemand.com/com.sap.iotservices.mms/v1/api/http/data/0d3293c4-exxx-417d-a944-a19abfaxxxxc’

    SQLCODE=-981, ODBC 3 State=”HY000″

    Line 1, column 1

    CALL push_iot_messages(

    host=’iotmmspxxxxxxtrial.hanatrial.ondemand.com’,

    device=’0d3293c4-exxx-417d-a944-a19abfaxxxxc‘,

    token=’529axxxxxx9xx5f5b5f6d270fd4xxxxf’,

    body='{“messageType”: 1,”messages”: [{“temperature”: 15},{“temperature”: 30}]}’)

    The xxx’s in the message hide the correct hex values they where correct. Also the Message Type is changed


    I am using  SQL Anywhere 17 17.0.0.1062


    Any suggestions ?


    Warm Regards,

    Pradnesh

    (0) 

Leave a Reply