Forums >> Programming >> Open Source

Jump to:




bvstone

A Sample File Update node.js Application for the IBM i

Posted:

A Sample File Update node.js Application for the IBM i

For the past few weeks I've been putting together a real world application with node.js and the Express framework (using Jade, which is now known as Pug, I guess).

I found in the creation of this application that a lot of the functions I was doing could be customized to reduce the amount of code used.  Similar to making ILE subprocedures with RPG, your programming style should be similar no matter the language or syntax.  Reduce and Reuse as they say in the Green world.

I decided to start a new thread dedicated to the functions that I have created.  These functions are pretty IBM i specific, but I am pretty sure they could be modified to be used on other platforms if needed.

The functions I created mainly deal with returning result sets, column names and column types.  These can be passed into our template to customize the layout.

The actual working demo applications can be seen here:

http://bvsdemo.bvstools.com:5250/

This article will deal with the Item Update Demo.

If for some reason it's not working, just shoot me an email at bvstone@bvstools.com and I'll make sure it's up an running.  I haven't yet added any functionality to keep the node.js web server running should it crash.  But, hopefully I plan to in the future.

The following articles will detail the application and how it works.




Latest Posts:

Update for Google WorkSpace Accounts (2024): Google Dropping Support for Update for Google WorkSpace Accounts (2024): Google Dropping Support for "Less Secure Apps" May 30th, 2022. What Does This Mean for Your IBM i Email?
Posted by January 19, 2024
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sales By State Report in QuickBooks Online Sales By State Report in QuickBooks Online
Posted by January 13, 2024
QuickBooks >> QuickBooks Online
How to Whitelist GreenTools for G Suite (G4G) For Your Organization How to Whitelist GreenTools for G Suite (G4G) For Your Organization
Posted by November 5, 2023
BVSTools >> BVSTools Software Discussion >> GreenTools for G Suite (Google Apps) (G4G) Specific Discussion
QuickBooks Online Releases QuickBooks Online Releases "New Invoices!"... and It's Terrible!
Posted by October 8, 2023
QuickBooks >> QuickBooks Online
Admin/4i - What is it? Admin/4i - What is it?
Posted by September 30, 2023
Vendor Corner >> MSD Information Technology
BVSTools Releases Send Job Log to BVSTools (SNDLOG2BVS) Command BVSTools Releases Send Job Log to BVSTools (SNDLOG2BVS) Command
Posted by August 28, 2023
BVSTools >> BVSTools Announcements
MAILTOOL Now Allows Email Redirection for Development and Testing MAILTOOL Now Allows Email Redirection for Development and Testing
Posted by May 27, 2023
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
GreenTools for Microsoft Apps (G4MS) Now Supports Footers When Sending Email GreenTools for Microsoft Apps (G4MS) Now Supports Footers When Sending Email
Posted by March 29, 2023
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
QuickBooks Online - Subtotals and Discounts Frustration QuickBooks Online - Subtotals and Discounts Frustration
Posted by March 16, 2023
QuickBooks >> QuickBooks Online
Making the Switch From QuickBooks Desktop to QuickBooks Online - No Picnic Making the Switch From QuickBooks Desktop to QuickBooks Online - No Picnic
Posted by March 16, 2023
QuickBooks >> QuickBooks Online
BVSTools Software Verified on V7R5 and Power10 BVSTools Software Verified on V7R5 and Power10
Posted by December 9, 2022
BVSTools >> BVSTools Announcements
Microsoft Office 365 Servers and Random Errors Issue Microsoft Office 365 Servers and Random Errors Issue
Posted by November 14, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sending/Resending Emails Using a MIME File with MAILTOOL Sending/Resending Emails Using a MIME File with MAILTOOL
Posted by November 8, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sending an HTML Email on Your IBM i Using MAILTOOL Sending an HTML Email on Your IBM i Using MAILTOOL
Posted by November 1, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Transferring License Keys from One System to Another Transferring License Keys from One System to Another
Posted by October 31, 2022
BVSTools >> BVSTools Software Discussion
bvstone

The viewItems Application

Posted:

The viewItems Application

The Node.js application

Following is the viewItems.js application:

var express = require('express');
var util = require('util');
var myStuff = require('mymodules');
var router = express.Router();

