Further Adventures with SQL Anywhere Developer Edition: Using UI5, oData, XS, HCP, Node.js and the YAAS Earth Observation service

[Robert_Russell]

 

Developing an UI5, XS & oData App with the SQL Anywhere Developer Edition

Background

I'll start by going into the background and inspiration for this blog, as it does bring together all the component parts. My blog has been inspired by a number of events. Firstly retrieving or backing up data from cloud systems. I had previously mentioned that Amazon had stopped my AWS instance that was running my SQL Anywhere installation in my last SQL Anywhere blog here. Also last year SAP decided to shut down the “HANA XS” based HANA Cloud Platform (HCP) instances. A new version is available with “HANA MDC” instances but any setup does get deleted after 7 days of inactivity, which is and continues to be really annoying.

While I have stopped using the AWS for now, I have found myself still using the HCP as there is so many good things going on with the platform to stop using it. ( I’ll Just have to remember not to go out of internet reach for 7 days.)

Another recent event was answering a question on this new SAP Community site in the SQL Anywhere -space- tagged area? The question related to XSJS and I replied with a suggested answer as I have been using XS for some time. However I was surprised to find a while back that SQL Anywhere has an XS Database API. I have always been impressed with SQL Anywhere since discovering the product here on SCN (SAP Community) and it always lets me achieve more with any of my demos than without it on my laptop.

Also I thank Moya Watson for sending me a link to the Earth Observation on YAAS which turned out to be the final piece for this blog. I have an ongoing interest in geospatial topics and thanks to the SAP Community Network I got to present a session at TechEd Las Vegas earlier this year on those topics. Therefore Moya sending the link to the Earth Observation service did trigger my interest and I found I could setup SQL Anywhere to help me get the satellite images from the YAAS site.
And because I came across SQL Anywhere first here at SCN/ SAP Community it seems a natural home to share my learnings on my use of the SQL Anywhere Developer Edition.

Creating this blog about an application I recreated using SQL Anywhere I did realise there are parts that I would change. If I had known at the beginning what I know now then some things would be setup differently 🙂 . However it is fun using SQL Anywhere and I would strongly suggest you should try it out if you haven’t already done so. I have used SQL Anywhere to help me improve my relevance on SCN, analyse SCNblogs twitter links and map crime. You can download a developer edition at the link below. SQL Anywhere also offers a lot more that I have not checked out yet but I will be doing so in 2017 - such as IOT and mobile. The main statement on the download site is “Create custom mobile and IoT applications” so I have to check that out soon….

 

SQL Anywhere Developer Edition Download Link

The Application “It’s An SCN World” on SQL Anywhere

My original blog found at the this link, covered my “It’s An SCN World” app but running on the HCP. It allowed any SCN member with a SAP ID account to register their location on a map.Using open source javascript libraries allowed me to show a world map in 2d or 3d for any registered user's location. As per the screenshots that follow.

The HCP application no longer works as SAP deleted the HANA XS HCP accounts as stated in the introduction.
https://blogs.sap.com/2015/08/22/its-an-scn-world-on-the-sap-hana-cloud-platform/

1) 3d World view based on Cesium used with OpenLayers3. There is a great javascript library that combines both OpenLayers and Cesium called ol3-cesium.  http://openlayers.org/ol3-cesium/ . The map shows the location of the registered users on my my in the 3d Cesium map running on SQL Anywheres web server.



2)  2d view below, the image on the left shows an Earth Observation Service satellite image overlaid on an HERE based map shown on the right. The satellite image is being served by an open source geospatial program called GeoServer. The entire map is setup using Openlayers and running in SQL Anywhere’s integrated web server.

Building the components of the Application

I will use the layout below to go through each of the components to create the SCN World web page on SQL Anywhere. Not all components are used on the final page but all are used in the creation of the page.



A brief explanation of each component and I will explain more detail in the relevant sections in my blog.
SQL Anywhere Web Server - I use SQL Anywhere’s integrated web server to host the web page, it serves all the open source javascript mapping libraries and the openUI5 code.

SQL Anywhere oData Server - I use this to enable a oData service to select the Earth Observation image I will use. It enables sorting of the data and selection of the image via a openUI5 based page.

SQL Anywhere Remote Table/Server - The remote server table service of SQL Anywhere is used with a Node.js based XS API call. This is via the HANA Cloud platform tunnel to make a connection to my trial HCP HANA account.

Node.js - I installed a version of Node on my Ubuntu machine to enable XS api and the reverse proxy capabilities to enable the SQL Anywhere web server and oData service to be consumed on one page (it is how I enabled CORS between oData and the web server). The Node.js service is the entry point of the page.

YAAS Earth Observation Service - I used this service to download the satellite images used on my application. These satellite images are large files (over 300mb in my use case) so not suited for a dynamic web page 🙂 but I setup SQL Anywhere to help me select the image for download based on cloud coverage - more on this in a the later section.

Linux Ubuntu 16: VirtualBox / Network Setup

I use virtualbox on my Mac and created a virtual machine with Ubuntu 16 running SQL Anywhere. For the network setup I use a virtualbox host only adapter and a NAT adapter. I use the host only adapter with dnsmasq installed on my Mac for hostname resolution. The hostnames I have used are,

192.168.56.149 sqlany17 sqlany17.haw odata.sqlany17.haw njs.sqlany17.haw

