Description
I am very much interested in the concept of converting RLA to Embedded
SQL in RPG. It's a task that could improve things for everyone. It's
something that I'd love to include in ILEditor. This blog will discuss
how each RLA opcode will convert to Embedded SQL (in theory).
Dcl-F
Let's say we declare our file with the following code
Dcl-F PRODUCTSP;
This would become an easy Dcl-DS with the EXTNAME keyword
Dcl-Ds PRODUCTSP EXTNAME('PRODUCTSP');
End-Ds;
OPEN
This part is important. Since using RLA is like just doing a SELECT * FROM X in SQL, when the file is opened we need to declare a scrollable
cursor that will select all rows from the table.
Open PRODUCTSP;
Would become
EXEC SQL DECLARE C1 SCROLL CURSOR FOR
SELECT * FROM PRODUCTSP;
EXEC SQL OPEN C1;
SCROLL is the most important keyword in this DECLARE statement. A
scroll cursor allows the file to be read in any directory or randomly.
For example the cursor can go forward, backwards and relavtive or
absolute. We need this to retain functionality in some of the opcodes.
READ
Read is a nice simple one. A READ opcode will simple read the next row
from the cursor.
Read PRODUCTSP;
Will become
EXEC SQL FETCH NEXT FROM C1 INTO :PRODUCTSP;
%EOF
%EOF (end-of-file) is used to determine if there are more record in
the file - or if the RLA cursor is at the end of the file.
Dow (NOT %EOF);
Might become
Dow NOT (SQLSTATE = '02000')
SQLSTATE is used in Embedded SQL as a simple way to determine if there
are any under-laying errors within the SQL engine. 02000 means that
there are no more records / no record found.
CLOSE
Close is a nice and simple one. We just simply close the cursor, just
like RLA would.
Close PRODUCTSP;
Would become
EXEC SQL CLOSE C1;
Program example
Let's take a look at a diff for converting a program from Embedded SQL
to RLA. Both the RLA and the Embedded SQL compile and have the same
result.
**free
ctl-opt dftactgrp(*no);
//Dcl-f PRODUCTSP UsrOpn;
Dcl-Ds PRODUCTSP EXTNAME('PRODUCTSP');
End-Ds;
//Open PRODUCTSP;
EXEC SQL DECLARE C1 SCROLL CURSOR FOR
SELECT * FROM PRODUCTSP;
EXEC SQL OPEN C1;
//Read PRODUCTSP;
EXEC SQL FETCH NEXT FROM C1 INTO :PRODUCTSP;
//Dow (NOT %EOF);
Dow NOT (sqlstate = '02000');
Dsply %Char(PRID);
//Read PRODUCTSP;
EXEC SQL FETCH NEXT FROM C1 INTO :PRODUCTSP;
EndDo;
//Close PRODUCTSP;
EXEC SQL CLOSE C1;
Return;
Extras
READP
Read-previous will read the last record, and a scrollable cursor can
handle this.
ReadP PRODUCTSP;
Would become
EXEC SQL FETCH PRIOR FROM C1 INTO :PRODUCTSP;
SETLL *LOVAL
Using SETLL (set-lower-limits) with *LOVAL would move the RLA cursor
to the beginning of the file. In SQL, we also have the ability to do
this with a scroll cursor.
SETLL *LOVAL PRODUCTSP;
Would become
EXEC SQL FETCH BEFORE FROM C1;
FETCH BEFORE will set the cursor to the beginning of the table, and
then the next FETCH NEXT will read the first row. You can also FETCH AFTER which will set the cursor to after the last record, in which you
should FETCH PRIOR to read the last row.