#sqlrpgle

DISCLAIMER: This is not my usual style of programming video. It’s long format, lots of me muttering at the camera while trying to fix some RPG code. I started recording with no idea if it would work or not. Spoiler – it did in the end. Also featuring an AI generated video, a growling version of me with fantastically lunatic hair. If you are a new programmer, or maybe considering a career in IBM-i…

If you have ever stared at a dusty fixed-format RPG III program from the 1990s and thought “there has to be a better way”, then IBM’s latest AI-powered sidekick might just be your new best friend. Meet IBM BOB (yes, that’s the official name now – Project Bob has grown up). BOB is IBM’s AI-first development partner, built right into a VS Code based IDE and tuned specifically for IBM i workloads.…

A while ago, even before I knew embedded SQL existed I had an idea that would make my life so easy. Truly dynamic SQL - meaning not worrying about types, or lengths, anything.

When I started to learn about embedded SQL, I started to really enjoy RPG. To me, it makes the language perfect - let alone the new free format. Although, with embedded SQL you still have to worry about types which can sometimes be annoying.

While toying with embedded SQL, I had an idea that I think would be useful. You'd use embedded SQL to retrive a whole record into one column as a character type, then you can substring the data out. This seems like a great ideas, but there would have to be a lot of backbone to it. Knowing what columns are in the file, their starting positions and their lengths.

I had that idea about a year ago, but at the time I couldn't figure out how to do it. But yesterday, I finally had a go at creating a service program to do all these things.

The service program that does all the work does use embedded SQL of course, but you don't have to worry about types - everything is a character.

The code looks like this + added comments,

//'Load' the file. This gets all the data about the //columns of this specified file. Library is required. FFSQL_Load('LIAMALLAN1':'CRHIST'); //'Open' will generate the statement and open the cursor. //This procedure can also accept one parameter, a WHERE clause. If (FFSQL_Open()); //'Read' will load the first (or next) record FFSQL_Read(); //'Get' will get a columns data and return //a variable character. There will also //eventually be something to get a fields char length. Text = FFSQL_Get('CR_BASE'); DSPLY Text; Text = FFSQL_Get('CR_CRID'); DSPLY Text; ENDIF; //'Close' will close the cursor. FFSQL_Close();

With the use of QCMDEXC, DSPOBJD and SQL you are able to easily retrive a list of libraries, PFs, LFs.. a lot of different object types. Here's how to do that (get a list of objects in a library) with embedded SQL.

Firstly, declare this EXTPGM prototype so we are able to run commands:

Dcl-PR Run ExtPgm('QCMDEXC'); Command Char(200) Const; Length Packed(15:5) Const; END-PR;

The next part is to run the DSPOBJD command and then put the results into an outfile. Note that I put it into a monitor because it will 'crash' if there are no objects in the library:

Monitor; Run( 'DSPOBJD ' + 'OBJ(#LALLAN/*ALL) ' + 'OBJTYPE(*ALL) ' + 'OUTPUT(*OUTFILE) OUTFILE(QTEMP/OBJLST) ' :80); On-Error *All; Return; Endmon;

You'll need a data structure to store the current record for the cursor, so for my example:

Dcl-Ds CurOBJ Qualified; Object Char(10); Description Char(50); END-DS;

The last part is using embedded SQL to query our outfile, which is simple:

Query = 'SELECT ' + 'ODOBNM, ' //Object name + 'ODOBTX ' //Description + 'FROM QTEMP/OBJLST '; //The outfile EXEC SQL PREPARE TheQuery FROM :Query; EXEC SQL DECLARE TheCursor CURSOR FOR TheQuery; EXEC SQL OPEN TheCursor; If (SQLSTATE >= '02000'); //Error? Else; EXEC SQL FETCH TheCursor INTO :CurOBJ; Dow (SQLSTATE = '00000'); DSPLY CurOBJ.Description; EXEC SQL FETCH TheCursor INTO :CurOBJ; ENDDO; Endif; EXEC SQL CLOSE TheCursor;

Only found this out recently so I had to write about it ASAP - seems a little hacky having to use a command to get this data but it's pretty cool IMO.

Edit: Thanks to @PHPDave, he sent me this code snippet so you don't even have to use DSPOBJD - so you'd just have to replace your select (& update the fields) with the following statement:

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('#LALLAN','ALL') ) AS OBJECTS_IN_LIBRARY ORDER BY OBJCREATED