I used the same IP address but chose different names for each service on the assumption that one day I may move the service - e.g. use another web server on another machine but the intention is to still use the same setup, as I would just move the hostname to a new IP address. There is no need to setup dnsmasq for hostname resolution it could be a local host file entry for a local installation of SQL Anywhere. However to keep things simple (as possible ;)) I will use the hostname for the rest of the blog linked to the service it provides e.g odata.sqlany17.haw is the oData SQL Anywhere service hostname.

SQL Anywhere Web Server

I will pick up the setup of my web server from my blog on Using Twitter & Bitly APIs with OpenUI5 running on SQL Anywhere where I setup the web server to host openui5. However for the web server there is a change to the following...

SSL / HTTPS setup

I will continue to use the SSL / HTTPS setup as before but as I introduce more services on different hostnames I will use SQL Anywhere’s createcert command to setup a Root Certificate Authority. This way I can avoid self-signed certificates and can use my own root CA to sign multiple certificate requests. As per my previous blog I will follow the examples on this very useful WIKI page as it explains the details around certificates for SQL Anywhere as well. Avoiding self signed certificates was useful for the Node.js reverse proxy setup that I will cover later.

https://wiki.scn.sap.com/wiki/display/SQLANY/Generating+X.509+Certificates+for+Secure+Communication+...

For my root CA I followed the following section
“Example 2: Generating an Enterprise Root Certificate that can be Used to Sign Other Certificates”
From the createcert command the important part for me was answering the prompts as instructed

Certificate Authority (Y/N) [N]: y

…
…

Enter file path to save certificate: entpublic.pem
Enter file path to save private key: entprivate.key
Enter password to protect private key: password
Enter file path to save identity: entserver.pem

 

The entpublic.pem file is the public certificate that I imported into all my browsers (keychain tool for my Mac) that would enable trust in my web browser for all subsequent certificates I generate, that are signed by my root CA.

A screenshot of my certificate in the keychain access on my Mac below. As I generated it on my computer I set it up for trust.




Now that I have a root CA certificate I can generate a new certificate for my SQL Anywhere Web Server.


Following the section “You can now use this key to sign other generated keys that can be used to start lower-tier database servers:”  from section two on the wiki page.

192.168.56.149 sqlany17 sqlany17.haw odata.sqlany17.haw njs.sqlany17.haw

From the hosts for my Ubuntu server I use sqlany17.haw as the common name for my certificate so the important lines from the createcert command for me were
..

 

Common Name: sqlany17.haw

Enter file path of signer's certificate: entserver.pem

..
..
Enter file path to save certificate:  SQL2public.pem
Enter file path to save private key: SQL2priv.key
Enter password to protect private key: password
Enter file path to save identity: SQL2server.pem

I made sure entserver.pem was used for the signers certificate as this is my root CA generated earlier.  I generated new files for my SQL Anywhere Web Server meant I had to make some changes to the setup.

Web Server Startup Changes to my SQL Anywhere 17 installation

The line in bold below is the reference to the file SQL2server.pem which is the combined certificate and key file and I use HTTPS on port 8443.

 

dbspawn dbsrv17 -zoc /root/log/weblog.txt -xs  "HTTP(port=8081;TO=3600)","HTTPS( port=8443;FIPS=N;IDENTITY="/root/SQLAnyROOTCA/SQL2server.pem";IDENTITY_PASSWORD={PASSWORD};TO=3600 )","ODATA( SecureServerPort=8444;SSLKeyStore=/root/SQLAnyROOTCA/keystore;SSLKeyStorePassword={PASSWORD}; ServerPort=8082)" /home/robert/sql17db/sql17

I now had my SQL Anywhere web server up and running with a signed certificate.

SQL Anywhere oData Server

SQL Anywhere comes with oData support and there are various ways to setup the oData server and I followed the integrated database method from that link. This way I do create another server which can’t be used to serve other non-OData content, such as HTML files. If you are interested in the oData server setup of SQL Anywhere it is worth checking out the limitations on this link. It does comply with OData protocol version 2 specifications. From my reading of the help on SQL Anywhere 17, the oData server is based on Jetty 9.3.7

oData Jetty Server setup for HTTPS

The SQL Anywhere help pages covers the process to setup the Jetty server with HTTPS at this link.

http://dcx.sap.com/index.html#sqla170/en/html/3bf33be36c5f1014ab87fea4094ce065.html

However it covers a self signed certificate command whereas I required to sign the certificate with my SQL Anywhere generated root certificate (again for my Node.js setup). From my list of hosts I will use odata.sqlany17.haw as the common name for my oData service certificate.
192.168.56.149 sqlany17 sqlany17.haw odata.sqlany17.haw njs.sqlany17.haw

Using createcert with the following options

Common Name: odata.sqlany17.haw
Enter file path of signer's certificate: entserver.pem
..
..
Enter file path to save certificate:  ODATApublic.pem
Enter file path to save private key: ODATAprivate.key
Enter password to protect private key: password
Enter file path to save identity: ODATAserver.pem

As before my root CA entserver.pem is used to sign my oData service with a common name of odata.sqlany17.haw.

Keystore Requirements

I checked the oData Server help for Jetty and found that a keystore is required for HTTPS setup. The Java command keytool can be used to create a keystore and this command is available with SQL Anywhere as per my installation. I first however used openssl to convert my odata.sqlany17.haw public certificate and related key into a pkcs12 file for the keystore.

Openssl was already available on my Ubuntu setup and the location of the keytool for me was located in the install directory of SQL Anywhere.

