As you will hopefully know, in my last few arcticles for PowerWire, I have been writing about retreiving tweets and storing them in a SQLite database on the IBM i.

I have a scheduled job on my IBM i that finds the tweets that have the hash tag #IBMChampion.

Now we have a few weeks of data stored in the SQLite database, let us analyse this information.

I intend to display this data in a neat chart on a website, all running on our IBM i.

I will be using the Node Express framework to provide functionality for this analysis.  See my previous PowerWire articles, and videos, if you need a refresh on this excellent framework.

I decided that we would produce a chart of these tweets that are grouped into a daily count.

As I’ve used SQLite to store all these tweets, the first job was to run a SQL statement over the table to summarise these tweets to give some meaningful details to report.

The information I stored when collecting the tweets was, the tweet ID, the tweet and the date/timestamp of the tweet.

As SQLite does not have a proper date/time column type, they are just stored as strings, I need to substring this column to get a summary by date.

The following sql statement provided just what I needed.

Once I had sorted the SQL statement out, I needed a way to display this information.

Searching Node Package Manager (NPM), I found that chart.js seemed to fit my requirement.

https://stackoverflow.com/questions/32092676/using-chart-js-inside-node-js

Once I had the Express framework installed, I needed to change the index.js file, in the /Routes directory.  This will be used to retreive the data from our SQLite database and display our data in a chart.

Let me show you all the steps that were necessary to achieve this.

1. Into the GET HTTP method of the index.js
2. Create SQL statement to retrieve tweets
3. A couple of hard-coded dates.  I need to get this information from the database for the user interface when my deadlines permit!
4. Run the SQL statement
5. Error handling
6. Step through the SQL resultset
7. Build the data string for the chart
8. A quick debug to the console to check I have got all the data required for the browser
9. Now render all the information back to the browser

Displaying the Data

As those following my Node Express series of articles will already know, to render any data, we must use the PUG file in the /views subdirectory.

The index.pug file will receive the 7-day variables and data string from the index.js file and plug that into the chart for chart.js to display.

Let me explain the index.pug file, as seen below.

1. Display chart title
2. Show all the data in a table above the chart
3. Use the HMTL 5 canvas element to display the chart

The JavaScript part of the pug file can be seen below.

1. Set the chart labels
2. Set the bar colours
3. Parse the data from the SQL statement into the chart
4. Piece all the configuration together
5. Create the JavaScript object using the configuration from step 3

Final Result

Lastly, use a SSH session to start up the server, which can be seen below,

1. Start the application, using debug mode
2. Application started on port number 3061
3. Into the index route
4. Show debug messages, just to illustrate the code for you

I will point my browser to the IBM i on port number 3061 and see what we get.

That is impressive, even if I say so myself 😊

Conclusion

Having all the pieces in place really made presenting this information easier, and as a bonus, hopefully it’s given me some brownie points with the IBM Champion program bosses!

Once I have completed this series on storing and displaying tweets, I will upload the source to my GitHub repository and share it with you all.

I will be giving a one-day workshop on everything Node.js and Open Source at the next i-UG user group meeting in July 2021 in the UK.  Hopefully, we can all meet up again and get back to normal, government restrictions permitting.

If you have any questions, either on this article, or anything else open source, use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience. 

IBM Champion 2021