Database Independent Record Level Access via SQL (In Development)

Profound.js provides much of the same Record Level Access support when working with any of the supported databases. Now you can easily access your data using without having to write a whole lot of sql.

Below is a list of the functions that are not supported:

Override database file support is not supported
Using files with level checking is not supported
QTEMP library/schema is not supported
Record Locking is not supported
Fetching records by relative record number is not supported
Commit and Rollback is not supported
Some of the INFDS (Information Data Structure) fields are not supported

For more information about RLA capabilities, see Record Level Access API.

To begin using SQL to access your data we need to record some key information about the file/table. We will store this information in a javascript file called a DbDefn module.

For instance, you have a file/table in your database named "product". We would:

Name the Module File name --> product.DbDefn.js
1. The module file name must end with .DbDefn.js
2. The first part of the name "product" is how your pjs program(s) will reference it.
Describing the table:
1. useSQL - required - Set this to true so that all PJS programs will access this file using an SQL interface. If you are not using an IBM i database, it will run as if it was set to true.
2. dbObject - required - Set this to the actual database object name, can be the name of a table or a view.. (this does not need to make the file name).
3. uniqueKey - required - This is needed to be able to read 1 record at a time in a uniquely order.
  1. field - the database column name – should be considered case sensitive
  2. ascending: true or false if the column should be sorted in ascending order, default is true
4. dbSchema - optional - Set this to the actual database library/schema. You would normally not need to use this.
5. format - optional - When converting RPG programs to Profoundjs programs this is required. This is the record format name of the IBM i table.
6. overrides - optional - this is where you can override specific fields types and values
  1. There are several reasons where you may need this. Just to name a few:
    1. The database does not support storage of that type of field (such as with Oracle and a Time type field)
    2. The database you choose to stores dates as number or string, but you want the UI to show them as true dates
    3. The database you choose to stores 2 separate fields for Date and Time, but you want to put them together as a single Date and Time field

Below is an example of this module file. If you have a lot of tables, you may want to better organize them.

This example also shows a couple different examples on how you can use that overrides property.

Field named "prcatid" that is stored in the database as character, but will be referenced within the pjs programs as decimal.
Field name "prtime" that is stored in the database as character, but will be referenced with the pjs programs as time.

module.exports = {
  useSQL: true,
  dbObject: "product",
  format: "product",
  uniqueKey: [
    { field: "prid", ascending: true }
  ],
  overrides: {
    prcatid: {
      type: "decimal",
      length:5,
      decimals:0,
      transformToDb: function() { return this.toString().padStart(5,"0") },
      transformFromDb: function() { return Number(this) },
    },
    prtime: {
      type: "time",
      transformToDb: profound.utils.timeToCharTransform,
      transformFromDb: profound.utils.charToTimeTransform
    }
  }
}

Once this module file is created, all pjs programs that do a pjs.defineTable(...) will then use and SQL interface to access the data.

If you are using an IBM i database, you can set the useSQL to false which will revert back to using the standard IBMi record level file operation commands
If you delete that module file, which will revert back to using the standard IBMi record level file operation commands
If you are not using an IBM i database, this module file is required to exist in order to use our Record Level API's

Here is a simple pjs program that reads all the records of a table. It is just like any other pjs program using RLA.

function products() {
    pjs.defineTable("product", { read: true, keyed: true });
    product.positionTo(0);

    var data = [];
    var record = product.fetch();
    while (!pjs.endOfData()) {
        data.push(record);
        record = product.fetch();
    }
   
    console.log(JSON.stringify(data,null,2));
}

exports.run = products;