1 / 23

Embedding SQL in RPG

Embedding SQL in RPG. 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. SQL Terms.

lev
Download Presentation

Embedding SQL in RPG

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related