var DBname = "*LOCAL";
var Library = 'BVSTONES';
var Table = 'ITEMPF';

router.all('/', function(req, res, next) {
  myStuff.setDBname(DBname);
  myStuff.setDBschema(Library);
  myStuff.setDBtable(Table);

	var sql = "select rrn(a) AS RRN, a.* from %s.%s a";
  sql = util.format(sql, Library, Table);
  
  var resultSet = myStuff.getResultSet(sql);
  var columnList = myStuff.getFieldDescriptions();
  var fieldTypes = myStuff.getFieldTypes();
  
  res.render('viewItems', {resultSet: resultSet, columnList: columnList, fieldTypes: fieldTypes});
});

module.exports = router;

The viewItems.js node.js program is used to present a list of items to the user.  As with any node.js application, we start out with a few "require" statements to allow the use of functionality included in the available modules.  

  • express - Required to use the Express framework
  • util - A nice string replacement module
  • mymodules - This will be explained in detail later, but this includes a set of functions I created specifically to deal with IBM i data
  • router - Again, required for the Express framework

You may be asking where is the /QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2 require statement?  Well, because we encapsulated the database access, update and insert functionality into our own module (mymodules) we don't need to require it here.

Examining the rest of the application we see it's actually quite simple.  We first define the table, library and database name we want to use.  We use a couple of "setters" to set this information inside of the myStuff (mymodules) module.  We then build a normal SQL statement and call three of our home-grown functions:

  • getResultSet() - Passing the SQL statement into this function will return the result set.  An example of the result set is as follows:
     
    [ { RRN: '1',
        ITITEM: 'BLUECAR',
        ITIDESC: 'Light Blue Car',
        ITPRICE: '1000.00',
        ITQTY: '1' },
      { RRN: '2',
        ITITEM: 'HAMSAND',
        ITIDESC: 'Ham Sandwich',
        ITPRICE: '1.29',
        ITQTY: '1' }, ...
    ]

     
  • getFieldDescriptions() - This function returns an object containing the descriptions for each column/field in the file we are querying.  The object is returned in the following format:
    {column_name:column_text, column_name:column_text...}

    An example of the field descriptions object is as follows:
     
    { ITITEM: 'Item Number', 
      ITIDESC: 'Desc', 
      ITPRICE: 'Price', 
      ITQTY: 'Qty' }

    This means that using the column name we can get the column description using the format column_text = object[column_name].  We will see this in use later in the application.
     
  •  getFieldTypes() - This function returns an object containing the field types for each column/field in the file we are querying.  The object returned is similar to the object returned by the getField Descriptions() function:
    {column_name:field_type, column_name:field_type...}

    Knowing the field type (ie, character, date, numeric, etc) will be helpful when we are building the HTML form.  An example of the field type object is as follows:
     
    { ITITEM: 'text',
      ITIDESC: 'text',
      ITPRICE: 'numeric',
      ITQTY: 'numeric' }

     

Once we have everything we need, we pass the result set, column list and list of field types to our template. 

The Jade/Pug Template

Following is the viewItems.jade template:

extends layout

block content
  - var dontShow = ['RRN']
  - var replaceHeadings = {ITITEM: 'Item Number', ITIDESC: 'Description', ITPRICE: 'Price', ITQTY: 'Qty'} 

  .updatableDiv
    | To add a new item, fill in the top row and click the Update button.
    include ./mixins/dataList.jade
    +dataList('detail', true)

Yes, it is this small.  Why?  Just as with eRPG applications I've been finding little hidden treasures that allow me to break up the pages into smaller reusable parts (ie, Server Side Includes).  But, in the case of the Jade template engine they are called Includes and Mixins.

Includes allow you to include pre-created code into your template.  Mixins take that one step further and work sort of like functions in that you can pass in variables to the Mixin in order to change the result of the data that is shown.

Two other important things to mention are the dontShow array and the replaceHeadings object.  Any column name we add to the dontShow array will be omitted from our display.  We can also override the column names from the system using the replaceHeadings object, as we have in this case.  Both of these allow us to keep some of the display functionality in the template vs the node.js application.  This of course means that if we want to modify the columns shown or the column headings we can do so without needed to restart our application.  

So, let's take a look at the dataList.jade Include file which includes the dataList Mixin:

