Embedding SQL in RPG

1. Embedding SQL in RPG

2. Why SQL? • SQL is the IBM standard for relational database access • SQL is the industry standard for relational database access • SQL is the defacto standard for Client/Server data retrieval • Don’t ask me what that means – it was in an IBM-supplied slide

3. SQL Terms System Name Related SQL Term Library Schema Physical File Table Record Row Field Column

4. SQL Commands • SELECT • Retrieves data from a file (or table in SQL terms) • UPDATE • Updates a record (row) in a file (table) • DELETE • Deletes a record (row) from a file (table) • INSERT • Adds a record (row) to a file (table) Full disclosure: I have not used the UPDATE, DELETE, or INSERT commands in an RPG program yet. (Perhaps Tony has and can share some info with us.)

5. Additional SQL Functions • DECLARE (CURSOR) • Sets the “rules” for what data will be included in the temporary result table and how to sequence it • Similar to the FILE, QRYSLT and KEYFLD keywords on an OPNQRYF in a CL program • OPEN and CLOSE (CURSOR) • Opens and closes the temporary result table • Similar to the OVRDBF, OPNQRYF, and CLOF statements in a CL program • FETCH • Retrieves data from the temporary result table • Similar to the READ or READP Op-Codes in RPG • COMMIT/ROLLBACK (journaling functions) and GRANT/REVOKE (security functions) will not be addressed in this session (primarily because I have not used them to this point).

6. RPG Source Types • SQLRPGLE • SQLRPG • This option (embedded SQL) is not automatically included in the iSeries OS • It is a billable add-on • If your system does not allow it, you can create the source, but you can’t compile them. You can run the compiled objects if they are compiled on a system that does allow it. • These source types go through a “pre-compiler” that parses (interprets) the SQL statements before compiling the RPG code.

7. Compiler Directives • SQL Commands are embedded between 2 compiler directives • C/EXEC SQL • C+ Place your • C+ SQL command • C+ here • C/END-EXEC

8. The DECLARE Function d ds d empnbr 1 5 0 d empnam 6 30 d empdpt 31 33 0 c/exec sql c+ DECLARE emp_cursor CURSOR for SELECT nbr, nam, dpt INTO :empnbr, c+ :empnam, :empdpt FROM empmas WHERE mar_stat = ‘M’ ORDER BY c+ dpt, nam c/end-exec • Program variables must be preceded by a colon • The INTO clause identifies where the data from the retrieved fields (columns) will be stored • Can be specified here or on the FETCH statement • The WHERE clause identifies records to be retrieved (QRYSLT) • The ORDER BY clause identifies sorting criteria (KEYFLD). • ORDER BY dpt DESCEND

9. Additional DECLARE Options • FOR UPDATE OF (Field Name) • Default is ALL columns (fields) can be updated and ALL rows (records) can be deleted • FOR UPDATE OF lists the columns that are to be updated • Columns listed in the ORDER BY clause may not be listed in the FOR UPDATE OF clause • FOR READ ONLY • Specifies no updating/deleting allowed • May improve performance • WITH HOLD • Default: Cursors close when Commit/Rollback commands execute • WITH HOLD keeps the cursor open

10. The OPEN Function c/exec sql c+ OPEN emp_cursor c/end-exec

11. The FETCH Function c/exec sql c+ FETCH NEXT FROM emp_cursor c/end-exec As mentioned earlier, the INTO clause can also be used here c/exec sql c+ FETCH NEXT FROM emp_cursor INTO :empnbr, :empnam, :empdpt c/end-exec • Make sure your fields on the SELECT match your fields on the INTO

12. Additional FETCH Options Alternatives to NEXT • PRIOR Retrieves the row before the current row • FIRST Retrieves the first row • LAST Retrieves the last row • BEFORE Positions the cursor before the first row (do NOT use INTO) • AFTER Positions the cursor after the last row (do NOT use INTO) • CURRENT Retrieves the current row (no change in cursor position) • RELATIVE n If n < -1 Retrieves the nth row before the current row If n = -1 Same as PRIOR If n = 0 Same as CURRENT If n = 1 Same as NEXT If n > 1 Retrieves the nth row after the current row

13. DECLARE for FETCH PRIOR c/exec sql c+ DECLARE emp_cursor SCROLL CURSOR for SELECT nbr, nam, dpt INTO :empnbr, c+ :empnam, :empdpt FROM empmas WHERE mar_stat = ‘M’ ORDER BY c+ dpt, nam c/end-exec SCROLL makes it possible for you to read forward or backward.

14. Example of RELATIVE c/exec sql c+ FETCH RELATIVE 5000 FROM emp_cursor c/end-exec Same as… c do 5000 c/exec sql c+ FETCH NEXT FROM emp_cursor c/end-exec c enddo

