Converting RLA to Embedded SQL

[worksofliam]

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.

[ahaohio]

The problem occurs when combined statements in RLA are dependent on one another - example:
"chain key file"; // Sets access path and reads record but followed by
"readp file"; // Gets prior record based on what the chain found or
"reade key file"; // Gets next - but only if key matches record contents

These are only a few samples. You can find all sorts of wacky stuff in really old RPG code that has been dragged kicking and screaming into the 21st century.

A fully functional RLA to SQL converter is still a holy grail worth working for. It may still be in violation of the 'true' essence of SQL as it would remain a row at a time instead of set-based but for many sites I have been talking to the reason for converting is that it is supposed to be easier for new programmers to understand what is going on in the programs...

[worksofliam]

@ahaohio I actually have an RLA to SQL converter! Perhaps I could share the code at some point on GitHub.

[ahaohio]

Could be a good idea as I think this could be – possibly – a cooperative effort. I have read your blog input pieces on github and they are quite good. But there are loads of problem to solve among those: * A setll is commonly used to check if a row exists for a key without reading any data * A chain can be followed by a read that assumes that you continue reading from that point * Or a readp reading the previous row from where the key found the match and that previous row can perfectly well have a different key value * Dare I mention READE and READPE? * What about DDS defined files with several record formats? (Such as one “logical” file with header and detail information in two formats). When you read such a file sequentially using RLA you get whatever format is next based on the common key. * What about applications that depend on locking a row when it is read? Not by accident – by purpose. That is just a few examples of what I have come across recently but there is definitely more. Från: Liam Barry <notifications@github.com> Skickat: den 6 september 2019 04:11 Till: worksofliam/blog <blog@noreply.github.com> Kopia: ahaohio <konsult@karlpersaker.se>; Mention <mention@noreply.github.com> Ämne: Re: [worksofliam/blog] Converting RLA to Embedded SQL (#16) @ahaohio <https://github.com/ahaohio> I actually have an RLA to SQL converter! Perhaps I could share the code at some point on GitHub. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=ANDI3P4XJBZDHUOT5FE6QADQIG3ZPA5CNFSM4H6UUQVKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6BPZSI#issuecomment-528678089> , or mute the thread <https://github.com/notifications/unsubscribe-auth/ANDI3P5AGD7YAAXGHGDO7W3QIG3ZPANCNFSM4H6UUQVA> . <https://github.com/notifications/beacon/ANDI3P7VEAZQTGUZRLR4T23QIG3ZPA5CNFSM4H6UUQVKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6BPZSI.gif>