/opt/sqlanywhere17/bin64/jre180/bin/keytool

Converting the certificates created with the createcert command for my oData service.

• openssl pkcs12 -inkey ODATAprivate.key -in ODATApublic.pem -export -out ODATAjetty.pkcs12

Create the keystore

• keytool -importkeystore -srckeystore ODATAjetty.pkcs12 -srcstoretype PKCS12 -destkeystore keystore

Relevant part of the of the keytool command is the keystore file which I reference in my startup of SQL Anywhere in bold below. I use port 8444 for HTTPS oData requests.

dbspawn dbsrv17 -zoc /root/log/weblog.txt -xs  "HTTP(port=8081;TO=3600)","HTTPS(
port=8443;FIPS=N;IDENTITY="/root/SQLAnyROOTCA/SQL2server.pem";IDENTITY_PASSWORD=
{PASSWORD};TO=3600)","ODATA(SecureServerPort=8444;SSLKeyStore=/root/SQLAnyROOTCA/key
store;SSLKeyStorePassword={PASSWORD};ServerPort=8082)" /home/robert/sql17db/sql17

I will cover how I setup an actual oData provider when I cover the way I use the YAAS Earth Observation Service. First though I move onto the SQL Anywhere XS API.

SQL Anywhere XS API

SQL Anywhere supports an XS Database API http://dcx.sap.com/index.html#sqla17api/en/html/8167aeda6ce210148850fbd9b3c3c94a.html

It is worth noting it is XS classic and the API is made available with Node.js  and the API is for the database. It does not offer some of the other features of XS classic (e.g. the $. Util libraries) therefore I set up another web server in Node (i’m collecting them :)) to enable me to setup database XS API calls in a web page. Also a reverse proxy which is a key piece that will enable be to bring it all together on one web page.

Node.js - XS API, Express and Proxy Server with HTTPS

When I installed SQL Anywhere I used the root account on my Ubuntu Linux installation. The XS API for SQL Anywhere expects to find the NODE_PATH environment variables for connections and I also found I had to set the LD_LIBRARY_PATH for ubuntu. I set these in the root profile so that every time I logon these paths are set alongside the SQL Anywhere configuration script.

#set these for nodejs to connect



export NODE_PATH=/opt/sqlanywhere17/node

export LD_LIBRARY_PATH=/opt/sqlanywhere17/lib64

#setup SQL env



. /opt/sqlanywhere17/bin64/sa_config.sh

For a while I had a standalone node script using XS API calls that could connect to my SQL Anywhere database and generate a GeoJSON output. The GeoJSON was important as I would need in my “It’s an SCN World” app for the location of all registered users. At this point I have to state that I don’t need XS to generate GeoJSON with SQL Anywhere as it is more than capable of doing so without it. However I knew how to create GeoJSON with XS and it was a good example for me to learn how to use the XS API with Node.js as a learning exercise.

There are 3 things I need to achieve with Node and XS

1. HTTPS web server setup


2. Reverse Proxy setup as I was going to use this to setup CORS


3. How to setup XS API GeoJSON call within a web service

I’ll start with point 1, so tempted to start in reverse order…..;)

 

1) Node Web Server HTTPS Setup
.

I used the SQL Anywhere createcert command and my common name for my node service is njs.sqlany17.haw

192.168.56.149 sqlany17 sqlany17.haw odata.sqlany17.haw njs.sqlany17.haw

Using createcert with the following options

Common Name: njs.sqlany17.haw
Enter file path of signer's certificate: entserver.pem
..
..
Enter file path to save certificate:  njspublic.pem
Enter file path to save private key: njskey.pem
Enter password to protect private key: password
Enter file path to save identity: njsserver.pem

As before my root CA entserver.pem is used to sign my Node.js service with a common name of njs.sqlany17.haw.

I had fun and games setting up Node with HTTPS: Issue with password on the key file

From my experience I found the Node web server with HTTPS would not start due to issues with a password on the  private key created with the createcert command. I removed the password with the following openssl command.

openssl rsa -in key.pem -out newkey.pem

A link to a site with information as to what the implications are in removing passwords from any keys https://wiki.apache.org/httpd/RemoveSSLCertPassPhrase. Googling setting up HTTPS with Node.js does return a lot of results so I will come back to this at some point but my entire code I use is at the end of my blog. Any recommendations to improve my setup, then please let me know. However the following commands highlight the HTTPS certificate section, where I use my newkey.pem file from the above openssl command. All the other files are generated with the createcert command as previously covered.

const options = {

 key: fs.readFileSync('newkey.pem'),

 cert: fs.readFileSync('njspublic.pem'),

ca: fs.readFileSync('entpublic.pem')

};

 

2) Reverse Proxy setup as I was going to use this to setup CORS

Cross-origin resource sharing (CORS) “A resource makes a cross-origin HTTP request when it requests a resource from a different domain than the one which the first resource itself serves” taken from https://developer.mozilla.org/en-US/docs/Web/HTTP/Access_control_CORS
I have setup multiple services on different hostnames and ports which means I need a mechanism to serve all parts from one location. The way I chose to do this is with a reverse proxy library in Node.js. I chose to use node-http-proxy at this URL.

https://github.com/nodejitsu/node-http-proxy

Using node-http-proxy I could setup a reverse proxy to allow my Node.js script to call all my services and create my “It’s an SCN World” app with all the component parts.
Again the full Node.js script is below but to highlight the important parts I found with the reverse proxy setup are

