Overview of technology used are listed below and I will be creating videos to cover some of the these topics.
If you are interested in any details from my video or information below then let me know via a comment on this blog.

Saturday, March 4, 2017

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.

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.

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

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

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.

How I Integrated my Twitter4J based code with SQL Anywhere

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.

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.

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 :).

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.

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).

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,

Setup Table to collect SAP HCP status page information

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

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

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)

--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.

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.