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:
- Using files with level checking
- QTEMP
- Record Locking
- Fetching records by relative record number
- Not all of the INFDS (Information Data Structure) values are available
For more information about RLA capabilities, see Record Level Access API.
To begin using RLA/SQL functions within your pjs programs:
Create the DbDefn module which enables you to use RLA functions in lieu of writing a lot of sql
To create a module we need to be able to name and describe some key parts of that table.
- File name --> example product.DbDefn.js
- The file name must end with .DbDefn.js
- The first part of the name "product" is how your pjs program will reference it.
- Describing the table:
- useSQL - optional - Set this to true if you want this to enable RLA/SQL functionality, else it will use the IBM i RLA functionality
- dbObject - required - Set this to the actual database object name, can be the name of a table or a view..
- dbSchema - optional - Set this to the actual database schema
- format - optional - When converting RPG programs to Profoundjs programs this is required. This is the record format name of the IBM i table.
- uniqueKey - required - This is needed to be able to read 1 record at a time in a uniquely specific sequence.
- field - the name of the column
- ascending: true or false if the column should be sorted in ascending order
- overrides - optional - this is where you can override specific fields types and values
- There are several reasons for using this. Here are a few:
- The database does not support storage of that type of field (such as a Time type field)
- The database stores 2 separate fields for Date and Time, but you want to put them together as a single DateTime field
- You have choose to store date or time values as string, but you want the UI to show them as true dates and times.
- There are several reasons for using this. Here are a few:
** Important note: table names and field names are case sensitive **
Below is an example of that PRODUCT table and was placed under the modules folder. It also shows a couple different examples on how you can use the overrise.
- Field named "prcatid" that is stored in the database as character, but will be referenced within the pjs program as decimal.
- Field name "prtime" that is stored in the database as character, but will be referenced with the pjs program 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 } } }
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.
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;