* var apiProxy = httpProxy.createProxyServer({changeOrigin: true});

Explanation of the changeorigin option from the project's GitHub page.
changeOrigin: true/false, Default: false - changes the origin of the host header to the target URL

The change origin parameter got around the issue where I was accessing my reverse proxy on my njs.sqlany17.haw but calling for example odata.sqlany17.haw. It was failing until I set the changeOrigin option to true. HTTPS calls were failing and there appeared to be a way to also tackle the issue with Subject Alternate Names on certificates but I went with the changeOrigin parameter.

* ca: fs.readFileSync('entpublic.pem')

I found I had to read the root CA certificate from file each time I used the reverse proxy.

3) How to setup XS API GeoJSON call within a web service 

For a while I was using Node with only the  node-http-proxy and making calls to SQL Anywhere via XS successfully. However for some reason it was not returning all the data in the browser. So I looked for alternatives and ended up using Express a web application framework for Node.js. I am now starting out with Express and it does appear to offer a lot more options for me to explore in my complete setup. However for now I only use Express combined with node-http-proxy and my XS calls. The base of my setup (which again can be found at the end of the blog) is based on https://codeforgeek.com/2015/12/reverse-proxy-using-expressjs/ but I modified the setup in the link to cover HTTPS and my XS calls.

SQL Anywhere Remote Server / Remote Table

I first connected SQL Anywhere to my HCP trial account on a Windows laptop and covered that here.
So for my Ubuntu Linux setup I was interested in making the connection again but this time had to use Linux….

So I downloaded the TomCat 7 SDK (link) downloaded the trial HANA client from the SAP Store (link) setup the tunnel to my HCP trial account HANA (link) .

I had some issues with the trial HANA client software and a missing libary.

ldd libodbcHDB.so
linux-vdso.so.1 =>  (0x00007ffe53373000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007fbf639dc000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fbf637d8000)
libpam.so.0 => /lib/x86_64-linux-gnu/libpam.so.0 (0x00007fbf635c9000)
libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007fbf63391000)
libaio.so.1 => not found

I installed the following on my Ubuntu server to resolve the issue.

sudo apt-get install libaio1 libaio-dev

Also making the SQL Anywhere remote server command connections to my trial account proved a bit of a challenge. I setup the following environment variable

export ODBCINI=/etc/odbc.ini

And the contents were as follows

[HANADB1]
servernode=localhost:30015
Driver=/usr/sap/hdbclient/libodbcHDB.so
description=HDB
databasename={My HCP SCHEMA NAME}

And I could connect with interactive SQL

dbisql -c "dsn=HANADB1"

However it could not connect using the ODBC.ini reference in the remote server command.
In the end I had success with a full DSN in the connection parameter.

CREATE SERVER "df" CLASS 'HANAODBC' USING 'DRIVER=/usr/sap/hdbclient/libodbcHDB.so;UID={MY_SCHEMA};PWD={MY_PASSWORD};SERVERNODE=localhost:30015';

 

Link to the help page for ODBC which I found useful for my Linux setup.

HANA View Setup

As I mentioned right at the start I had luckily rescued my data from the deleted HANA XS trial HCP account. Quite lucky that I did download it all, as I read the blog about the deletion fairly near the deletion time. It would have been annoying to have lost my work on the HCP through no fault of my own. But lesson learnt that I need to backup my data in the cloud and document any learnings rather than leaving it all in the cloud. So I had my data from HCP and purely driven by the fact I wanted to connect SQL Anywhere to my new HCP HANA account, I put the data back in my new trial HCP instance. A really small dataset for the “It’s an SCN World” app but just trying out the connections before moving onto bigger things.

HANA HCP SQL to create a view of my SCN Users table which contains location details.

CREATE VIEW {MY_SCHEMA_NAME}.SCN AS SELECT * FROM "NEO_7YZMZC83MT9WX3OCFHA5HFEQX"."p{xxxxxxx}trial.hihanaxs.neogeoxs::SCNUSER1";

**NEO_7YZMZC83MT9WX3OCFHA5HFEQX"."p{xxxxxxx}trial.hihanaxs.neogeoxs was the old HANA XS schema name that I uploaded entirely up to my new HCP instance. I wanted a view called SCN to the table in my new schema.

SQL Anywhere to create a remote table (via the remote server) to the HCP view created above.

CREATE EXISTING TABLE "dba"."ITSANSCNWORLD" (

"scnuser" VARCHAR(90) NOT NULL,

"text" NVARCHAR(36) NOT NULL,

"lat" NUMERIC(12,9) NOT NULL,

"lon" NUMERIC(12,9) NOT NULL

) AT 'df..{MY_HCP_SCHEMA}.SCN';

 

I can now use SQL statements on my SQL Anywhere server to query the view/table in my HCP trial instance. As shown below.

 


I have chosen to show the anonymous users registered as the text column could also contain a twitter ID of the registered user. (Although masked the scnuser column was already a HASHed value to hide any SAPIDs as I didn’t want to store them in my original app in my HCP account.)

XS API Code for GeoJSON

Now I had all the parts setup I could now use the XS API to create GeoJSON for my final app. (again I keep repeating:) but the entire code used for my Node setup is at the end)  Extracted GeoJSON part is here.

//////////////////Location around the world

app.get('/XSJ', function (req, res) {

conn.connect();

var list=[];

var select_all_sales_orders_query =  'select "text", "lat", "lon" from "ITSANSCNWORLD"   ' ;

stmt = conn.prepareStatement( select_all_sales_orders_query );

stmt.execute();

var result = stmt.getResultSet();

function createSDNWorldpoint(result) {

var geometry = JSON.parse(result.getString(2)); // GeoJson is Object

return {

type: "Feature",properties: {    text : result.getString(1)

                                       },

//just with co-ords

"geometry": {type: "Point", coordinates:[ result.getDecimal(3)  ,  result.getDecimal(2)  ]}





   };

}

while ( result.next() )

{

                       var shapeEntry = createSDNWorldpoint(result);

                       list.push(shapeEntry);

}

//var resp = JSON.stringify({

var recordset = JSON.stringify({

                                   "type":"FeatureCollection",

                                   "features": list

                   });

////////



res.send(recordset);

conn.disconnect();

});

//////END of locations around the world

 

I am going to thank Stoyen Manchev for his blog again as I keep on using the variable select_all_sales_orders_query in his blogs honour 🙂 even though it has nothing to do with sales! I keep it has I learnt a lot of great things from Stoyen on the HCP. Also it does bring into perspective for me that all the libaries and functionality in the original XS environment I relied upon (although it is all XSA nowadays which I have not tried but I am starting out with Node 😉 ) https://blogs.sap.com/2013/10/17/8-easy-steps-to-develop-an-xs-application-on-the-sap-hana-cloud-pla...

I am now going to come onto the Earth Observation Service and how I could use this service to download a satellite image to use on my map. However first I had to choose a location and place to use as a demo for the service. Only one place would do for me and luckily for me someone from Japan had registered on my SCN world map, so I had my example latitude and longitude to work with for my demo.

A way to setup SQL Anywhere to download Earth Observation Service data

Time for break - time for a rant on the YAAS service…..

<RANT>




I am really amazed at how much I came to detest the page above (a first world problem) while using the YAAS API documentation. I think at the time something was wrong with the site. It no longer happens but when I clicked looking for help to be shown boxes dropping into a hole felt like no-one cares at YAAS about my search on their site.  They had set up a conveyer belt especially for me to deliver my click down a gaping hole to nowhere! (I hate it) a simple apology would be better for me and no animation.
</RANT>
I had a location in Japan that I would use for my demo but I needed some guidance on how to use the Earth Observation Service. A thank you to Wenzel Svojanovsky from me for his blog. As I based my SQL Anywhere services on his example API calls but with my Japan location.

https://blogs.sap.com/2016/12/07/port-of-antwerp-from-space-perspective/

Also I followed Sayon Kumar Saha’s blog, thank you Sayon.
https://blogs.sap.com/2016/11/25/sap-earth-observation-analysis-microservices-on-yaas/

Also I need to point out that the YAAS Earth Observation service is in beta and is stated to change in the new year. It is not clear to me whether there will still be a free option when it does change next year. As it is in beta the service is changing regularly, which can lead to some frustrations with the service (and may have triggered that rant earlier ;)) . So I work around some things that should be more straight forward only because I can’t rely on the stability of the service.

Once I had by authentication tokens from YAAS, I would use the same SQL Anywhere methods as per my experience with the Twitter API that I mention in my blog here.

I setup a SQL Anywhere function to call the YAAS service

ALTER FUNCTION "dba"."earthauthtoken_f"( in "u" long varchar,in "h" long varchar,in "body" long varchar )

returns long varchar

url '!u'

certificate 'file=/var/tmp/VeriSignClass3PublicPrimaryCertificationAuthority-G5.crt'

type 'HTTP:POST:application/x-www-form-urlencoded'

header '!h'

 

For the certificate file I exported the api.beta.yaas.io root certificate VeriSign Class 3 as shown in the image below (only the root certificate was required).

 


I setup a procedure to make the authentication and it contains my client_id and secret (which I have removed from the code as you need your own).

ALTER PROCEDURE "dba"."eathauthtoken_p"() result ( html_string LONG VARCHAR )

BEGIN

DECLARE K long VARCHAR ;

DECLARE S long VARCHAR ;

DECLARE BKS long VARCHAR ;

DECLARE h long VARCHAR;

DECLARE u long VARCHAR;

CALL sa_set_http_option('SessionTimeout', '5');

SET  TEMPORARY OPTION remote_idle_timeout = 100;

call dbo.sa_set_http_header( 'Content-Type', 'application/json');



--Twitter BASE64 of <API key>:<API Secret>

--set BKS = BASE64_ENCODE(string( K ,':',S));

--EARTH URL - to get Bearer Token

set u = 'https://api.beta.yaas.io/hybris/oauth2/v1/token';

--set h = string ('Authorization: Basic ', BKS );

set h = 'content-type: application/x-www-form-urlencoded';

--Use function to get the Bearer Token from Twitter

select earthauthtoken_f(u, h, 'grant_type=client_credentials&client_id={MY_CLIENT_ID}&client_secret={MY_SECRET}&scope=sap.eoa_service');

END

 

The above is a copy of my Twitter setup mentioned in my other blog earlier but this time I added a web service to create a JSON response which is enabled by the “call dbo.sa_set_http_header” line in bold in the above procedure.

To actually create the web service I used the following.

CREATE SERVICE "zeartha" TYPE 'RAW' AUTHORIZATION OFF USER "dba" AS call "eathauthtoken_p"();

