Retrieving Tweets From the IBM i
In the first part of the series, we learned how to post Tweets from the IBM i. That was really cool, but what if we want to retrieve Tweets and store them in a table where they can be used for analytical purposes? Maybe we’d like to see what people in the IBM i community are talking about. Or, perhaps we want to know what a particular user is Tweeting about. As we saw in part 1, we can use RPG programs in conjunction with PHP to accomplish these tasks.
The RPG program to retrieve Tweets is much the same as it was for posting Tweets, so we won’t spend much time on that here. The PHP script is exactly the same, but since I didn’t show the PHP script in the first part, I’ll explain it here in full detail to give the complete picture. Please note the code for this article is written for IBM i 7.2 and above.
The Twitter API we’re using in this article is ‘statuses/user_timeline’. Documentation for this API can be found here.
Twitter requires the use of OAuth authentication when consuming their APIs. OAuth is a protocol that provides a standard format to allow secure authorization. We’ll only discuss OAuth at a high level; more detailed documentation of the OAuth standard can be found at https://oauth.net/.
OAuth credentials are calculated in the PHP script, ‘Twitter_Api.php’, and stored in SQL table ‘Twit_cred’ (Figure 1). Unfortunately, I can’t show the contents of this table because some of the values are assigned specifically to my account, but I can show the OAuth fields and how to populate them (Figure 2).
Figure 1. Portion of the Twit_cred SQL table
.jpg.aspx "A2_Figure_1-(2).jpg")
Figure 2. Populating Twit_cred
.jpg.aspx "A2_Figure2-(2).jpg")
‘Twitter_Api.php’ is called from an RPG program that we spoke about at length in part 1. Now, we’ll examine ‘Twitter_Api.php’ and see exactly what it’s doing. There’s a lot going on in the script, so we’ll break it down into logical pieces.
First, we receive parameters from the RPG program, ‘Twit_get’, and set up the library list (Figure 3). Note that when executing the script from ‘Twit_get’, the ‘search_value’ parameter will be populated and ‘tweet_text’ won’t. Similarly, when executing the script from ‘Twit_post’, ‘search_value’ will be blank and ‘tweet_text’ will be populated. By doing this, we can use the same PHP script for both RPG programs.
Figure 3. Parameters passed to Twitter_Api.php
.jpg.aspx "Figure3-(2).jpg")
Now, we connect to the IBM i and define and prepare the SQL statement to pull in the static OAuth credentials for the process we’re running. We use a parameter marker in the select statement to prevent SQL injection (Figure 4).
Figure 4. Connect to the IBM i and define and prepare the SQL statement
.jpg.aspx "Figure4-(1).jpg")
At this point, we’ve completed the preparation of the SQL statement, so now we execute it (Figure 5).
Figure 5. Execute the SQL statement to retrieve the static OAuth values
.jpg.aspx "Figure5-(1).jpg")
The static OAuth values are loaded into variables and the HTTP method is converted to uppercase. This is important because it will be used in creating a signature base string later on. The base URL must be URL encoded.
The current time needs to be in UNIX format (Figure 6). This value will also be used for creating the signature base string. It’s important to note that Twitter will reject API requests that don’t have a fairly current timestamp (roughly 24 hours).
Figure 6. Get the current time in UNIX format
.jpg.aspx "Figure6-(2).jpg")
The next step is to create key/value pairs required by the OAuth spec (Figure 7). Each of the keys and values need to be URL encoded.
Figure 7. Format the OAuth key/value pairs
.jpg.aspx "Figure7-(1).jpg")
Now that we have all the key/value pairs in the proper format, we use them to build a parameter string. The parameter string is the third and final piece needed to build a signature base string.
We concatenate all the key/value pairs into one string and URL encode it (Figure 8), creating the parameter string.
Figure 8. Build the parameter string.
.jpg.aspx "Figure8-(1).jpg")
We’re done with most of the heavy lifting, but there are still a few pieces to take care of. We need to build a signature base string and a signing key as shown in Figure 9.
Figure 9. Build the signature base string and the signing key
.jpg.aspx "Figure9-(1).jpg")
The signature base string is the concatenation of the HTTP method, URL, and the URL encoded parameter string just created. The signing key is the combination of the consumer secret and token secret from my Twitter account.
There are two steps remaining. We need to calculate a signature and a header string (Figure 10) that will be passed to the Twitter API. I’ll show both pieces followed by an explanation.
Figure 10. Calculate the signature and Header string
.jpg.aspx "Figure10-(1).jpg")
The signature is a combination of the signature base string and the signing key. These values need to be hashed using SHA1, then base64 encoded. Finally, the signature itself needs to be URL encoded.
The last piece is to update ‘Twit_cred’ with the values calculated so the RPG program can use them when consuming the API (Figure 11). A simple SQL update is executed with the three calculated values; the timestamp, the signature, and the header key.
Figure 11. Update ‘Twit_cred’ with calculated OAuth values
.jpg.aspx "Figure11-(1).jpg")
Once again, parameter markers are used to prevent SQL injection. Since some of the OAuth values are calculated, ‘Twitter_Api.php’ needs to be executed each time we consume Twitter API’s.
It’s time to run the process to retrieve Tweets from Twitter. Twitter returns the Tweets as a JSON document that is parsed in the RPG program. In case you’re curious, the JSON document sent back from Twitter looks similar to Figure 12.
Figure 12. Sample of the JSON response from Twitter
.jpg.aspx "Figure12-(1).jpg")
The program is called passing the process id and the screen name of the user from which we want to retrieve Tweets (Figure 13). In this case, we get Tweets from the Twitter account I created for use with this article (mike_IBMi).
Figure 13. Call Twit_get program
.jpg.aspx "Figure13-(1).jpg")
Before we check the SQL table to view the Tweets, I want to show my Twitter account for comparison purposes (Figure 14). We should see all these Tweets in the SQL table.
Figure 14. Twitter account showing my Tweets
.jpg.aspx "Figure14-(1).jpg")
Next, we verify that all Tweets were loaded into ‘TwitterGetResponse’. We use ACS to query the ‘TwitterGetResponse’ table (Figure 15) and view the contents (Figure 16).
Figure 15. ACS query of ‘TwitterGetResponse’ table
.jpg.aspx "Figure15-(1).jpg")
Figure 16. Results of the query of the ‘TwitterGetResponse’ table
.jpg.aspx "Figure16-(1).jpg")
Voila! I have all my Tweets in a table on the IBM i.
We covered many topics in this series. From consuming Twitter APIs, executing PHP scripts from RPG, and using the HTTPGETCLOB and HTTPPOSTCLOB functions to consume web services, to parsing JSON objects. We also covered using CLOB and UTF-8 data types in RPG and URL encoding with SQL. From a PHP standpoint, we learned how to read and update SQL tables and apply URL encoding, Base64 encoding, and hashing to variables.
I hope you enjoyed this series and I’m sure you can find ways to apply the concepts and technologies demonstrated.
Special thanks to Scott Forstie, Jesse Gorzinski and Charles Guarino for providing technical editing assistance.
--------------------------------------------------------------------------------
-- 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_get
// Author : Mike Larsen
// Date Written: 10/22/2017
// Purpose : This program will consume a Twitter web service that
// retrieves Tweets from a particular user.
//
// Example usage:
// https://api.twitter.com/1.1/statuses/user_timeline.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 screenNameParm char(13) inz('?screen_name=');
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(25);
// - - - - - - -
// 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
// retrieving 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_get ExtPgm;
processId char(20);
searchValue char(20);
end-pr;
// Main procedure interface
dcl-pi Twit_get;
processId char(20);
searchValue char(20);
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 TwitterGetResponse;
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) + ' ' +
'"' + %trim(searchValue) + '"' + ' ' +
'"' + blankVar + '"' + ''')';
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: TwitterGet
-- Date Written: 10/15/17
-- Author: Mike Larsen
-- Purpose: This table will hold information about the a persons
-- tweets from a Twitter REST web service.
--
-- RUNSQLSTM SRCFILE(mikel/qddlsrc) SRCMBR(twitterget) COMMIT(*NONE)
-- DFTRDBCOL(mikel)
--
--------------------------------------------------------------------------------
Create Table TwitterGetResponse (
-- 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_TwitterGetResponse Primary Key("ID"))
RcdFmt TwitGetR;
Label on Table TwitterGetResponse
is 'TwitterGet table';
Label on Column TwitterGetResponse
(
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 TwitterGetResponse to Public With Grant Option;
Grant Delete, Insert, Select, Update
on TwitterGetResponse to Public;
Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.
comments powered by