mixin dataList(listID, showNewRecord)
  .dataList
    p= resultSet.length + " rows returned."
    table
      tr    
        each fieldValue, fieldKey in columnList
          if (dontShow.indexOf(fieldKey) < 0)
            th.small1(align="left") 
              if (!replaceHeadings[fieldKey])
                =columnList[fieldKey]
              else
                =replaceHeadings[fieldKey]
      if (showNewRecord)
        tr(data-rrn=0, data-ID=listID)
          each fieldValue, fieldKey in columnList
            if (dontShow.indexOf(fieldKey) < 0)
              td.small1 
                include showFields.jade
                +showFields(fieldTypes[fieldKey], fieldKey, "")
      if (resultSet.length)  
          each result in resultSet
            tr(data-rrn=result.RRN, data-ID=listID)
              each fieldValue, fieldKey in result
                if (dontShow.indexOf(fieldKey) < 0)
                  td.small1 
                    include showFields.jade
                    +showFields(fieldTypes[fieldKey], fieldKey, fieldValue)
 
    br
    button.updateButton Update
    
    script.
      adjustFieldSizes(10);

Now, if you're not familiar with the Jade template engine that would be a good place to start learning.  It's a method that makes creating HTML on the fly "easier".  I only put quotes around "easier" because in order to know how to use the template engine there is a prerequisite of knowing HTML.  So you're still not getting away from that (as well as JavaScript, which obviously needs to be understood for node.js programming).  Once you know the HTML then you need to learn the syntax and functionality of the template engine you're using.  

For this Mixin we pass in 2 parameters:

  • listID - This is simply a unique ID that we will use to assign an ID to each row of data.  This is important for when we want to update records.
  • showNewRecord - This is a boolean parameter that allows us to show a blank record or not.  This is useful in situations where not only we allow updating of existing records, but where we want to allow the user to enter new records.

We are also using the 3 objects, resultSet, columnList and fieldTypes from our node.js application.  Now, these "could" be passed in as parameter but as long as we call them the same names in our application they don't need to be.

First we use the resultSet parameter and the length property to display how many rows of data there are.

    p= resultSet.length + " rows returned."

We then start our table.  Normally we want to include headings for each column and this case is no different.  This is where the columnList object comes into play.  We loop through each of the headings in the columnList object and check to see if the column name is in the dontShow array.  If not, we display the column heading.  Of course, we also check the replaceHeadings object for any override of the heading.

    table
      tr    
        each fieldValue, fieldKey in columnList
          if (dontShow.indexOf(fieldKey) < 0)
            th.small1(align="left") 
              if (!replaceHeadings[fieldKey])
                =columnList[fieldKey]
              else
                =replaceHeadings[fieldKey]

Next, we check the boolean value of the showNewRecord parameter to see if we need to display a blank record used for adding new records to the file.

      if (showNewRecord)
        tr(data-rrn=0, data-ID=listID)
          each fieldValue, fieldKey in columnList
            if (dontShow.indexOf(fieldKey) < 0)
              td.small1 
                include showFields.jade
                +showFields(fieldTypes[fieldKey], fieldKey, "")

Finally we get to the nitty gritty.  This next section is where we will loop through the result set and display a row for each of the records in the result set.  We first check to make sure there are entries in the result set using the length property of resultSet.  Again, before including each column we check the dontShow array to make sure we want to show this particular column.  

      if (resultSet.length)  
          each result in resultSet
            tr(data-rrn=result.RRN, data-ID=listID)
              each fieldValue, fieldKey in result
                if (dontShow.indexOf(fieldKey) < 0)
                  td.small1 
                    include showFields.jade
                    +showFields(fieldTypes[fieldKey], fieldKey, fieldValue)

Wait... what's this?  Another Mixin?

Yes, we're using another Mixin (showFields) to display the actual contents of the table cell.  So, lets take a look at the showFields.jade Mixin template:

mixin showFields(inFieldType, inKey, inValue)
  .showFields
    if (inFieldType == 'numeric')
      input.inKey.adjustable(name=fieldKey, value=inValue, type="number")
    else if ((inFieldType == 'date') || (inFieldType == 'timestamp'))
      if (inValue == '')
        - var newValue = '0001-01-01'
      else
        - var newValue = inValue
      input.inKey.datepicker.adjustable(name=fieldKey, value=newValue.substr(0,10), type="text")
    else
      textarea.inKey.adjustable(name= fieldKey, cols=10, rows=1)
        | #{inValue}

