IBM i > TRENDS > WHAT'S NEW

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

Figure 2. Populating Twit_cred

‘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

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

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

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

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

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.

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

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

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

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

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

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

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

Figure 16. Results of the query of the ‘TwitterGetResponse’ table

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;
 

Tweet

---

---

---

comments powered by Disqus

---