IBM i Modernization - The User Interface
(Part 12)
The example application which is reviewed in this piece is nearly identical to the one which was used in Part 11. It might help for you to review that before proceeding here.
The main distinction between the two examples is that this one returns data from an SQL query (result set) rather than record-level access (i.e. RPG CHAIN). This presents and opportunity to discuss the idea of externalizing SQL I/O rather than embedding SQL in RPG source members.
Click on the following screen shot to try the application in a new tab or window.
This application presents a drop-down list of cities in Utah. When you select one, a request is sent to an IBM i server that returns a list of "entities" that are located in the "selected" city.
How Does It Work?
Since the client-side code is nearly the same as that which was explained in Part 11, let's jump right into the the ILE RPG code.
//-----------------------------------------------------------------
// procedure prototypes
//-----------------------------------------------------------------
/copy *libl/qrpglesrc,rdwtnapi#1
/copy *libl/qrpglesrc,rdcsrapi#1
//-----------------------------------------------------------------
// module level data
//-----------------------------------------------------------------
d rw e ds extname(rwpgmc) qualified
d c1 s * inz(*null)
d s1 s * inz(*null)
d addr s 160a varying
d filter s 80a varying
d squote c ''''
//-----------------------------------------------------------------
// program entry
//-----------------------------------------------------------------
c *entry plist
c parm rw
//-----------------------------------------------------------------
// retrieve reference to external template
//-----------------------------------------------------------------
/free
if s1 = *null;
c1 = csrNew('TLOC100P':'NAME, GADDRSTR');
s1 = wtnOpen('IUI100');
else;
csrSetInst(c1);
wtnSetInst(s1);
endif;
//-----------------------------------------------------------------
// retrieve and return entity name and address
//-----------------------------------------------------------------
filter = 'CITY = ' + squote + wtnQryGet('city') + squote;
csrSetFilter(filter);
csrRefresh();
dow csrGoto(csr_next);
addr = %trimr(csrColStr('NAME'))
+ '<br>' + %trimr(csrColStr('GADDRSTR'));
wtnRecSet('ADDRESS');
wtnFldSet('ADDR':addr);
wtnRecWrt('ADDRESS');
enddo;
wtnRecWrt('FINISH');
return;
/end-free
The first thing that you might notice is that the program has NO "file" specifications ("F" specs). This may be of interest to some because it addresses a problem that has nagged RPG programmers for years - the forced recompile of programs when the layout of database files (tables) change.
While the program has no "F" specs, neither does it have any embedded SQL. The procedural interface that I'm using is a high-level wrapper around the IBM i SQL-CLI interface which has proven to be quite functional, readable, and elegant.
The Cursor API
This program pulls in a set of procedure prototypes that reference service-program procedures which are bound to the program at compile time. The procedures implement a nice programming interface for working with SQL and SQL result sets (cursors). A cursor in this case is an SQL query against any externally defined database object (i.e. table or logical file such as an SQL view).
A brief explanation of the "cursor" procedures used in this program:
- csrNew() - creates a new instance of a cursor; returns a pointer to it. The 10-character data object name is passed as a parameter. A string containing a comma-separated list of column names is an optional second parameter.
- csrSetInst() - tells the cursor engine which cursor to work with. This may be necessary when multiple cursors are open within a single program, or multiple programs are called within a single IBM i JOB, wherein each program may hold references to different cursors.
- csrSetFilter() - defines an SQL "where" clause, which is passed as a string parameter.
- csrRefresh() - generates a result set (cursor); dynamically runs an SQL SELECT statement.
- csrGoto() - fetches a row from a cursor (i.e. first, next, prior, last, by row number).
- csrColStr() - returns a string value from a named column in the current row. Other procedures return values of other data types (i.e. integer, real, float, date, time, timestamp).
In my opinion, RPG code is simplified (often significantly) when SQL statements are defined externally in SQL views, and referenced by a name. The remaining code is more readable. Other advantages include:
- No need for a pre-compiler.
- Error handling is simplified (i.e. no SQL code values to deal with).
- Programming interface is simplified (i.e. no SQL diagnostics or descriptors to deal with; fewer options for programmers to deal with).
Cursor Utilities
This example application includes a drop-down list of "cities" in Utah which is dynamically generated at runtime from the contents of a JSON array (cities.json), which is generated from the following SQL view:
select distinct city from tloc100p
I used the following program to generate the cities.json stream file from the SQL view:
//-----------------------------------------------------------------
// procedure prototypes
//-----------------------------------------------------------------
/copy *libl/qrpglesrc,rdcsrapi#1
//-----------------------------------------------------------------
// miscellaneous variables
//-----------------------------------------------------------------
d cols s 128a varying
d stmf s 128a varying
d c1 s *
d squote c ''''
/free
csrInit();
cols = 'CITY AS VALUE, CITY AS TEXT';
c1 = csrNew('TLOC100V1':cols);
stmf = '/rdweb/info3/iui12/cities.json';
csrSetOrder('TEXT');
csrRefresh();
csrToStmf(stmf:'4');
return;
/end-free
The procedure to note is csrToStmf() (cursor to stream file) which is a utility that generates a JSON array from an SQL result set. Actually the procedure transforms cursors to a variety of formats (i.e. XML, CSV, Plain Text).
If the stream file name is empty, the procedure outputs the stream to the HTTP server. So it can be used to dynamically generate JSON arrays and return them to browsers.
Summary
Transitioning from the display-file paradigm in order to support browser user interfaces provides an opportunity to adopt procedural interfaces that simplify and streamline database programming. The cursor API shown in this article is an example of that.