15. FETCH and data structures D emp_ds ds D 1 5 0 nbr D 6 30 name D 31 31 job c/exec sql C+ declare emp_cursor cursor for select emp_nbr, emp_name, emp_job C+ from emp c/end-exec c/exec sql C+ FETCH NEXT FROM emp_cursor INTO :empds c/end-exec Full disclosure: I haven’t tried this either. IBM’s slide says it should work.

16. UPDATE • As I said earlier, I haven’t used this command in an RPG program myself yet, but I found an example: c/exec sql c+ update emp c+ set sal = sal + :raise c+ where current of empcsr c/end-exec Will update the currently read record, by adding a program field called raise to the file field called sal

17. The CLOSE Function c/exec sql c+ CLOSE emp_cursor c/end-exec

18. Error Detection & Handling The field SQLCODE stores a return code after every SQL command is executed: • = 0 Successful statement execution • > 0 Successful statement execution, with warning condition • < 0 Unsuccessful statement execution The value indicates the exact condition: • 100 = Row not found • -522 = Not authorized to object

19. Error Detection (Cont’d) WHENEVER statement checks the SQLCA (SQL Communications Area) and can branch to a location based on a condition. There are 3 conditions: • SQLWARNING (SQLCODE > 0 but not = 100) • SQLERROR (SQLCODE < 0) • NOT FOUND (SQLCODE = 100) And 2 possible actions: • CONTINUE • GO TO • c/exec sql • c+ FETCH NEXT FROM emp_cursor INTO :empds where emp_nbr = 500 • c+ WHENEVER NOT FOUND GO TO not_found_tag • c/end-exec

20. Error Detection (Cont’d) Check for an error after every command: c/exec sql c+ FETCH NEXT FROM emp_cursor INTO :empds where emp_nbr = 500 c/end-exec C if sqlcode <> 0 C exsr sr_err C endif

21. Dynamic SQL What is Dynamic SQL? • SQL statements are not predefined in program • Dynamically created on the fly as part of program logic • PREPARE statement can be used in program logic to compile dynamically created SQL statements • Used when run time variables are involved in the selection process or the exact syntax of an SQL statement cannot be pre-determined for any other reason • Since dynamic SQL statements did not go through the precompiler, they need to be interpreted and executed within the program – this can make them resource intensive.

22. 400 d SQL_ok c coost)0) 500 d SQL_more s n inz(*on) 600 d SQL stmt s 500 inz)’select esbnr, eeleb, aspack, 700 d esupc, essc, asmcd, esbri, aslaei, 800 d asbez, es esdnr from artmep 900 d where’) 1000 1100 * Declare SQL Cursor 1200 c/exec sql 1300 c+ declare artmap cursor scroll cursor for sl 1400 c/eod-exec 1500 1600 c eval SQL stmt %trimr(SQLstmt) + ‘ ‘ + 1700 c ‘ASFL = ‘‘‘ + scfleg + 1800 c eval curr group = 1 1900 2000 c select 2100 c when scselt = ‘P 2200 c evel SQL stmt = %trimr(SQLstmt) + ‘ end ‘ + 2300 c ‘ASEG between 700 and 799’ 2400 c when scselt = ‘B’ 2500 c eval SQL stmt %trimr(SQLstmt) + end ‘ + 2600 c ‘ASEG not between 700 and 799’ 2700 c endsl 2800 2900 c if scquel <> *blenks 3000 c eval SQL stmt = %trimr(SQLstmt) + ‘ and ‘ + 3100 c ‘ASMCD = ‘ ‘ ‘ + scqual + ‘ ‘ 3200 c endif 3300 3400 c if scwid c> 0 3600 c eval SQL stmt = %trimr(SQLstmt) + ‘ and ‘ + 3600 c ‘ASBNI = ‘ + scwidalph 3700 c endif 3800 3900 c if eden <> 0 4000 c eval SQL stmt = %trimr(SQLstmt) ÷ ‘ and ‘ + 4100 c ‘ASLAEI ‘ + sclenalph 4200 c endif 4300 4400 c if sclicn c> 0 4500 c eval SQL stmt = %trimr(SQLstmt) + ‘ and ‘ + 4600 c ‘ASLIZD = ‘ + sclicnalph 4700 c endif 4800 4900 c if scstyl c> 6000 c eval SQL stmt = %trimr(SQLstmt) ÷ ‘ and ‘ + 5100 c ‘ASSNR = ‘ ‘ ‘ + scstyl ÷ ‘ ‘ ‘ 5200 c endif

23. 7700 *Prepare variable SQL statement 7800 c/exec sql 7900 c+ prepare s1 from :SQL_stmt 8000 c/end-exec The DECLARE statement declared a cursor called ARTMAP, so you would perform your FETCH from ARTMAP just as if this had been a pre-defined select statement instead of a prepared one.