Record Level Access API
Overview
The Record Level Access API contains the Profound.js APIs used to read, write, update, and delete database records one record at a time, in the same way that RPG programs access files with operation codes such as CHAIN, READ, SETLL, WRITE, and UPDATE. A file is declared with pjs.defineTable(), which creates a file instance in the calling scope. The record I/O operations are then called as methods of that instance, using the internal file name (for example, productsp.fetchNext()).
Because Profound.js runs these operations top-down without callbacks, the code reads in the same order it executes, which makes it straightforward to convert existing RPG logic or write new business logic.
By default, files are accessed on IBM i through the Profound.js Connector module using native record level access. The same APIs can also be used against SQL databases, or against IBM i files through SQL, by creating a DbDefn module for the file (see Record Level Access via SQL), and against jsonDB tables for Offline Capabilities. In RLA via SQL mode, record locking, access by relative record number, and commitment control are not available.
Defining a File
pjs.defineTable() - Defines a physical or logical file, its record formats, and its fields in the calling scope, and creates the file instance used by the other APIs in this section.
Opening and Closing Files
Files are opened automatically when they are defined and closed automatically when the defining function ends, unless the userOpen configuration option is used. These APIs give the program direct control over the open state.
file.open() - Opens a file that was defined with
userOpen: true.file.close() - Closes the file.
file.isOpen() - Returns whether the file is currently open.
pjs.closeFiles() - Closes all files in the current scope.
pjs.closeAll() - Closes all files in the current scope and releases all data area locks.
Positioning the File
file.positionTo() - Positions the file at the first record with a key equal to or greater than the search argument (
SETLL).file.positionAfter() - Positions the file at the first record with a key greater than the search argument (
SETGT).pjs.kds() - Returns the sub-fields of a data structure as an array for use as a search argument (
%KDS).
Retrieving Records
file.getRecord() - Retrieves a single record by key or relative record number (
CHAIN).file.fetchNext() - Retrieves the next record (
READ).file.fetchNextEqual() - Retrieves the next record if its key matches the search argument (
READE).file.fetchPrevious() - Retrieves the previous record (
READP).file.fetchPreviousEqual() - Retrieves the previous record if its key matches the search argument (
READPE).
Writing, Updating, and Deleting Records
file.write() - Inserts a new record (
WRITE).file.update() - Updates the record that was most recently read (
UPDATE).pjs.fields() - Builds a list of fields so that
file.update()changes only those fields (%FIELDS).file.delete() - Deletes the record that was most recently read, or the record identified by a search argument (
DELETE).file.unlock() - Releases the lock on the record that was most recently read (
UNLOCK).
Checking the Result of an Operation
Each file instance keeps its own result indicators, and Profound.js also keeps a global set that reflects the most recent operation on any file. Use the file. methods when more than one file is in use.
file.found() - Whether the most recent
getRecord(),positionTo(),positionAfter(), ordelete()on the file found a record (%FOUND(file)).pjs.found() - The same indicator for the most recent operation on any file (
%FOUND).pjs.setFound() - Sets the global found indicator.
file.equal() - Whether the most recent
positionTo()on the file found an exact key match (%EQUAL(file)).pjs.equal() - The same indicator for the most recent
positionTo()on any file (%EQUAL).pjs.setEqual() - Sets the global equal indicator.
file.endOfData() - Whether the most recent read on the file reached the end or beginning of the file (
%EOF(file)).pjs.endOfData() - The same indicator for the most recent read on any file (
%EOF).file.status() - The file status code from the most recent operation on the file (
%STATUS(file)).file.getConfig() - Returns a copy of the configuration the file was defined with.
Commitment Control
pjs.commit() - Commits pending changes to all files open under commitment control (
COMMIT).pjs.rollBack() - Rolls back pending changes to all files open under commitment control (
ROLBK).
Offline Data Synchronization
pjs.downloadTable() - Downloads a physical file and its logical files from IBM i to a jsonDB table for offline use.
pjs.uploadTable() - Appends the records of a jsonDB table back to the physical file on IBM i.
Error Handling
Record-not-found and end-of-file conditions do not throw errors; they are reported through file.found(), file.endOfData(), and file.status(). Other failures, such as an I/O operation on a closed file or a duplicate key on a write, throw a Profound.js Error whose statusCode property holds the RPG-style file status code (for example 1211 or 1021). These errors can be caught with a standard JavaScript try/catch block.
Typical Program Flow
The following example shows how the APIs in this section fit together. An order header file and a detail file are defined, the detail lines for one order are read with a partial key, the order total is calculated, and the header record is updated.
function updateOrderTotal(orderNumber) {
// Header is read and updated; detail lines are read only.
pjs.defineTable("ordheader", { keyed: true, read: true, update: true });
pjs.defineTable("orddetail", { keyed: true, read: true });
var total = 0;
// Position to the first detail line for the order and read all lines
// whose order number matches.
orddetail.positionTo(orderNumber);
orddetail.fetchNextEqual(orderNumber);
while (!orddetail.endOfData()) {
total += odqty * odprice;
orddetail.fetchNextEqual(orderNumber);
}
// Retrieve the header record (locked, because the file is open for update),
// set the new total, and write only that field back.
ordheader.getRecord(orderNumber);
if (ordheader.found()) {
ohtotal = total;
ordheader.update(null, pjs.fields(ohtotal));
}
// Both files are closed automatically when this function ends.
return total;
}
exports.default = updateOrderTotal;Related Pages
- file.close()
- file.delete()
- file.endOfData()
- file.equal()
- file.fetchNext()
- file.fetchNextEqual()
- file.fetchPrevious()
- file.fetchPreviousEqual()
- file.found()
- file.getConfig()
- file.getRecord()
- file.isOpen()
- file.open()
- file.positionAfter()
- file.positionTo()
- file.status()
- file.unlock()
- file.update()
- file.write()
- pjs.closeAll()
- pjs.closeFiles()
- pjs.commit()
- pjs.defineTable()
- pjs.downloadTable()
- pjs.endOfData()
- pjs.equal()
- pjs.fields()
- pjs.found()
- pjs.kds()
- pjs.rollBack()
- pjs.setEqual()
- pjs.setFound()
- pjs.uploadTable()