Page Comparison

Profound.js Connector provides much of the same Record Level Access support when working with non IBM i database objects.  Below is a list of the functions that are not supported with these other database:

...

js provides simple apis to access your data on any of the supported databases, without having to write a lot of sql.

Some of the capabilities include:

• Read/Write/Update/Delete capabilities
• Fetching records sequentially in either direction
• Fetching records by key
• Multi-part keys
• Use of data structures to fetch and update data
• Use of global record format fields to fetch and update data
• Options for qualified and non-qualified access
• Key data structures
• Updating fields selectively
• Top-down transactional I/O without requiring callbacks
• Access to INFDS (Information Data Structure) values are available
• Prepared statements
• Immediately executed statements
• Parameter binding
• Custom connection attributes
• Remote connections

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

All you need to do to begin using RLA/SQL functions within your pjs programs is to create the DbDefn module.  This module enables you to use RLA functions in lieu of writing a whole lot of sql, and it also allows for database independence.

To create a module we need to name and describe some key parts of that table (or view/logical).

1. Module File name --> example 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.
2. Describing the table:
   1. useSQL - required - Set this to true if you want this to enable RLA/SQL functionality, else it will use the IBM i RLA functionality
   2. dbObject - required - Set this to the actual database object name, can be the name of a table or a view.. 
   3. uniqueKey - required - This is needed to be able to read 1 record at a time in a uniquely specific sequence.
      1. field - the name of the column
      2. ascending: true or false if the column should be sorted in ascending order
   4. dbSchema - optional - Set this to the actual database schema
   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 for using this.  Here are a few:
         1. The database does not support storage of that type of field (such as a Time type field)
         2. The database stores 2 separate fields for Date and Time, but you want to put them together as a single DateTime field
         3. You have choose to store date or time values as string, but you want the UI to show them as true dates and times.

** Important note: table names and field names are case sensitive **

Below is an example of that PRODUCT table.  It was placed directly under the modules folder.  If you have a lot of tables, you will want to better organize them.

This example also shows a couple different examples on how you can use the override.

...

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
    }
  }
}

If you have had a pjs program you don't have to do anything to it, as long as it does not use any of the unsupported functions.

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

...

...

Below are not supported:

• Override database file 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