Posting Tweets from IBM i
More and more, social media provides an avenue to share information. It’s very easy to read your Twitter feed or post a Tweet using a smartphone, PC, etc., but what if you want to post Tweets automatically or on a schedule? Maybe you have a new product to promote or perhaps you want to advertise a holiday sale. By leveraging social media, you can reach your customer base easily and effectively. This sounds great, but how do you post Tweets from the IBM i? Fortunately, Twitter provides APIs that allow us to do so. This two-part series will show how to post a Tweet from the IBM i, as well as how to retrieve Tweets and load them into a SQL table. Please note the code for this article is written for IBM i 7.2 and above.
So, how do we get started? The first step is to create a developer account on Twitter. I’m not going to go into much detail about this as Twitter provides documentation on their website, but I will note that all I need to do is create a developer account and an application within my account. The application doesn’t need to do anything; I just need to have it, as it will provide me the credentials I need to start using the Twitter APIs. Creating this application on Twitter was free, however there is a rate limit that needs to be adhered to when posting Tweets. Twitter allows users to post 2,400 Tweets per day and limits them further to 40 Tweets per 15 minutes. If you exceed either of these limits, you’ll receive an error response from the API and risk getting ‘blacklisted’ by Twitter. If this happens, you’ll need to work with the Twitter support team to correct the issue. Note that Twitter also has ‘premium’ APIs that allow higher rate limits, more complex queries and metadata enrichments. Monthly fees for the premium APIs start at $149 per month.
Here are some links to get started:
Once I’ve created my application, I need to generate a consumer key, consumer secret, access token and an access token secret. These credentials are specific to my account, so I need to keep this information secure. Figure 1 shows my account and test application (private information is blocked out, of course).
Figure 1. Establish my account and application within Twitter.
Once my Twitter account and application are set up, I can start using Twitter’s APIs!
The Twitter APIs I’m working with require OAuth authentication. Aside from the raw information I need to provide for OAuth, I also need to transform the values being passed. That includes URL encoding, hashing, Base64 encoding, and creating a signature and signing key. In addition, I also need to get the current time in epoch (Unix) format. With all these requirements in mind, I decided to perform these steps using PHP, since it has functions to take care of each of these. Since I know I can execute a PHP script from an RPG program, this is a good plan.
With this background, here’s a brief summary of the process. I create an SQL table to hold all the OAuth values I need. This approach allows me to soft code the OAuth information in both the PHP script and the RPG program. It also allows me to use SQL functionality to secure access to this data if I choose (remember I said this information should only be known by me). SQL security is not covered in this article as I want to focus solely on using the Twitter APIs.
Next, I’ll build an RPG program, Twit_post, that executes a PHP script to retrieve the OAuth data elements from the SQL table, perform the necessary transformations and calculations, and update the SQL table with the newly calculated values. Many of the OAuth data elements are static, but there are a few that are derived, so I calculate the OAuth credentials for each API call. Figure 2 shows the OAuth data elements and their type.
Figure 2. OAuth data elements
Once I have everything in the proper format, I retrieve them in Twit_post and use them to consume the Twitter API, ‘statuses/update’. Information on this API can be found at https://developer.twitter.com/en/docs/tweets/post-and-engage/api-reference/post-statuses-update
Twit_post takes two parameters (Figure 3):
- Process id – this parameter indicates which Twitter API I want to consume. I’m only using one Twitter API in this example, but I soft coded this parameter since I’m using the process id as a key to a table that stores information about various Twitter API’s. By doing this, I won’t have to make any programming changes in case the process id changes.
- Tweet text –the Tweet I’m posting.
Figure 3. Parameters passed to Twit_post
Start by retrieving the system name so the PHP script, Twitter_api.php, knows the name of the database. Next, tell Twit_post where to find the PHP script. Note that the PHP location may be different in your environment (Figure 4).
Figure 4. Populate variables passed to Twitter_api.php
.jpg "Figure4_B-(1).jpg")
Next, execute Twitter_Api.php, using Qshell (Figure 5). Note that certain parameters are enclosed in quotes. This is necessary because there may be spaces within a value being passed. If I don’t enclose those values in quotes, the PHP script will interpret them as multiple parameters and I won’t get the results I expect.
Figure 5. Execute Twitter_Api.php
.jpg "Figure5-(1).jpg")
I want to clarify why I send a blank variable to ‘Twitter_Api.php’. Aside from using it for posting Tweets, we’ll also use it to retrieve Tweets using a different Twitter API. Therefore, it’s only being used as a placeholder. In the second part of this series, I’ll we’ll send search text in this parameter, and a blank variable in the ‘tweetText’ spot.
After the PHP script completes, the OAuth values have been calculated and stored in the SQL table ‘Twit_cred’ (The PHP script will be included in the second part of this series). Now we bring the values into the RPG program Twit_post so we can prepare to consume the Twitter API (Figure 6).
Figure 6. Retrieve OAuth credentials from SQL table into a data structure
Now that we have the required OAuth information, organize it in a format required by the OAuth standard (Figure 7). The OAuth credentials are passed to the Twitter API via the HTTP Header. In addition, the URL needs to be URL encoded and in UTF-8 format. The next section of code takes care of all these requirements.
Figure 7. Construct OAuth credentials
There are two tasks remaining: First, to consume the Twitter API and second, to parse the data returned from the API that happens to be in JSON format. Before going any further, here is a portion of what the JSON response looks like so you have an idea of what we’re dealing with. Please note this is not the complete response; it’s just a sample (Figure 8). I highlighted the Tweet I posted; note that I included a hashtag.
Figure 8. Sample of the JSON response from Twitter
I We’ll use the HTTPPOSTCLOB function to consume the Twitter API, pull back the JSON response, and load it into a variable that is defined as a CLOB data type (Figure 9).
Figure 9. Consume Twitter API using the HttpPostClob function
You may be asking why “twitterJson” was placed just before I execute the HTTPPOSTCLOB function. Here’s why—we’re using the JSON_TABLE function to parse the JSON being returned from Twitter. However, JSON_TABLE only works with JSON objects and the data being returned is a JSON array. By concatenating “twitterJson” with the JSON being sent back from Twitter, we create a JSON object that can be parsed easily by the JSON_TABLE function. Additionally, Db2 for i’s JSON support is based upon the SQL standard.
We’re only using some of the data elements from the JSON document, but it’s important to note that Twitter returns a lot of other information that can be useful. For example, it provides hashtags, user mentions, and URL’s that were included in the Tweet. It will also tell you if the Tweet was a reply to a particular user or status. Geo location information is also included. These are just some examples. More information about the JSON document can be found at https://developer.twitter.com/en/docs/tweets/post-and-engage/api-reference/post-statuses-update.
All that’s left if to parse the JSON returned from Twitter (Figure 10). We’ll break this into a few pieces and explain each part.
Figure 10. Parse JSON document into a SQL table
Start by selecting the attributes we’re interested in and use the ‘Json_Table’ function to parse them. Notice that I’m we’re using the ‘Coalesce’ function for some of the attributes. This is so some of the values can be null and we need to ensure to make sure the process can handle them properly. If we find a null, ‘Coalesce’ allows us to translate it into blanks.
Within the ‘Json_Table’ function, I we tell it which attributes I we want and their definitions. Some of the attributes are nested within the JSON object, so I we’ll need to define where to find them. For example, attributes that pertain specifically to a user are nested within an object called ‘user.’ The line of code “Nested ‘$.user[*]” directs the ‘Json_Table’ function to look inside the object ‘user’ for the attributes we want.
We’ve now parsed the JSON and we’re ready to write the attributes to a table. The last section of code does just that. Here we add the JSON data elements into a data structure (Figure 11), then into a SQL table (Figure 12).
.jpg "Figure11-(1).jpg")
Figure 11. Data structure to hold parsed JSON elements
Figure 12. Load JSON document elements into a data structure and insert into a SQL table
I loop through the JSON data, fetch a row into data structure ‘jsonData’, then insert it into SQL table ‘TwitterPst’ TwitterPostResponse.
It’s finally time to run the program and see the results (Figure 13). I call Call Twit_post the program passing the process id and the Tweet I we want to post.
Figure 13. Call Twit_post program
When the program completes, the Tweet appears in my account (Figure 14)!
Figure 14. Twitter account showing my Tweet
Since we log the posted Tweets into an SQL table, we will also want to check that to make sure the process ran successfully. We’ll use Run SQL Scripts to query the table (Figure 15) and see the Tweet along with all the selected attributes (Figure 16).
Query of ‘TwitterPostResponse’ Table
Figure 16. Results of the query of the TwitterPostResponse’ table
Working with the Twitter APIs is pretty straight forward. There are many API’s available and we’re sure you can think of ways to use others. In the next part of the series, we’ll show how to retrieve Tweets posted by a user.
--------------------------------------------------------------------------------
-- Table name: Twit_Cred
-- Date Written: 10/15/17
-- Author: Mike Larsen
-- Purpose: This table will hold credentials that will be used for
-- the Twitter REST web service. Note that some of these
-- values aren't static and can/will change.
--
-- RUNSQLSTM SRCFILE(mikel/qddlsrc) SRCMBR(twit_cred) COMMIT(*NONE)
-- DFTRDBCOL(mikel)
--
--------------------------------------------------------------------------------
Create Table Twit_cred (
-- auto generated id field
Id Numeric (5, 0) Generated always as Identity(
start with 1 increment by 1 no minvalue no maxvalue
no cycle no order cache 20) implicitly hidden,
process_id Char (20) not null default ' ',
httpMethod Char (5) not null default ' ',
twitter_url Char (150) not null default ' ',
-- Oauth fields
oauth_consumer_key Char (30) not null default ' ',
oauth_nonce Char (50) not null default ' ',
oauth_signature Char (50) not null default ' ',
oauth_signature_method Char (10) not null default ' ',
oauth_timestamp Char (10) not null default ' ',
oauth_token Char (50) not null default ' ',
oauth_version Char (5) not null default ' ',
consumer_secret Char (60) not null default ' ',
oauth_token_secret Char (60) not null default ' ',
-- Header key
header_key Char (400) not null default ' ',
-- audit fields for when record was added
AddDate Date not null default Current_Date,
AddTime Time not null default Current_Time,
AddPgm Char(10) Ccsid 37 not null default '',
AddUser Varchar(128) Allocate(18) Ccsid 37 not null default User,
-- audit fields for when record was updated
UpdateDate Date not null default Current_Date,
UpdateTime Time not null default Current_Time,
UpdatePgm Char(10) Ccsid 37 not null default '' ,
UpdateUser Varchar(128) Allocate(18) Ccsid 37 not null default User,
Constraint PK_ID_Twit_Cred Primary Key("PROCESS_ID"))
RcdFmt Twit_CredR;
Label on Table Twit_Cred
is 'Twit_Cred table';
Label on Column Twit_Cred
(
Id Text is 'id',
process_id Text is 'Process id',
httpMethod Text is 'Http method',
twitter_url Text is 'Url',
oauth_consumer_key Text is 'Oauth consumer key',
oauth_nonce Text is 'Oauth nonce',
oauth_signature Text is 'Oauth signature',
oauth_signature_method Text is 'Oauth signature_method',
oauth_timestamp Text is 'Oauth timestamp',
oauth_token Text is 'Oauth token',
oauth_version Text is 'Oauth version',
consumer_Secret Text is 'Consumr secret',
oauth_token_secret Text is 'Oauth token secret',
header_key Text is 'Header key',
AddDate Text is 'Added date',
AddTime Text is 'Added time',
AddPgm Text is 'Added by program',
AddUser Text is 'Added by user',
UpdateDate Text is 'Updated date' ,
UpdateTime Text is 'Updated time',
UpdatePgm Text is 'Updated by program',
UpdateUser Text is 'Updated by user');
Grant Alter, Delete, Index, Insert, References, Select, Update
on Twit_Cred to Public With Grant Option;
Grant Delete, Insert, Select, Update
on Twit_Cred to Public;
**FREE
ctl-opt option (*srcstmt : *nodebugio : *nounref);
ctl-opt debug (*input);
//- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Program : Twit_post
// Author : Mike Larsen
// Date Written: 10/22/2017
// Purpose : This program will consume Twitter web service that
// posts Tweets from a particular user.
//
// Example usage:
// https://api.twitter.com/1.1/statuses/update.json
//
//====================================================================*
// Date Programmer Description *
//--------------------------------------------------------------------*
// 10/22/17 M.Larsen Original code. *
// *
//- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *
// - - - -
// Program status data structure
dcl-ds pgm_stat PSDS qualified;
ProgramId char(10) pos(1);
User char(10) pos(254);
end-ds;
// - - - - - - -
// Workfields
// - - - - - - -
dcl-s SqlText char(400) ccsid(*utf8);
dcl-s quote char(6) inz('"');
dcl-s Oauth char(400) inz;
dcl-s WebServiceHeader char(500) ccsid(*utf8);
dcl-s WebServiceUrl char(200) ccsid(*utf8);
dcl-s tweetTextEncoded char(20) ccsid(*utf8);
dcl-s statusParm char(8) inz('?status=');
dcl-s twitterUrl char(200) inz ccsid(*utf8);
dcl-s twitterJsonClob sqltype(clob:16000000);
// for the Php script
dcl-s CmdStr char(1000);
dcl-s pathToCli char(100);
dcl-s phpScript char(100);
dcl-s database char(10);
dcl-s library char(30);
dcl-s quotes char(1) inz('''');
dcl-s blankVar char(20);
// - - - - - - -
// credentials fields
// - - - - - - -
dcl-ds credData qualified;
httpMethod char(5);
url char(150);
oauth_consumer_key char(30);
oauth_nonce char(50);
oauth_signature char(50);
oauth_signature_method char(10);
oauth_timestamp char(10);
oauth_token char(50);
oauth_version char(5);
header_key char(400);
end-ds;
// The 'jsonData' data structure will contain all the fields were retrieving
// from the Twitter web service. There are others, but I'm only choosing a
// few for this example.
// NOTE: data structure fields have to be in the same order as you're
// retriveing them in json_table
dcl-ds jsonData qualified;
// message details
created_at char(35);
msg_id_str char(25);
text char(140);
in_reply_to_status_id_str char(25);
in_reply_to_user_id_str char(25);
in_reply_to_screen_name char(25);
retweet_count zoned(5:0);
favorite_count zoned(5:0);
favorited char(5);
retweeted char(5);
// user details
user_id_str char(25);
name char(50);
screen_name char(50);
location char(50);
description char(100);
followers_count zoned(5:0);
friends_count zoned(5:0);
favourites_count zoned(5:0);
time_zone char(35);
statuses_count zoned(5:0);
lang char(5);
end-ds;
// - - - -
//Run CL Command
dcl-pr Run ExtPgm('QCMDEXC');
CmdStr Char(3000) Options(*VarSize);
CmdLen Packed(15:5) Const;
CmdDbcs Char(2) Const Options(*Nopass);
end-pr;
// Prototypes (entry parameters)
dcl-pr Twit_post ExtPgm;
processId char(20);
tweetText char(140);
end-pr;
// Main procedure interface
dcl-pi Twit_post;
processId char(20);
tweetText char(140);
end-pi;
//--------------------------------------------------------
Exsr clearFiles;
Exsr setUp;
Exsr runPhpScript;
Exsr getCredentials;
Exsr buildOauth;
Exsr consumeWs;
Exsr parseJson;
*Inlr = *On;
Return;
//--------------------------------------------------------
// clearFiles subroutine
//--------------------------------------------------------
Begsr clearFiles;
Exec Sql
Set Option Commit = *None;
// - - - -
// clear output table before consuming the web service
Exec sql
Delete from TwitterPostResponse;
Endsr;
//--------------------------------------------------------
// setUp subroutine
//--------------------------------------------------------
Begsr setUp;
// set Php variables. these may be soft coded in a production process.
pathToCli = '/usr/local/zendsvr6/bin/php-cli';
phpScript = '/www/zendsvr6/htdocs/Php_scripts/Twitter_Api.php';
library = 'MIKEL';
// get system name
Exec Sql
values current server
into :database;
CmdStr = 'CHGJOB CCSID(37)';
Callp Run(Cmdstr:%Size(CmdStr));
Endsr;
//--------------------------------------------------------
// runPhpScript subroutine
//--------------------------------------------------------
Begsr runPhpScript;
// the Php script will retrieve the Oauth values from the Sql table for
// the process_id sent to it and calculate the Oauth credentials. This
// program will then retrieve the credentials when it's ready to consume
// the Twitter Api.
CmdStr = 'Qsh Cmd(' + quotes +
%trim(pathToCli) + ' ' +
%Trim(phpScript) + ' ' +
%trim(database) + ' ' +
%trim(library) + ' ' +
%trim(processId) + ' ' +
'"' + blankVar + '"' + ' ' +
'"' + tweetText + '"' + ''')';
Callp Run(Cmdstr:%Size(CmdStr));
Endsr;
//--------------------------------------------------------
// getCredentials subroutine
//--------------------------------------------------------
Begsr getCredentials;
// retrieve Oauth values from the sql table now that they've been
// calculated.
Exec sql
Select httpMethod, twitter_url, oauth_consumer_key, oauth_nonce,
oauth_signature, oauth_signature_method, oauth_timestamp,
oauth_token, oauth_version, header_key
into :credData
From Twit_cred
where process_id = :processId;
Endsr;
//--------------------------------------------------------
// buildOauth subroutine
//--------------------------------------------------------
Begsr buildOauth;
// - - - -
// build the Oauth credentials from values retrieved from the Sql table.
Oauth = 'OAuth oauth_consumer_key=' + Quote +
%trim(credData.oauth_consumer_key) + Quote + ',' +
' oauth_nonce=' + Quote +
%trim(credData.oauth_nonce) + Quote + ',' +
' oauth_signature=' + Quote +
%trim(credData.oauth_signature) + Quote + ',' +
' oauth_signature_method=' + Quote +
%trim(credData.oauth_signature_method) + Quote + ',' +
' oauth_timestamp=' + Quote +
%trim(credData.oauth_timestamp) + Quote + ',' +
' oauth_token=' + Quote +
%trim(credData.oauth_token) + Quote + ',' +
' oauth_version=' + Quote +
%trim(credData.oauth_version) + Quote;
// populate the httpHeader required by the Twitter web service.
WebServiceHeader = ' ' +
'
--------------------------------------------------------------------------------
-- Table name: TwitterPst
-- Date Written: 10/22/17
-- Author: Mike Larsen
-- Purpose: This table will hold the response from a status Post
-- made through the Twitter REST web service.
--
-- RUNSQLSTM SRCFILE(mikel/qddlsrc) SRCMBR(twitterpst) COMMIT(*NONE)
-- DFTRDBCOL(mikel)
--
--------------------------------------------------------------------------------
Create Table TwitterPostResponse (
-- auto generated id field
Id Numeric (5, 0) Generated always as Identity(
start with 1 increment by 1 no minvalue no maxvalue
no cycle no order cache 20) implicitly hidden,
created_at Char (35) not null default ' ',
msg_id_str Char (25) not null default ' ',
text Char (140) not null default ' ',
in_reply_to_status_id_str Char (25) not null default ' ',
in_reply_to_user_id_str Char (25) not null default ' ',
in_reply_to_screen_name Char (25) not null default ' ',
retweet_count Numeric (5,0) not null default 0,
favorite_count Numeric (5,0) not null default 0,
favorited Char (5) not null default ' ',
retweeted Char (5) not null default ' ',
user_id_str Char (25) not null default ' ',
name Char (50) not null default ' ',
screen_name Char (50) not null default ' ',
location Char (50) not null default ' ',
description Char (100) not null default ' ',
followers_count Numeric (5,0) not null default 0,
friends_count Numeric (5,0) not null default 0,
favourites_count Numeric (5,0) not null default 0,
time_zone Char (35) not null default ' ',
statuses_count Numeric (5,0) not null default 0,
lang Char(5) not null default ' ',
-- audit fields for when record was added
AddDate Date not null default Current_Date,
AddTime Time not null default Current_Time,
AddPgm Char(10) Ccsid 37 not null default '',
AddUser Varchar(128) Allocate(18) Ccsid 37 not null default User,
-- audit fields for when record was updated
UpdateDate Date not null default Current_Date,
UpdateTime Time not null default Current_Time,
UpdatePgm Char(10) Ccsid 37 not null default '' ,
UpdateUser Varchar(128) Allocate(18) Ccsid 37 not null default User,
Constraint PK_ID_TwitterPostResponse Primary Key("ID"))
RcdFmt TwitPstR;
Label on Table TwitterPostResponse
is 'TwitterPst table';
Label on Column TwitterPostResponse
(
Id Text is 'id',
created_at Text is 'Created at',
msg_id_str Text is 'Message id string',
text Text is 'Text',
in_reply_to_status_id_str Text is 'In reply to status id string',
in_reply_to_user_id_str Text is 'In reply to user id string',
in_reply_to_screen_name Text is 'In reply to screen name',
retweet_count Text is 'Retweet count',
favorite_count Text is 'Favorite_count',
favorited Text is 'Favorited',
retweeted Text is 'Retweeted',
user_id_str Text is 'User id string',
name Text is 'Name',
screen_name Text is 'Screen name',
location Text is 'Location',
description Text is 'Description',
followers_count Text is 'Followers count',
friends_count Text is 'Friends count',
favourites_count Text is 'Favourites count',
time_zone Text is 'Time zone',
statuses_count Text is 'Statuses count',
lang Text is 'Language',
AddDate Text is 'Added date',
AddTime Text is 'Added time',
AddPgm Text is 'Added by program',
AddUser Text is 'Added by user',
UpdateDate Text is 'Updated date' ,
UpdateTime Text is 'Updated time',
UpdatePgm Text is 'Updated by program',
UpdateUser Text is 'Updated by user');
Grant Alter, Delete, Index, Insert, References, Select, Update
on TwitterPostResponse to Public With Grant Option;
Grant Delete, Insert, Select, Update
on TwitterPostResponse to Public;