Informing the IBM Community

A NodeJS Website on IBM i – Database Access

0
(0)

Continuing my Node.js website on IBM i, in this article I’ll show you how we can access a file/table and display the results on a web page all neatly formatted.
We will take our employee table, do some record selection and sort for a certain order.
Before we access our database, we need to prepare a web-page that will be used to display our results.

Firstly, we copy one of our existing routes to give us a template for a new route.
Here we can see I copied the existing index.js file in the /routes folder and called it employee.js

We need to do the same with the view file, so copy the /index.pug file, in folder /views and call it employee.pug.
So, our main processing file will be employee.js with the screen handling, similar to a display file, being the employee.pug.
I haven’t mentioned PUG files in this sequence yet. PUG files (also previously known as JADE files) are the display feature of our site. There are many PUG resources on the web if you need to know more than I will share in this series.
Before we add our database access, it is a good idea to make sure our employee web pages are working so far.
The main player in any Node Express application is the app.js file, in the application root. I’ve highlighted this file in the above figure.
We need to change this file, so it handles any requests for the employees and display the results.
The figure below shows the changes I had to make to the app.js file. Simples, only 2 lines to add!

Once we’ve made these changes, in a PASE shell, start Nodemon (don’t forget the -L parameter) and call the employee page, remembering to pass /employee as part of the URL query string.

If the browser doesn’t show the same as the figure above, the first thing I would check is for any typos to the two changes made in the app.js
Later we will change our employee.pug file to be a bit more informative than ‘Welcome to Express’.

DB2 Connector

Before we can do anything with the database, we have to install a database module that will do all the hard work for us. – This is one of the great benefits of node, no need to re-invent the wheel!
In a PASE shell, use NPM to install this module.

npm install -s idb-pconnector

Once this is installed, we have to tell our employee.js source to use it.
Looking at the employee.js file, I’ve highlighted the code to include a quick SQL to retrieve all our UK employees in surname order.

Now we have coded our database access, we need to show these results in a browser.
Now we can go back to our employee.pug file, where we make the following changes.

And below, we can see our results, not bad for a first pass!

Let us take it a stage further and neatly format our table.

Layout

One of the great advantages of using a framework like express is that fixing your application together is so simplified. This can be seen when we take a look at our web pages layout.
Provided as part of the express framework, is a PUG file called layout, and yes you guessed it, this page contains the layout for every page we render. Also, on the plus side, we can easily disable this for certain pages if we need to, for example, displaying a PDF that doesn’t need any formatting.
By added a line of code to our layout page that links to a site called Bootstrap, our formatting has been taken care of.
For those who are not aware of Bootstrap, it’s a stylesheet framework, that is great for being responsive. A responsive layout adapts to fit the screen of the user, whether it’s a mobile, or a giant TV. Very, very handy. All our development uses bootstrap, there is never an argument about a framework being included from the start of any application.
Before we had bootstrap, we would have had to format the output from our database in a HTML table, that didn’t always go as expected! Take a look at Bootstrap, it will be worth your time learning this without doubt.
The line of code I added to our layout.pug file can be seen below.

Now let’s use bootstrap to format our employee web-page.
So, back to our employee.pug file and explain what we have here.

Now running our new coding, we can see the benefits that bootstrap and express layouts give us.
Try changing your browser window size and see how bootstrap caters for the size change. All very impressive.

GitHub

As with all my previous articles on this topic, these changes have now been published to my GitHub repository, which can be found at this link. Hopefully, this will provide you with a template to experiment with Node.js on IBM i.
Take a look and have a play!

Conclusion

Hopefully in this article I’ve shown how easy it is to amend our basic website to include DB2 for i access.

We will further enhance it in my next article.

If you have any questions, either on this article, or any other 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 39 years IBM midrange experience.

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 “A NodeJS Website on IBM i – Database Access”

  1. Blooming FAB.

    Once I get the Node JS running on my new server (soon) and Node Red doing its thing, I’LL BE DEPLOYING THIS APPROCH. cheers Andy!

    Looks like a new presentation for i-UG 😉

    Cheers

    Mike R

  2. Cheers Mike – No problem – See you there 🙂

Leave a Reply

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