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

IBM i > DEVELOPER > GENERAL

Creating CRUD


Let’s dive into how the various routes have changed. When displaying data (i.e., index.jade and show.jade) you only need one route to accomplish the task. When multiple user interactions are required (i.e., display a form and then process it), then you need multiple routes to make that flow work. For example, consider the /customers/:id/edit and /customers/:id/update routes. The /edit route is used to first load an existing DB2 entry into a form and the /update route is to process the HTML form submission. Notice how the res.render in the /edit route is setting the form_action view variable. This is how the /edit and /new routes can share the same HTML form, namely the _form.jade file.

The /update route receives in the form submission, composes and executes an SQL UPDATE statement, and then redirects the user back to the /customers route.

app.get('/customers/:id/edit', function (req, res) {
 var sql = util.format("SELECT * FROM QCUSTCDT WHERE CUSNUM=%s", req.params.id)
 db.exec(sql, function(result) {
   res.render('customers/edit', 
     { title: 'Customer', 
       result: result[0], 
       form_action: util.format('/customers/%s/update', req.params.id)
     })
 })
})

app.post('/customers/:id/update', function (req, res) {
 var sql = util.format(
   "UPDATE QCUSTCDT SET CUSNUM='%s',LSTNAM='%s',INIT='%s',STREET='%s' WHERE CUSNUM='%s'", 
   req.body.CUSNUM, req.body.LSTNAM, req.body.INIT, req.body.STREET, req.body.CUSNUM
 )
 db.exec(sql)
 res.redirect('/customers')
})

Following we have the /customers/new and /customers/create routes that are used to display an empty form and process that form’s submission, respectively. This is very similar to the /edit and /update routes already described, including the setting of the form_action view variable to alter the path that the form will be submitted to.

app.get('/customers/new', function (req, res) {
 res.render('customers/new', {result: {}, form_action: '/customers/create'})
})

app.post('/customers/create', function (req, res) {
 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
 )
 db.exec(sql)
 res.redirect('/customers')
})

And finally, see the /customers/:id/delete route for DB2 row removal. I could have been more purist and used app.delete for the route but that requires much more complexity in the view layer and an app.get with an :id accomplishes the task quite nicely:

app.get('/customers/:id/delete', function (req, res) {
 var sql = util.format("DELETE FROM QCUSTCDT WHERE CUSNUM='%s'", req.params.id)
 db.exec(sql)
 res.redirect('/customers')
})

You'll also notice that some SQL calls aren’t using a callback. The db2i module is currently synchronous in nature for all interactions. I’ve communicated to IBM about this and hope they will produce an asynchronous (non-blocking) version in the future so we don’t have to code workarounds (i.e., separate processes front-ended by Apache or nginx to allow for concurrency).

One final note, you’ll notice messages of completion after creating/editing/deleting a customer is missing. It can be easily added by introducing the connect-flash middleware. I purposely left this feature off in hopes someone from the community would add it.

Who’s up for the task?

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