SQL Anywhere: Setup a Twitterbot for the SAP Cloud Platform's status.io feed

Background

I have been busy with a personal project to recreate some of my SQL Anywhere setup that was originally running in the cloud. (I had an Linux based SQL Anywhere server on Amazon Web Services (AWS) which was terminated my AWS.)  With my original version I had used SQL Anywhere to read the, now out of service, SAP twitter account @SCNblogs. I had used SQL Anywhere’s ability to calculate hash based message authentication codes to read the @SCNblogs twitter timeline. The end result was my data geek entry blog linked below.

https://blogs.sap.com/2013/08/14/data-geek-challenge-analysing-the-scnblogs-twitter-timeline/

With SQL Anywhere I was able to generate OAuth authentication but not in a format that twitter accepted and I had to use openssl to complete the process at the last step. In my opinion this was due to unclear documentation in the Twitter API help pages. Although I was able to work around it with SQL Anywhere calling openssl commands via xp_cmdshell system procedure at the last step for the binary format required. In my original blog I thanked Eric Farrar for his blog on SQL Anywhere’s hash capabilities and his blog was at this link but is now a 404 page cannot be found. Makes me wonder how many of my old blogs now contain dead links.

http://scn.sap.com/community/sybase-sql-anywhere/blog/2009/12/10/calculating-hash-based-message-authentication-codes-with-sql-anywhere

It’s new home

https://blogs.sap.com/2009/12/10/calculating-hash-based-message-authentication-codes-with-sql-anywhere/

On a side note, I have previously used the  the “Way Back Machine” to find missing web pages. I visited https://archive.org/web/ entered the original URL and and the original URL for Eric’s blog was saved back in 2013 at this link

https://web.archive.org/web/20130925161421/http://scn.sap.com/community/sybase-sql-anywhere/blog/2009/12/10/calculating-hash-based-message-authentication-codes-with-sql-anywhere

Back to my blogs main content now and I will break it into the following sections

1. How I setup my Twitterbot with Twitter4J (an unofficial Java based library for the twitter API). As I intended to setup my SQL Anywhere server to tweet and not read tweets this time.
2. How I Integrated my Twitter4J based code with SQL Anywhere
3. How I setup my Twitterbot to tweet about the status.io feed of the newly named SAP Cloud Platform. The status.io page shows any current issues with the SAP Cloud Plaltform. It also offers an RSS feed with latest incidents.
4. Receate @SCNblogs account using my twitterbot and the bitly url shortener service.

My Twitterbot Code Setup

I didn’t want my main twitter account to be used to send out automated tweets - as in a worst case it may get out of control! I wanted to use a new dedicated account and set it up to tweet at my main account. I created an account call https://twitter.com/sqlany_rjruss and soon realised twitter had made some restrictions on API usage.

The API Setup required a phone number and I only have one number and this is locked against my main Twitter account. So I had to search for a workaround/solution. In the end I followed the information in this post to register my bot account sqlany_rjruss to link back to my main twitter account.

http://dghubble.com/blog/posts/twitter-app-write-access-and-bots/

Once I had my 4 tokens listed in a file called .twurlrc, as per Dalton’s blog link, I had a write enabled app. This meant I could proceed to setup my Java code to send out tweets.

Using Java project Twitter4J

http://twitter4j.org/en/

I only used the core Jar file from the project

The following is the example code I used as I found the main examples on the twitter4J site seemed to follow a method to retrieve the access tokens each time. I chose to hard code the tokens in my code :).

Example Code

import twitter4j.Status;

import twitter4j.Twitter;

import twitter4j.TwitterException;

import twitter4j.TwitterFactory;

import twitter4j.auth.AccessToken;

import twitter4j.auth.RequestToken;

import twitter4j.conf.ConfigurationBuilder;

import java.io.BufferedReader;

import java.io.IOException;

import java.io.InputStreamReader;

public final class tweet {

public static void main(String[] args) {

if (args.length < 1) {

           System.out.println("Usage: java -cp \".;./twitter4j-core-4.0.4.jar\" tweet [text]");

//MAC usage java -cp "tweet:.:twitter4j-core-4.0.4.jar" tweet "macbook pro"

           System.exit(-1);

}

String consumerKey = "{CONSUMER_KEY}";

      String consumerSecret = "{CONSUMER_SECRET}";

      String accessToken = "{ACCESS_TOKEN}";

      String accessSecret = "{ACCESS_SECRET}";

      ConfigurationBuilder cb = new ConfigurationBuilder();

      cb.setDebugEnabled(true)

          .setOAuthConsumerKey(consumerKey)

          .setOAuthConsumerSecret(consumerSecret)

          .setOAuthAccessToken(accessToken)

          .setOAuthAccessTokenSecret(accessSecret);

   try {

Twitter twitter = new TwitterFactory(cb.build()).getInstance();   

twitter.updateStatus(args[0]);

   } catch (TwitterException e) {

       System.err.println("Error occurred while updating the status!");

System.err.println(e);

   }

   }

}

