Record Level Access

Record-Level Access

Record-Level Access (RLA) is RPG's native file I/O model. In OpenRPG, RLA is implemented over ODBC — the file is actually a database table, and the RLA opcodes translate to SQL operations transparently. Any ODBC-connected database works.

Declaring a File

DCL-F filename DISK KEYED EXTDESC('tablename');

Keyword	Meaning
DISK	Disk (database) file
KEYED	Key-based access (CHAIN, SETLL, READE)
EXTDESC('name')	Maps to the SQL table named name

Omit KEYED for sequential-only access.

The table's columns become program-scope variables with the same names as the column definitions. All names are uppercased to match RPG convention.

CHAIN — Random Read by Key

DCL-F CUSTFL DISK KEYED EXTDESC('customers');
DCL-S key VARCHAR(10);

key = 'C001';
CHAIN key CUSTFL;
IF %FOUND(CUSTFL);
  DSPLY CUSTNO;
  DSPLY CUSTNAME;
ENDIF;

%FOUND(filename) is true when the last CHAIN found a record.

READ — Sequential Read

DCL-F CUSTFL DISK EXTDESC('customers');

READ CUSTFL;
DOW NOT %EOF(CUSTFL);
  DSPLY CUSTNAME;
  READ CUSTFL;
ENDDO;

%EOF(filename) becomes true after a READ past the last record.

WRITE — Insert a New Record

Assign values to the field variables, then WRITE:

CUSTNO   = 'C010';
CUSTNAME = 'New Customer';
CUSTBAL  = 0;
WRITE CUSTFL;

UPDATE — Modify the Current Record

After a successful CHAIN or READ, modify fields and UPDATE:

key = 'C001';
CHAIN key CUSTFL;
IF %FOUND(CUSTFL);
  CUSTBAL = CUSTBAL + 500;
  UPDATE CUSTFL;
ENDIF;

DELETE — Remove the Current Record

After a successful CHAIN or READ, DELETE removes that row:

key = 'C010';
CHAIN key CUSTFL;
IF %FOUND(CUSTFL);
  DELETE CUSTFL;
ENDIF;

SETLL — Position to a Key

SETLL positions the file cursor so the next READ starts at the first record with a key >= the argument:

DCL-F CUSTFL DISK KEYED EXTDESC('customers');
DCL-S key VARCHAR(10);

key = 'B000';
SETLL key CUSTFL;

READ CUSTFL;
DOW NOT %EOF(CUSTFL) AND CUSTNO <= 'B999';
  DSPLY (CUSTNO + ' ' + CUSTNAME);
  READ CUSTFL;
ENDDO;

READE — Read Equal Key

READE reads the next record only if its key matches the argument:

key = 'B002';
SETLL key CUSTFL;

READE key CUSTFL;
IF %FOUND(CUSTFL);
  DSPLY CUSTNAME;
ENDIF;

Connection

RLA uses the same ODBC connection as embedded SQL. You can connect explicitly or use rpgc.conf (see Database Connections):

DCL-F CUSTFL DISK KEYED EXTDESC('customers');
DCL-S connStr VARCHAR(200);

connStr = 'Driver={SQLite3};Database=myapp.db;';
EXEC SQL CONNECT USING :connStr;

key = 'C001';
CHAIN key CUSTFL;

Or with rpgc.conf — no EXEC SQL CONNECT needed at all:

DCL-F CUSTFL DISK KEYED EXTDESC('customers');
DCL-S key VARCHAR(10);

key = 'B002';
CHAIN key CUSTFL;
IF %FOUND(CUSTFL);
  DSPLY CUSTNAME;
ENDIF;

Complete RLA Example

**FREE

DCL-F EMPFL DISK KEYED EXTDESC('employees');

DCL-S connStr VARCHAR(200);
DCL-S key     VARCHAR(10);

connStr = 'Driver={SQLite3};Database=company.db;';
EXEC SQL CONNECT USING :connStr;

EXEC SQL CREATE TABLE employees (
  EMPNO   VARCHAR(10) PRIMARY KEY,
  EMPNAME VARCHAR(50),
  SALARY  DECIMAL(9,2)
);
EXEC SQL INSERT INTO employees VALUES('E001','Alice',85000);
EXEC SQL INSERT INTO employees VALUES('E002','Bob',72000);
EXEC SQL INSERT INTO employees VALUES('E003','Carol',91000);

// Random read
key = 'E002';
CHAIN key EMPFL;
IF %FOUND(EMPFL);
  DSPLY ('Found: ' + EMPNAME);
ENDIF;

// Sequential scan
READ EMPFL;
DOW NOT %EOF(EMPFL);
  DSPLY (EMPNO + ' ' + EMPNAME);
  READ EMPFL;
ENDDO;

// Write a new record
EMPNO   = 'E004';
EMPNAME = 'David';
SALARY  = 68000;
WRITE EMPFL;

// Update
key = 'E001';
CHAIN key EMPFL;
IF %FOUND(EMPFL);
  SALARY = SALARY + 5000;
  UPDATE EMPFL;
ENDIF;

// Delete
key = 'E004';
CHAIN key EMPFL;
IF %FOUND(EMPFL);
  DELETE EMPFL;
ENDIF;

EXEC SQL DROP TABLE employees;
EXEC SQL DISCONNECT;
*INLR = *ON;