You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > GENERAL

Creating CRUD


In “Riding ExpressJs and Node.js to the Web,” I introduced the ExpressJs framework and Jade view engine as a means to convey the contents of DB2 table QCUSTCDT. Great! But displaying data is only part of the composition of a normal Web app. An obvious next step would be to allow adding, updating and deleting of data. This article will dive into how to create a CRUD (Create, Read, Update, Delete) application and further elaborate on the previous article.

First things first: clone the nodejs repository onto your IBM i, as shown:

$ cd /home/aaron/git
$ git clone git@bitbucket.org:litmis/nodejs.git
$ cd nodejs/examples/expressjs_customer
$ git checkout ff330da

The last command, git checkout, is to point you at the version of code as it was before this article. If you want to use the completed application, then don't do git checkout or view the changes on Bitbucket.

Next, run npm install to obtain the ExpressJs framework:

$ npm install

As I mentioned, we’re going to be messing with the data in DB2 table QCUSTCDT. I believe it’s best to preserve the original and create a copy in a new library. Use STRSQL to create a journaled collection (a.k.a. library or schema), as shown:

CREATE COLLECTION MYLIB

Then use CRTDUPOBJ to clone QCUSTCDT to MYLIB, as shown:

CRTDUPOBJ OBJ(QCUSTCDT) FROMLIB(QIWS) OBJTYPE(*FILE) TOLIB(MYLIB) DATA(*YES)

Now that the DB2 table is ready, I’ll cast some further vision for how this CRUD app will work. It’s worth noting my approach to CRUD is borrowed from the Ruby On Rails approach. By this I mean we will be following some Ruby On Rails conventions relating to naming and organizing the views and routes.

Organizing Views

In the previous article, we created a views directory and placed index.jade (home page), customers.jade (customer listing) and customer.jade (show a single customer) in it. To better organize for application growth, we will create directory views/customers and rename customers.jade to index.jade and customer.jade to show.jade, and put them both in this new directory.

Since we’re adding create and update capabilities to the application, we need to compose some new view files to facilitate that end, specifically new.jade and edit.jade, as shown:

---new.jade---
<h1>New Customer</h1>

include _form

a(href='/customers') Back

---edit.jade---
<h1>Edit Customer</h1>

include _form

a(href='/customers') Back

Notice the include _form portion. This is bringing in what's called a “partial” named _form.jade, which also exists in the views/customers directory, as shown:

---_form.jade---
form(method="post",action=form_action)
 p CUSNUM: 
   input(type="number", name="CUSNUM", value=result.CUSNUM)
 p LSTNAM:
   input(type="text", name="LSTNAM", value=result.LSTNAM)
 p INIT: 
   input(type="text", name="INIT", value=result.INIT)
 p STREET: 
   input(type="text", name="STREET", value=result.STREET)
 p
   button(type="submit") submit

When specifying include you don't need to specify the .jade extension. Also, view files prefixed with underscores denote they are partials. This partial will be used for both new and edit scenarios, which is why we modularized it into a partial. More on this later when we get to the controller code in app.js.

The last view change is to views/customers/index.jade, as shown:

---index.jade---
a(href='/customers/new') New Customer 

h1=title
 table
   thead
     tr
       th Last Name
       th Customer Number
       th
       th
   tbody
     - each row in results
       tr
         td=row.LSTNAM
         td: a(href='/customers/#{row.CUSNUM}')=row.CUSNUM  
         td: a(href='/customers/#{row.CUSNUM}/edit') edit
         td: a(href='/customers/#{row.CUSNUM}/delete') delete

Links to “New Customer,” “edit” and “delete” have been added. Notice how the “New Customer” link doesn't have reference to a particular customer and “edit”/”delete” does (i.e., row.CUSNUM). By specifying row.CUSNUM we turn the URL into something similar to an RPG CHAIN, allowing the app.js controller code to know which row in the database should be acted upon.

Next, we’ll look at the app.js file that contains the latest changes necessary to turn the original display-only application into a CRUD application. To gain a better perspective of the changes you can review this diff. We will now digress through various portions of this controller code to understand what’s going on and why.

The first change is a new ExpressJs middleware named body-parser, as shown:

var body_parser = require('body-parser')
. . .
app.use(body_parser.urlencoded({ extended: true }))

This module will take HTML form variables and place them in req.body so we can very easily access form variables (i.e., req.body.CUSNUM).

Use the following command to install body-parser. The --save option saves it into package.json.

$ npm install body-parser --save

I’ve also introduce the util module. The util.format() function greatly cleans up string replacement in SQL statements, as shown:

var util = require( "util" )
. . .
var sql = util.format(
 "INSERT INTO QCUSTCDT (CUSNUM,LSTNAM,INIT,STREET) VALUES ('%s', '%s', '%s', '%s')",
 req.body.CUSNUM, req.body.LSTNAM, req.body.INIT, req.body.STREET
)

In the database initialization portion, I changed it to set the schema so we didn't have to fully qualify on each SQL statement, as shown:

db.exec("SET SCHEMA MYLIB") 

Setting the schema globally works in this application because we’re accessing a single database. If multiple databases were being accessed, then qualified tables would be a better approach.

Aaron Bartell is Director of IBM i Innovation for Krengel Technology Inc. and an IBM Champion.



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.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters