Informing the IBM Community

Data-Queues with ODBC

0
(0)

In my last couple of PowerWire articles, I have written about using data-queues and about ODBC connectivity.

If you need to refresh yourself of either of the articles, the data- queues article can be found here, with the ODBC article in the link here.

In this article, we are going to combine those two features and show how we can write a very simple node.js program that sits and waits for entries on a data-queue and then, when it receives an entry, it will send an email using the message from the data queue.

Before we get started on the node program, I will show the structure of the message that is in the data-queue for us to process.

It will include the email address of the supplier to send the confirmation email to, the supplier company, the supplier country code and the order number. 

All these parts will be concatinated together to form a string.  Each part will be deliminated with a hash character (#).  An example of this string would be:

andy@formaserve.co.uk#FORMASERVE#UK#19905

Now on to the node program. 

As this program will use ODBC to connect to the IBM i and receive the data queue entries, I will be developing this program locally on my PC.  One of the many benefits of using ODBC.

The NPM packages I will be using in the index.js node program will be:

  • ODBC
  • NodeMailer
  • DotEnv

This node.js script will use a .env file for all configuration.  Please note that if you use a source control package, Git for example, the .env file should be exluded. 

The layout of this environment file for this coding example, is as follows;

The DotEnv package will then let me use the process.env variable for all the configuration values.  For example, process.env.DSN will hold the value of the Data Source Name (DSN).

Now onto the node coding.  For ease, I’ll split the coding and will fully explain each section.

  1. Use the required modules for the project from Node Package Manager (NPM)
  2. Build an ODBC connection object
  3. Build a SMTP email transport object

Second Section

In this short section, I’ll build a variable holding the SQL service to receive data from a data queue.

Third and Final Part

  1. Create a node asynchronous function called getFromDQ
  2. Make the ODBC connection
  3. Try and catch for any errors
  4. A continuous loop, used to get next data queue message
  5. Execute the data queue SQL statement prepared earlier
  6. Check if we have a data queue entry
  7. A quick debug message for article explanation
  8. Check if the data queue message is *END, which is used to gracefully stop the processing
  9. If data queue entry empty loop to next one
  10. Split up the message we now have, so that it is ready for processing
  11. Create the email transporter object with details from the data queue
  12. A couple more debug messages
  13. Error handling
  14. Execute the getFromDQ function

System Console

The figure below details the console output for running the node script.

  1. Start the Node script with debugging on
  2. Make connection to SMTP server
  3. First two attempts at receiving dataqueue, show no entries, the third attempt receives an entry
  4. Shows the SMTP logs as debugging is switch on
  5. Found no more entries on data-queue
  6. An *END processing message found so leave the node script

Resulting Email

Here we can see the email produced by the NodeMailer package.

Conclusion

Once again, open source proves how easy it is to integrate our applications and take full advantage of the benefits they bring. 

I could have used other methods to achieve this such as using the XML toolkit, but I hope you would agree, the ODBC method makes our code very clean and easy to read.

I would like to thank Jesse Gorskinki, from IBM, for providing an example of using data-queues using node.js.  His enthusiasm for open source on the IBM i holds no bounds! Thanks Jesse.

All the examples I have written for this article can be found on my open-source repository on GitHub, which can be found at https://github.com/AndyYouens/f_Learning

If you have any questions, either on this article, or anything else on the IBM i, 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

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.


Comments

2 responses to “Data-Queues with ODBC”

  1. I loved the article inspiring! i’m always learning more i liked.

    Congratulations on the site, also know mine:
    https://b9g.net Coolest Streaming!
    .

Leave a Reply

Your email address will not be published. Required fields are marked *