Forums >> Programming >> Web Programming >>
The mymodules Node.js Module




Posted:
bvstone

The mymodules Node.js Module

 
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-2017 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).