The showFields Mixin accepts 3 parameters:

  • The field type (inFieldType)
  • The field name (inKey)
  • The field value (inValue)

Now, depending on the type of field we create an input field of the appropriate type (ie, number, textbox, or text for a date).  If a field is a date we use a jQuery UI addon to make selecting dates easier called datepicker.  This particular example doesn't have any date data types, so for now we'll not worry about that.

Returning back to the dataList.jade template we see that after the table is complete we display a simple update button and then call a JavaScript function named adjustFieldSizes().  

    br
    button.updateButton Update
    
    script.
      adjustFieldSizes(10);

The adjustFieldSizes() function is included in the client side javascript and is used to adjust the size of each of the fields used depending on their contents.

function adjustFieldSizes(textSize) {
  $('textarea,adjustable').each(function(index) {
    var length = 0;
    var textArray = $(this).text().split(/\n/);
    var rows = textArray.length;

    for (var i = 0; i < rows; i++) {
      var thisLength = textArray[i].length;

      if (thisLength > length) {
        length = thisLength;
      }
    }
    
    if (length <= 0) length=textSize;
    if (rows <= 0) rows = 1;
    
    $(this).attr('cols', length + 5);
    $(this).attr('rows', rows);
  });
  
  $('input[type=text].adjustable').each(function(index) {
    var length = $(this).text().length;

    if (length <= 0) length=textSize;
      
    $(this).attr('size', $(this).val().length);
  });
}

Now that we have a grasp of how things are displayed, the next article will look at how we allow the application to update and add records.


Last edited 05/10/2016 at 10:08:22



bvstone

The updateItem Application

Posted:

The updateItem Application

Now that we have seen how we are displaying information from a file that can be updated, we now will focus on how the records are updated.

Not only does this application use node.js on the server side, but it also uses jQuery on the client side.  Following is the jQuery code used for updating and adding new records in our application:

var changedRRNs = [];

$(document).ready(function () {
	doBinds();
});

function doBinds() {
  fieldChange();
  updateRows();
}


function fieldChange() {
  $('body').on('change', ':input', function(event) {
    var changedRRN = $(this).closest('tr').attr('data-rrn');
    
    if ($.inArray(changedRRN, changedRRNs) < 0) {
      changedRRNs.push(changedRRN);
    }

  });    
  
}

function updateRows() {
  $('body').on('click', '.updateButton', function(event) {
    $.each(changedRRNs, function(index, rrn) {
      var thisRow = $('[data-rrn="' + rrn + '"');
      var formData = '';

      thisRow.removeClass('errorUpdating');
      thisRow.addClass('updating',1000);
    
      var i=0;
    
      $(':input', thisRow).each(function() {
        i++;
      
       if (i > 1) {
          formData = formData + '&';
       }
      
        formData = formData + encodeURIComponent($(this).attr('name')) + '=' + encodeURIComponent($(this).val());  
      });

      var postURI ='';
      var dataID = thisRow.attr('data-ID');

      if (dataID == 'detail') {
        postURI = '/updateItem/' + rrn;
      }
   
      if (postURI != '') {
    		$.post(postURI, formData, function( data ) {
  		  })
        .fail(function(errorThrown) {
           thisRow.addClass('errorUpdating',1000);
           console.log('error:' + errorThrown.responseText);  
         })    
    		.always(function() {
          thisRow.removeClass('updating',1000);
    		});
      }   

    }); 

    changedRRNs = []; 
    location.reload()
  });
}

At first this may seem like a lot, but when broken down into it's smaller units it becomes clear how things work.  Lets take high level look at the parts of this client side JavaScript:

  • changedRRNs - This is a global array used to keep track of the Relative Record Numbers that are changed.  
     
  • doBinds() - This is just a function I use to call all the binding functions when the page is loaded.  Instead of coding each of the jQuery action events this makes things cleaner and describes the actual actions that are used in the application.
     
  • fieldChange() - This jQuery action function is used to watch for changes to any input field on the page.  When a field changes we add the RRN to the changedRRN array for processing later.
     
  • updateRows() - this jQuery action function is triggered when the Update button is clicked.  When it is clicked we iterate through the list of changedRRNs.  We use the RRN to get the values from each of the fields in each row and call the jQuery .post() function (which is shorthand for an Ajax POST).

