 |
| Robot with a personality? |
created a Twitter service. It automatically can respond to tweets and can also store the incoming messages in a DB2 table. In my example it is just a, more or less, silly service right now, but it could be expanded to react to and log Twitter-based customer feedback, provide some basic interim reaction or acknowledgement. Let me show you how easy it is to get started...
To build the Twitter service you need the "Node-RED Starter" boilerplate from the Bluemix catalog as well as the "SQL Database" service. Create the Node-RED boilerplate first, then add the database service and directly bind it to the new application. Once everything has been provisioned, it is time for wiring up the Twitter service in the flow editor. The following picture gives an overview of the two flows when completed. I made the flows available in the Node-RED library.
 |
| Flow in Node-RED for Twitter robot and reporting |
The upper flow is used for Web-based reporting on stored tweets. An incoming http request is routed to a SQLDB query node. There the tweet data is fetched from DB2, handed over to a template to produce an HTML page with the result table which finally is returned as http response.
Without discussing all the details, I wanted to provide some information on how to work with DB2 from within Node-RED. There are input and output nodes for the SQLDB and the dashDB services. To store a single record, a "payload" with the column name/value pairs needs to be created. For the example I used a simple table "twitarchive" to hold the archived tweets. Here is the DDL:
create table twitarchive(
id int generated always as identity,
tstamp timestamp,
tweet varchar(200),
username varchar(100)
);Because the value for the ID column is generated, I only need to pass on the values for the remaining columns. This is done in the "Process Twitter Input" node by using the structure "twitarchive":
var twitarchive = { payload : {
TSTAMP: 'TIMESTAMP',
USERNAME: msg.user,
TWEET: msg.tweet }
};
It is important to use the same column names as in DB2, including upper casing. Else there won't be a match. In the input and output nodes for sqldb/dashDB access the same rule applies to specifying table names (remember: if no quotes are used for table names, names are converted to upper case).
I used a template node to format query results into a HTML table. The template and the resulting output are shown below.
This concludes the overview. Let me know if you are interested in details. And if you want to test the service, tweet to "@db2bm". The hashtag "#archive" in the tweet (when found :) causes the message to be store, "#autoanswer" produces a more elaborate random reply.
Tweet to @db2bm
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
}
th {
text-align: left;
}
td {
padding: 15px;
}
th {
background-color: green;
color: white;
}
</style>
<title>Archived Tweets</title>
</head>
<body>
<h1>Archived Tweets</h1>
The following tweets have been archived in DB2 so far:
<table>
<tr><th>User</th><th>Tweet</th><th>Time</th></tr>
{{#payload}}<tr>
<td><a href="http://twitter.com/{{USERNAME}}">{{USERNAME}}</a></td><td>{{TWEET}}</td><td>{{TSTAMP}}</td>
</tr>{{/payload}}
</table>
</body>
</html>  |
| DB2-based result table with some archived tweets |