I have tested Java 7 and 8 to compile the code with javac as part of the JDK I was using.

I tested the code on Windows 10 and Mac Yosemite and Sierra operating systems.

For Windows 10 the follow command successfully posted a tweet

Usage: java -cp ".;./twitter4j-core-4.0.4.jar" tweet “windows 10"

For my Mac’s I found I had to alter the path for the class path to get the tweets posted.

Usage: java -cp "tweet:.:twitter4j-core-4.0.4.jar" tweet "macbook pro"

SQL Anywhere’s JAVA external environment

SQL Anywhere can use Java as an external environment and a link to the help for SQL Anywhere 17 is here. One of my findings at the end of my setup was the difference between the Java environment returning a resultset or not. My example code does not return anything and only writes out.

http://dcx.sap.com/index.html#sqla170/en/html/8160578b6ce2101480bff9bdb43fee68.html

To capture errors while troubleshooting I found that starting SQL Anywhere and capturing log files was useful. E.g.

dbsrv17 -zoc "C:\sql17logs\log" -o "C:\sql17logs\dblog"

One example error below was triggered when I was testing with exactly the same tweet. To get around this I added a timestamp to all my tweets to make them unique!!

I. 02/22 21:04:59. Error occurred while updating the status!

. 403:The request is understood, but it has been refused. An accompanying error message will explain why. This code is used when requests are being denied due to update limits (https://support.twitter.com/articles/15364-about-twitter-limits-update-api-dm-and-following).

message - Status is a duplicate.

code - 187

Relevant discussions can be found on the Internet at:

http://www.google.co.jp/search?q=2fc5b7cb or

http://www.google.co.jp/search?q=0ea287d2

TwitterException{exceptionCode=[2fc5b7cb-0ea287d2], statusCode=403, message=Status is a duplicate., code=187, retryAfter=-1, rateLimitStatus=null, version=4.0.4}

I found that the SQL Anywhere had a standard JAVAVM as part of the install on Windows 10.

SELECT db_property('JAVAVM');

There is an Option to change the location of Java

ALTER EXTERNAL ENVIRONMENT JAVA

 LOCATION 'c:\\jdk1.8.0\\jre\\bin\\java.exe';

I copied the code for Twitter4J into a dedicated directory.

In SQL Central entered the Java external environment to import my class and the Twitter4J jar file.

I followed the wizard to add my tweet.class

Followed the wizard to import the core Twitter4J jar file

I then set up a procedure to call my class which would not return a result (option V)

CREATE PROCEDURE "dba"."sqlanytweet"( in "arg1" long nvarchar )

external name 'tweet.main([Ljava/lang/String;)V' language "JAVA"

I then tested calling the procedure from interactive SQL on my windows instance.

As mentioned earlier to prove to myself that this would work on other systems I setup my Mac version of SQL Anywhere to tweet using the same Java code, as shown below.

Now, what to tweet about?

Reading the Status.IO RSS feed for the SAP Cloud Platform

On the SAPCP’s page http://sapcp.statuspage.io/ there is a subscribe function which I used to get details about the RSS feed. I have no direct knowledge of how the feed is configured or updates triggered. So the following is all in theory for a demo as I have assumed how it will work and the following is based upon that guess work. I will split it into the following sections,

1. Setup Table to collect SAP HCP status page information
2. Setup Function and Procedure to read the RSS feed and populate my control table
3. Setup Function and Procedure to tweet any updates

Setup Table to collect SAP HCP status page information

First I setup a table matching the RSS feed and adding one column to use to control how I tweet about any updates.

All columns matched an “item” in the RSS feed for the HCP status.io feed. Apart from the highlighted “tweeted” column which I will use to tweet any updates. I chose to use statusscp for the table name as the HANA Cloud Platform is now known as the SAP Cloud Platform (scp). I failed to keep this naming convention everywhere and still refer to HCP (some screen shots will have HCP but the real thing is now SCP) but then again it should be NEO.

Setup Function and Procedure to read the RSS feed and populate my control table

As the status RSS feed is XML based and I am familiar with OpenXML features of SQL Anywhere then it is an ideal way for me to read the HCP status.io activity feed (and covered an OpenXML approach here as well)

First I downloaded the certificate from the RSS status page

The above screenshot is now out of date as the certificate (and link) has changed.

Updated version below and any new certificates would have to be downloaded for any subsequent changes to the certificate. Maybe I should have one big certificate file containing all known root certificates.

I saved the file into the twitter directory I created earlier as I will need to reference this in my openxml function,

Next I setup the function and procedure in SQL Anywhere that I will use to read the RSS feed (the function has the certificate reference).

ALTER FUNCTION "dba"."statusscp_f"( in "u" long varchar )

returns long nvarchar

url '!u'

certificate 'file=c:\\twitter\\hcpstatus.cer'

type 'HTTP:GET'

ALTER PROCEDURE "dba"."statusscp_p"() result ( title LONG nvarchar, "description" LONG nvarchar, pubDate LONG nvarchar, link LONG nvarchar, guid 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;

set u = 'https://saphcp.statuspage.io/history.rss';

MERGE INTO statusscp(title, "description", pubDate, link, guid )

 USING (

select title, "description", pubDate, link, guid

from openxml(  statusscp_f(u) ,  '/rss/channel/item' , 1 , '<rss version="2.0" xmlns:dc="http://purl.org/dc/elements/1.1/"/>' )

with(

      title long nvarchar 'title' ,

  "description" long nvarchar 'description' ,

  pubDate long nvarchar 'pubDate' ,

  link long nvarchar 'link' ,

guid long nvarchar 'guid'     

)

) AS sourceData( title, "description", pubDate, link, guid)

 ON statusscp.pubDate = sourceData.pubDate

 WHEN NOT MATCHED THEN INSERT

 WHEN MATCHED THEN UPDATE;

END

It is unclear to me how many incidents are kept on the SAPCP status.io RSS feed however my approach was to setup a job to keep a table updated based on the published data pubDate field.

Example of running the procedure followed by a select on the statusscp table

Setup Function and Procedure to tweet any updates

I setup the statusscp table in SQL Anywhere with a column “tweeted” which I would use to trigger and record actual tweets via my twitterbot.

Example  procedure of how I do this is below.

ALTER PROCEDURE "dba"."status_tweet_scp_p"() result ( outp LONG nvarchar)

BEGIN

DECLARE TW long VARCHAR ;

DECLARE DCR long VARCHAR ;

DECLARE MSG long VARCHAR;

DECLARE TWHO long varchar;

DECLARE DT long varchar;

DECLARE WORDSEARCH long nvarchar;

set TWHO='@rjruss';

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

SET  TEMPORARY OPTION remote_idle_timeout = 100;

-- where title like '%Trial%' or title like '%Europe%' or title like '%All%';

--SELECT title, "description" as de, pubDate, link FROM "dba"."statusscp" where tweeted is  null

FOR names AS curs INSENSITIVE CURSOR FOR

SELECT title, "description" as de, pubDate, link FROM "dba"."statusscp" where tweeted is  null and ( title like '%Trial%' or title like '%Europe%' or title like '%All%')

DO

set DT = pubDate;

set WORDSEARCH = REGEXP_SUBSTR( de, '>Resolved<|>Identified<|>Investigating<' );

set MSG = string( TWHO, ' ', title, ' ', link, ' STATUS:', WORDSEARCH , ' :',  DATEFORMAT(GETDATE(), 'RRHHNNSS') );

--select MSG  from dummy;

select "dba"."sqlanytweet"( MSG );

UPDATE "dba"."statusscp"

SET tweeted='sent'

WHERE pubDate=DT;

END FOR;

select 'end' from dummy;

--testing create only one table entry and set to null before calling this procedure

--update  "dba"."statusscp" set tweeted = NULL;

--call status_tweet_scp_p()

END

This line in bold is used to only tweet about Trial and Europe and All incidents. This way it will not tweet about US or other data centers which I have no interest ;).

SELECT title, "description" as de, pubDate, link FROM "dba"."statusscp" where tweeted is  null and ( title like '%Trial%' or title like '%Europe%' or title like '%All%')

This line in bold uses SQL Anywhere regular expression to search the RSS feed for a status text. It works on the understanding that SAP provide updates with Resolved, Identified and Investigating in this order. As I take advantage of the default behavious of REGEXP_SUBSTR to stop at the first match. If SAP or the status.io functionality make changes to these status messages then the code will no longer work as intended.

set WORDSEARCH = REGEXP_SUBSTR( de, '>Resolved<|>Identified<|>Investigating<' );

The final line in bold sends a formatted tweet at my main account via the sqlany_rjruss twitterbot. This uses the procedure I covered in my Twitter4J setup earlier.

select "dba"."sqlanytweet"( MSG );

I chose to control the process via a batch job on my windows 10 computer which would call an sql script. The sql would trigger the read of the RSS feed and send out any tweets as necessary, as per the following.

statusHCP.bat file

cd c:\twitter

dbisql -c  "DSN=STATUSHCP;UID=dba;PWD={PASSWORD};IDLE=4" -nogui  statusHCPtweet.sql

statusHCPtweet.sql file

call statusscp_p();

call status_tweet_scp_p();

exit;

An example tweet as follows