What's neat about the process is that we also update each row that is being updated with a class that makes it's background blue.  When the .post() is complete we remove the class to show the update is done.  If an error occurs during the update we add a class to the row to make it red to show that the row was NOT updated.  This type of DOM manipulation is what makes jQuery so powerful and easy to make your application appear more interactive.

The updateItem Node.js Application

In the above jQuery we see that the .post() function is made to /updateItem.  For each item that is updated we call this application to to update the specific record.

The server side updateItem.js application is as follows:

var express = require('express');
var myStuff = require('mymodules');
var router = express.Router();

var DBname = "*LOCAL";
var Library = 'BVSTONES';
var Table = 'ITEMPF';

router.post('/:rrn', function(req, res, next) {
  myStuff.setDBname(DBname);
  myStuff.setDBschema(Library);
  myStuff.setDBtable(Table);
  
  var rrn = req.params.rrn;
  
  if (rrn > 0) {
    var rc = myStuff.updateTableByRRN(rrn, req.body);
  } else {
    var rc = myStuff.insertIntoTable(req.body);
  }

  var result = 'Item Updated.'
  
  if (rc < 0) {
    result = 'Error updating item.';
  }
  
  res.send(result);
});

module.exports = router;

Again, we start with the requires.  In this case we are importing the standard functions for express as well as mymodules, which is the group of functions that I created specifically for data retrieval and updates on the IBM i.

We set the names of the database, library and table that we are working with.  In this case again it is the ITEMPF file in library BVSTONES.

In order to know which row to update we are passing in the Relative Record Number as a parameter on the call to the function:

  • /updateItem/<rrn>

The RRN in this case is used for the update because there's no way for each file to know the exact key fields to use for the update.  So as long as we aren't reorganizing our files every 10 minutes the RRN used in the web page should still match the RRN in the database itself and can be used for updating (or adding in this case).

Next we retrieve the parameters passed in when the .post() jQuery function made a POST request to our updateItem node.js application.  This will be a list of fields and their values.  If you recall when we created the list of items, each field had a name that was the actual database field name.  So, the past data would look something like this:

ITITEM=BLUECAR&ITIDESC=Light%20Blue%20Car&ITPRICE=1000.00&ITQTY=1

Now, the cool thing is when we request to use this data it's returned in JSON format so it's more usable in our application.  Here is what the data looks like using the req.body property:

{ ITITEM: 'BLUECAR',
  ITIDESC: 'Light Blue Car ',
  ITPRICE: '1000.00',
  ITQTY: '1' }

So, now that we have this we simply pass it into either the updateTableByRRN() or insertIntoTable() function.

In the next article we will finally take a look at the mymodules (mystuff) node.js module to see the nuts and bolts of the application and how we have started putting together our own set of functions to make database interaction easier.


Last edited 05/10/2016 at 10:34:58



bvstone

The mymodules Node.js Module

Posted:

The mymodules Node.js Module

Now that we've seen the functionality of our application we can take a closer look at the meat and potatoes that allows us to easily interact with a database.  Whether it's returning a result set, updating a record, or inserting a new record, that functionality is all part of the mymodules node.js module.

The source is as follows:

var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2');
var util = require('util');
var DBname, DBschema, DBtable = '';
var DBcommit = false;
var DBtrim = true;

function trimRow(row) {
  for (i in row) {
    row[i] = row[i].trimRight();
  }  
  
  return row;
}

function error(message) {
  console.log(message + '\n' + new Error().stack);  
}

function checkDB () {
  if (DBname == '') {
    error('Database name cannot be blank.');
  }

  if (DBschema == '') {
    error('Database schema cannot be blank.');
  }

  if (DBtable == '') {
    error('Database table cannot be blank.');
  }  
}