To call the authentication service I can use a direct call to my SQL Anywhere web service on https://sqlany17.haw:8443/zeartha  as shown in the image below.




Now I had my authentication sorted I intended to set up a openUI5 page to query the satellite images and be able to sort them by cloud coverage. I learnt from Wenzel’s blog that cloud coverage is important to the quality of the image. So my idea was to create an openUI5 page which calls an oData service which could sort the returned satellite images by cloud coverage.
So first I need a table and a SQL Anywhere oData Producer.

I created the following table,

CREATE TABLE "dba"."eotable" (

"ID" LONG NVARCHAR NOT NULL,

"CLOUD" DECIMAL(8,3) NULL,

"PROJ" LONG NVARCHAR NULL,

PRIMARY KEY ( "ID" ASC )

) IN "system";

The table EOTABLE required a primary key for the oData service based on the ID of the image downloaded. The standard oData service in SQL Anywhere can automatically generate a the service if the table has a primary key.
To setup the SQL Anywhere oDATA producer setup I used the following, the /sql17/ url link would be important to my Node.js routing for the reverse proxy/ express setup (the code for my Node.js script is at the end of the blog, did you remember that? ;)))
CREATE ODATA PRODUCER "SCN" ROOT '/sql17/SCN';
Calling my oData service at this url https://odata.sqlany17.haw/sql17/SCN currently shows the following.

 



The eotable is now ready for data.
The Earth Observation service returns results in XML and XML is well suited to the OPENXML functionality of SQL Anywhere. There are a number of ways to import XML into the database that are documented in SQL Anywhere help but I am familiar with OPENXML and the procedure I use is as follows.

 

ALTER PROCEDURE "dba"."earthquery_p"() result ( html_string LONG nvarchar, cloud decimal(8,3),EP long nvarchar )

BEGIN

DECLARE K long VARCHAR ;

DECLARE S long VARCHAR ;

DECLARE BKS long VARCHAR ;

DECLARE h long VARCHAR;

DECLARE u long VARCHAR;

DECLARE idc long varchar;

CALL sa_set_http_option('SessionTimeout', '5');

SET  TEMPORARY OPTION remote_idle_timeout = 100;

call dbo.sa_set_http_header( 'Content-Type', 'application/json');
--EARTH URL - to get Bearer Token

set u = 'https://api.yaas.io/sap/earth-analysis/v1/wcs/describeEoCoverageSet';

--set h = string ('Authorization: Basic ', BKS );

set h = 'Authorization: Bearer {MY_AUTH_TOKEN}';

--Use function to get the Bearer Token from Twitter
--CREATE OR REPLACE VARIABLE @blink long varchar;

--select top 1 CoverageId, cloud

--&dimensionTrim=lat(35.680884805, 35.680884805 )&dimensionTrim=long(139.767172763,139.767172763)'

--this select for ONE to use with auto download -- select top 1 CoverageId into idc

MERGE INTO eotable(ID, CLOUD, PROJ)

USING (

select CoverageId, cloud, PROJ

from openxml(  earthauthtoken_f(u, h, 'eoid=DS_RGB&dimensionTrim=lat(35.679884805, 35.681884805 )&dimensionTrim=long(139.766172763,139.768172763)' ) , '/wcseo:EOCoverageSetDescription/wcs:CoverageDescriptions/wcs:CoverageDescription',1,

'<root xmlns:wcs="http://www.opengis.net/wcs/2.0"

xmlns:xlink="http://www.w3.org/1999/xlink"

xmlns:ows="http://www.opengis.net/ows/2.0"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:crs="http://www.opengis.net/wcs/crs/1.0"

xmlns:wcseo="http://www.opengis.net/wcs/wcseo/1.0"

xmlns:gml="http://www.opengis.net/gml/3.2"

xmlns:gmlcov="http://www.opengis.net/gmlcov/1.0"

xmlns:swe="http://www.opengis.net/swe/2.0"

xmlns:eop="http://www.opengis.net/eop/2.0"

xmlns:om="http://www.opengis.net/om/2.0"

numberMatched="345" numberReturned="100"

xsi:schemaLocation="http://www.opengis.net/wcs/wcseo/1.0 http://schemas.opengis.net/wcs/wcseo/1.0/wcsEOAll.xsd http://www.opengis.net/wcs/2.0 http://schemas.opengis.net/wcs/2.0/wcsAll.xsd"/>

'

)

with(

       CoverageId long nvarchar 'wcs:CoverageId' ,

--        cloud Decimal(8,3) 'gmlcov:metadata/gmlcov:Extension/wcseo:EOMetadata/eop:EarthObservation/om:result/cloudyPixelPercentage' ,

cloud Decimal(8,3) 'gmlcov:metadata/gmlcov:Extension/wcseo:EOMetadata/eop:EarthObservation/om:result/cloudPercentage' ,

       PROJ long nvarchar  'gml:domainSet/gml:RectifiedGrid/gml:origin/gml:Point/@srsName'  

) where cloud between 0 and 40 order by cloud asc

) AS sourceData(ID,CLOUD,PROJ)

ON eotable.ID = sourceData.ID

WHEN NOT MATCHED THEN INSERT

WHEN MATCHED THEN UPDATE;

--

END

Some explanation about the lines in bold above

{MY_AUTH_TOKEN}:  is my authentication token which I take from my web service.
MERGE INTO eotable(ID, CLOUD, PROJ): I use the MERGE INTO command to either update a record if an ID already exists in the table or inserts if it does not.