function getFieldType(field) {
  switch(field) {
    case db.SQL_NUMERIC:
    case db.SQL_DECIMAL:
    case db.SQL_INTEGER:
    case db.SQL_SMALLINT:
    case db.SQL_FLOAT:
    case db.SQL_REAL:
    case db.SQL_DOUBLE:
    case db.SQL_BIGINT:
      return 'numeric';
      break;
    case db.SQL_DATE:
      return 'date';
      break;
    case db.SQL_TIMESTAMP:
      return 'timestamp';
      break;
    default:
      return 'text';
      break;
  }

}

function getFieldDescriptions() {
  checkDB();

  var sql = "select COLUMN_NAME, COLUMN_TEXT from QSYS2.SYSCOLUMNS where SYSTEM_TABLE_SCHEMA = '%s' and SYSTEM_TABLE_NAME = '%s'"; 
  sql = util.format(sql, DBschema, DBtable);
  var fieldRs = {};
  var resultSet = getResultSet(sql);
  
  for (var key in resultSet) {
    var obj = (resultSet[key]);
    var newKey=obj['COLUMN_NAME'];
    var newValue=obj['COLUMN_TEXT'];
    fieldRs[newKey] = newValue;
  }
  
  return fieldRs;  
}

function getFieldTypes() {
  checkDB();
  
  var sql = "select * from %s.%s limit 1";
  sql = util.format(sql, DBschema, DBtable);

  var typeRs = {};
  
  try {
    db.init();
	  db.conn(DBname);
    db.exec(sql, function(rs) {
      var rc = db.numFields();
    
      for (var i=0; i < rc; i++) {
        var newKey = db.fieldName(i);
        var newValue = db.fieldType(i);
        typeRs[newKey] = getFieldType(newValue);
      }
    
    });

  } catch(e) {
    var errorString = 'Error calling getFieldTypes() for %s.%s.\nsql=%s\n%s';
    errorString = util.format(errorString, DBschema, DBtable, sql, e);
    error(errorString);
  }

	db.close(); 
  
  return typeRs;  
}

function getResultSet(sql) {
  checkDB();
  
  var resultSet;

  try {  
    db.init();
    db.conn(DBname);
    db.exec(sql, function(rs) {
      resultSet = rs;
    });
  } catch(e) {
    var errorString = 'Error calling getResultSet().\nsql=%s\n%s';
    errorString = util.format(errorString, sql, e);
    error(errorString);
  }

	db.close();  
  
  if (DBtrim) {
    for (row in resultSet) {
      trimRow(resultSet[row]);
    }
  }    
  
  return resultSet;    
}

function insertIntoTable(dataObject) {
  checkDB();
  
  var fieldTypes = getFieldTypes();
  var sql = "insert into %s.%s";
  sql = util.format(sql, DBschema, DBtable);

  var keyCount=0;
  var columnList = "";
  var valueList = "";

  for (var key in dataObject) {
    keyCount++;
    var dataValue = dataObject[key].trimRight();  

    var field = "'%s'";
    // See /QOpenSys/QIBM/ProdData/Node/db2.js for definitions

    if (fieldTypes[key] == 'numeric') {
      field = "%s";
      
      if (dataValue == '') {
        dataValue = '0';
      }
    }

    if (keyCount == 1) {
      columnList += "(" + key;
      valueList += "(" + field;
    } else {
      columnList += ", " + key;
      valueList += ", " + field;
    }

    dataValue = dataValue.replace("'", "''");  
    valueList = util.format(valueList, dataValue);
  }

  columnList += ')';
  valueList += ')';

  sql += ' ' + columnList + ' values ' + valueList;

  if (!DBcommit) {
    sql = sql + ' with none';
  }
  
  var rc = 0;

  try {
    db.init();
	  db.conn(DBname);
    db.exec(sql);     
  } catch(e) {
    rc = -1;
    error('Error running SQL:\n' + sql + '\nError:' + e);
  }

  db.close();  
  
  return rc;
}

function updateTableByRRN(rrn, dataObject) {
  checkDB();
  
  var fieldTypes = getFieldTypes();
  var sql = "update %s.%s a";
  sql = util.format(sql, DBschema, DBtable);

  var keyCount=0;
  
  for (var key in dataObject) {
    keyCount++;
    var dataValue = dataObject[key].trimRight();    

    var field = "'%s'";
    // See /QOpenSys/QIBM/ProdData/Node/db2.js for definitions

    if (fieldTypes[key] == 'numeric') {
      field = "%s";
            
      if (dataValue == '') {
        dataValue = '0';
      }
    } 

    if (keyCount == 1) {
      sql = sql + " set %s=" + field;
    } else {
      sql = sql + ", %s=" + field;
    }
    
    dataValue = dataValue.replace("'", "''");  
    sql = util.format(sql, key, dataValue);
}

  sql = sql + ' where RRN(a)=%d';

  if (!DBcommit) {
    sql = sql + ' with none';
  }
  
  sql = util.format(sql, rrn);
  var rc = 0;

  try {
    db.init();
	  db.conn(DBname);
    db.exec(sql);     
  } catch(e) {
    rc = -1;
    error('Error running SQL:\n' + sql + '\nError:' + e);
  }

  db.close();  
  
  return rc;
}

function getDBname() { return DBname;}
function setDBname(name) { DBname = name;}
function getDBschema() { return DBschema;}
function setDBschema(name) { DBschema = name;}
function getDBtable() { return DBtable;}
function setDBtable(name) { DBtable = name;}
function getDBcommit() { return DBcommit; }
function setDBcommit(bool) { DBcommit = bool;}
function getDBtrim() { return DBtrim; }
function setDBtrim(bool) { DBtrim = bool;}

module.exports = {
  getFieldDescriptions: getFieldDescriptions,
  getFieldTypes: getFieldTypes,
  trimRow: trimRow,
  getResultSet: getResultSet,
  insertIntoTable: insertIntoTable,
  updateTableByRRN: updateTableByRRN,
  getDBname: getDBname,
  setDBname: setDBname,
  getDBschema: getDBschema,
  setDBschema: setDBschema,
  getDBtable: getDBtable,
  setDBtable: setDBtable,
  getDBcommit: getDBcommit,
  setDBcommit: setDBcommit,
  getDBtrim: getDBtrim,
  setDBtrim: setDBtrim
}

Lets go through each of these functions one at a time to see how they work.

trimRow()

The trimRow() function is simply used to trim the blanks from the end of each field in a database row.  I found that when data is returned from an IBM i table the text includes all the trailing spaces.  Of course we don't want this when we're displaying the application so we created this simple function that "trims" the trailing spaces off of each field in a result set row.

error()

The error() function is a simple function we use to throw an error to the console.

checkDB()

The checkDB() function is used to make sure that before any database functions are performed that we have set the database name, table an library/schema names.

getFieldType()

This function is used to get a more generic field type description.  There are many data types possible but some of them can be combined into more generic descriptions.  This is in no way complete, but for now it does what we need by describing a column as numeric, date, timestamp or text.

getFieldDescriptions()

This function is used to retrieve an object that contains a list of field descriptions for the file we are working with.  This is done using the system file named SYSCOLUMNS in the QSYS2 library.   

getFieldTypes()

This function is used to retrieve the field type of each column in a database file.  This is done by retrieving one record from the table and then using the fieldName and fieldType methods that are available in the DB2 for i Access APIs and passing them to the getFieldType() function.

getResultSet()

This function is used to return a result set.  Simply pass in a valid SQL statement and the result set will be returned to your application.

insertIntoTable()

This function is used to insert a new row into a table.  The data should be passed in as an object list as described in the previous article in this section.  The format is:

{fieldName: fieldValue, fieldName: fieldValue...}

As an example:

{ ITITEM: 'BLUECAR',
  ITIDESC: 'Light Blue Car ',
  ITPRICE: '1000.00',
  ITQTY: '1' }

This function will return 0 for a successful insert and -1 for an error.

updateTableByRRN()

This function is used to update a specific row in a table.  The first parameter should contain the relative record number (RRN) of the row to update.  The second parameter should contain an object list containing the field name and the data value.  The format of this second parameter should be exactly like the object list described in the insertIntoTable() section above.

This function will return 0 for a successful update and -1 if an error occurs.


Last edited 05/10/2016 at 10:59:27




Reply




© Copyright 1983-2020 BVSTools
GreenBoard(v3) Powered by the eRPG SDK, MAILTOOL Plus!, GreenTools for Google Apps, jQuery, jQuery UI, BlockUI, CKEditor and running on the IBM i (AKA AS/400, iSeries, System i).