This was working fine for my needs until recently, when the IDs changed! Doh, back to the drawing board on the actual best way to do that. However as the YAAS service is in beta I will wait a while longer before deciding the best approach as a JSON based api is apperently on the roadmap for 2017. (That does depend if the service is still free as I won't be paying!)

with(
CoverageId…: (plus the rest in bold is where OPENXML matches the xpath for the actual value to be returned to the table. This can take some time and if you have a program that can detect xpaths (such as notepad++ with XML tools)  it makes it a little easier to setup.

I run this procedure regularly and a screenshot of the table contents below.



 

I now have data for my oData service so now onto the ui5 table to select the image based on cloud cover.

I use the exact same openUI5 base code from my Twitter/Bitly blog but with some modifications
Switch over to the ODataModel
var oModel = new sap.ui.model.odata.v2.ODataModel("/sql17/SCN",true,"DBA","",headers,false,true);

I checked the Chrome developer tools (image below) I can see that the oData version 2 is in use and an origin of njs.sqlany17.haw which indicates the reverse proxy is working.



Now previously on my twitter page I had switched off sorting as I was struggling with the omodel/odata calls to get the cell values. Now I have a working solution from Atuna’s answer to this question - https://archive.sap.com/discussions/thread/3452412

//Get the  SPath of selected row

var sPath = oEvent.getParameter("rowContext").getPath();

//Get the model attached to the table

//Ensure that variable sysTable is visible

var model = oTable.getModel();

//get the selected  data from the model

var data  = model.getProperty(sPath);

//https://archive.sap.com/discussions/thread/3452412  - Atuna's answer

console.log(data['ID'] );

console.log(data['CLOUD'] );

console.log(data['PROJ'] );

Which means I can sort by cloud coverage and select the best image and the image below shows the odata call sorting by CLOUD. And my thanks to Atuna for the ability to select the cell value.

 



 

The final piece is to actually download the image to my laptop. The Earth Observation service satellite image files are massive!! At over 300mb, in my case, so not really suited for online web dynamic map 🙂 . So I just trigger a download to my laptop for further use.

This is the procedure I use to download the image

ALTER PROCEDURE "dba"."earthgetIMG"(in idc long varchar) result ( html_string LONG BINARY )

BEGIN

DECLARE K long VARCHAR ;

DECLARE S long VARCHAR ;

DECLARE BKS long VARCHAR ;

DECLARE h long VARCHAR;

DECLARE u long VARCHAR;

--DECLARE idc long varchar;

CALL sa_set_http_option('SessionTimeout', '5');

SET  TEMPORARY OPTION remote_idle_timeout = 100;

call dbo.sa_set_http_header( 'Content-Type', 'image/tiff');

--EARTH URL - to get Bearer Token

set u = 'https://api.yaas.io/sap/earth-analysis/v1/wcs/describeEoCoverageSet';

set h = 'Authorization: Bearer {MY_AUTH_TOKEN}';
set u = 'https://api.yaas.io/sap/earth-analysis/v1/wcs/getCoverage';

--select idc from dummy;

select earthauthtoken_f(u, h, string('coverageId=', idc, '&format=image/tiff' ) ) from dummy;
END

To use this procedure in my openUI5 page I setup a web service in SQL Anywhere as follows.

CREATE SERVICE "earthGET" TYPE 'RAW' AUTHORIZATION OFF USER "dba" URL ON METHODS 'HEAD,GET' AS call "earthgetIMG"(:url);

In my UI5 page from Twitter I completely swap out the jQuery based bitly iframe for the following.

sbu="https://sqlany17.haw:8443/earthGET/" + data['ID'];

$("#bittymap").attr("src", sbu);

It basically triggers the download in my browser as a TIFF file is not setup to display in any browser (that I am aware of).
I know have my satellite image file of Japan.

 

Troubleshooting SQL Anywhere Web Service Calls

A feature I have found useful in troubleshooting web service calls in SQL Anywhere is the ability to switch on a log file that records all the web traffic. This is very useful particularly for me on the openxml calls. When the YAAS api service changed and I had to check out the xpath’s for the new results I checked the log file.

dbspawn dbsrv17 -zoc /root/log/weblog.txt -xs  "HTTP(port=8081;TO=3600)","HTTPS(
port=8443;FIPS=N;IDENTITY="/root/SQLAnyROOTCA/SQL2server.pem";IDENTITY_PASSWORD=
{PASSWORD};TO=3600)","ODATA(SecureServerPort=8444;SSLKeyStore=/root/SQLAnyROOTCA/keystore;SSLKeyStorePassword={PASSWORD};ServerPort=8082)" /home/robert/sql17db/sql17

In my startup command for SQL Anywhere the -zoc option (in bold above) can log web service calls to the stated log file. A screenshot below of my weblog.txt file as a binary TIFF file is downloading, indicating a successful call to the YAAS service. The file did get quite large due to the 300mb files I was downloading from YAAS. However when there were issues I could strip out the XML to find out why my OPENXML calls were failing for example (which were due to a change in the API returned results).




 

Setup Earth Observation image to be used in OpenLayers

In my original HCP based “It’s an SCN World” app I used a library which combined OpenLayers and Cesium call ol3-Cesium. As I transferred the entire source code from my HCP to my local SQL Anywhere instance I kept the original code. I did have to make some adjustments to get it working again e.g. XS code as covered above. Another one of the changes was to remove the base layer from a company called Mapquest who changed access to their free openstreetmap based maps. I chose to use HERE because of it’s partnership with SAP and the HANA SHINE based examples available on the HCP trial.

 

var raster = new ol.layer.Tile({

            visible: true,

            source: new ol.source.XYZ({

                url: 'https://{1-4}.base.maps.cit.api.here.com/maptile/2.1/maptile/newest/normal.day/{z}/{x}/{y}/256/png8?app_id={APPID}&app_code={APP-CODE}&lg=eng'

            })



});

GeoServer

To use the TIFF image downloaded from the YAAS Earth Observation Service I used an open source GeoSpatial program call Geoserver.

GeoServer can use the TIFF images out of the box and all I did was to load the image into GeoServer and set up a layer to use in OpenLayers. The only setting I added was a transparent colour setting shown below. This removes a black border from the TIFF image in my Ol3-Cesium based map. However there is a white border still around the image that I will come back to again at some point to see if I can remove that.

http://docs.geoserver.org/2.8.x/en/user/data/raster/geotiff.html



The ol3-cesium code to add the TIFF image as a layer using GeoWebCache is

var earthobserve = new ol.layer.Tile({

 visible: true,

 source: new ol.source.TileWMS(({

   url: '../../geoserver/gwc/service/wms',

   params: {'LAYERS': 'cite:qgisjapan', 'VERSION': '1.1', 'TRANSPARENT': 'true',

     'FORMAT': 'image/png', 'WIDTH': '256', 'HEIGHT': '256', 'SRS':'EPSG:900913' },

   }))

 });

Conclusion/wrap it all up...

What I really enjoy and amazed about with SQL Anywhere is the flexibility and potential that it offers. It also runs on my laptop and even rasberry pi’s and embedded on mobile devices. I have yet to use it on the a Pi or mobile device but certainly will in the new year. I have finally got myself a Rasberry Pi (how many years behind am I :0) and the first things I am doing on it is with SQL Anywhere.

Season Greetings and Happy New Year and thank you for reading. Appreciate it is a lot of content in one place but for me that is how best to structure and format the content. I did spend time to work through the flow of the blog, but appreciate any feedback on any part of my blog.



And I finish with that NODE.JS code as promised….

//Robert Russell 2016



//rob.roosky@yahoo.com



//SQLAnywhere



var sqla = require( '/opt/sqlanywhere17/node/sqlanywhere-xs' );



var cstr = { Server     : 'sql17',



            UserID     : 'DBA',



            Password   : '{PASSWORD}'



           };



var conn = sqla.createConnection( cstr );





var express  = require('express');



var app      = express();



var httpProxy = require('http-proxy');



const fs = require('fs');



var https = require('https');





const options = {



 key: fs.readFileSync('newkey.pem'),



 cert: fs.readFileSync('njspublic.pem'),



ca: fs.readFileSync('entpublic.pem')



};





var apiProxy = httpProxy.createProxyServer({changeOrigin: true});



var serverOne = 'https://sqlany17.haw:8443',



   ServerTwo = 'https://odata.sqlany17.haw:8444'



serverthree = 'http://192.168.56.1:8080';





//GEOSERVER



app.all("/geoserver/*", function(req, res) {



//    console.log('redirecting to Server3');



   apiProxy.web(req, res, {target: serverthree});



});





//SQL ANY WEB SERVER CALLS



app.all("/test/*", function(req, res) {



   apiProxy.web(req, res, {target: serverOne, ca: fs.readFileSync('entpublic.pem')});



});







//SQL ANY WEB SERVER CALLS



app.all("/earth*", function(req, res) {



   apiProxy.web(req, res, {target: serverOne, ca: fs.readFileSync('entpublic.pem')});



});





app.all("/ui5/*", function(req, res) {



   apiProxy.web(req, res, {target: serverOne, ca: fs.readFileSync('entpublic.pem')});



});





app.all("/hcp/*", function(req, res) {



   apiProxy.web(req, res, {target: serverOne, ca: fs.readFileSync('entpublic.pem')});



});





//ODATA CALLS



app.all("/sql17/*", function(req, res) {



   apiProxy.web(req, res, {target: ServerTwo, ca: fs.readFileSync('entpublic.pem')});



});







//////////////////Location around the world



app.get('/XSJ', function (req, res) {



conn.connect();



var list=[];



var select_all_sales_orders_query =  'select "text", "lat", "lon" from "ITSANSCNWORLD"   ' ;



stmt = conn.prepareStatement( select_all_sales_orders_query );





stmt.execute();



var result = stmt.getResultSet();



function createSDNWorldpoint(result) {



var geometry = JSON.parse(result.getString(2)); // GeoJson is Object



return {



type: "Feature",properties: {    text : result.getString(1)



                                       },



//just with co-ords



"geometry": {type: "Point", coordinates:[ result.getDecimal(3)  ,  result.getDecimal(2)  ]}







   };



}



while ( result.next() )



{



                       var shapeEntry = createSDNWorldpoint(result);



                       list.push(shapeEntry);



}



//var resp = JSON.stringify({



var recordset = JSON.stringify({



                                   "type":"FeatureCollection",



                                   "features": list



                   });



////////





res.send(recordset);



conn.disconnect();



});



//////END of locations around the world





var httpsServer = https.createServer(options, app);





httpsServer.listen(8004);

 

.