|
>Problem is.. I'm working at a company with >only RPG III so i'm pretty stuck with limited >resources here.. If the company has RPG III, it has RPG IV. They're in the same software package. Whether you'll get fired for doing your job better that the rest of the team (i.e. using modern tools that are already paid for) is another story. -snip- >But i'm always willing to test out the >sql option. Just need a little background >info on how to do so. Basically, embedded SQL requires that you change the source type to SQLRPG. When you compile this, the SQL pre-processor will run and create a temporary source file, convert your embedded SQL into SQL API calls and then compile that temporary source. The final result is an object program that has system API calls embedded in it that interact with DB2 (instead of 'native' access, like READ, CHAIN, WRITE and so on.) The beauty of embedded SQL is that the syntax is exactly the same, whether you put it inside an RPG III program or RPG IV. The dark side is that the reference manuals REALLY are reference manuals; don't expect a tutorial. Then there are the evil pre-processor quirks. Basically, the pre-processor doesn't understand modern RPG, so if you create a program with, say, subprocedures in it, the SQL may or may not compile. IBM is working on it though! The embedded SQL can be as simple as C Z-ADD0 COUNT 70 C/EXEC SQL C+ SELECT COUNT(*) INTO :COUNT FROM ZGEN02/UUAJREP C/END-EXEC But more often, it's more like C/EXEC SQL C+ DECLARE DTLCSR CURSOR FOR C+ SELECT NAME, CITY, STATE, ZIP C+ FROM BUCK/MASTER C+ ORDER BY CITY, NAME C/END-EXEC C/EXEC SQL C+ OPEN DTLCSR C/END-EXEC C NXTDTL TAG C* Trap errors C/EXEC SQL C+ WHENEVER SQLERROR GO TO OPNSUM C/END-EXEC C/EXEC SQL C+ FETCH DTLCSR INTO :NAME, :CITY, :STATE, :ZIP C/END-EXEC C/EXEC SQL C+ CLOSE DTLCSR C/END-EXEC Please note that many people avoid the WHENEVER SQLERROR clause because of the TAG. Feel free to check SQLCOD or SQLSTT instead. Hm. Those are pre-defined variables that are set after every executable SQL statement (like OPEN, FETCH). It's your job as programmer to check one of those (IBM recommends SQLSTT) after every SQL operation. If you don't, you will never know what error occurred! d hostBin s 9p 0 d hostNul s 5i 0 c/exec sql declare bin cursor for c+ select binary from datesample c/end-exec c/exec sql c+ open bin c/end-exec c dow 1=1 c/exec sql c+ fetch bin into :hostBin :hostNul c/end-exec c if sqlstt <> '00000' c leave c endif c hostBin dsply c enddo c/exec sql c+ close bin c/end-exec c eval *inlr = *on or H* Embedded SQL example H Debug Indent(*None) alwnull(*usrctl) * dbgview(*Source) FQsysprt O f 198 Printer Oflind(*InOF) D NULL s 5i 0 D ColScalePrt s 10 inz D* Retrieved variables DRtnData ds occurs(10) D TblLib 10 D TblName 10 D TblType 1 D TblText 50 D ColName 10 D ColType 10 D ColLen 9 0 D ColScale 9 0 D ColText 50 D* Null map for retrieved variables DRtnNullRaw ds occurs(10) D NullMap 4b 0 dim(9) DRtnNullNamed ds D TblLibNull 5i 0 D TblNameNull 5i 0 D TblTypeNull 5i 0 D TblTextNull 5i 0 D ColNameNull 5i 0 D ColTypeNull 5i 0 D ColLenNull 5i 0 D ColScaleNull 5i 0 D ColTextNull 5i 0 D* Program status data structure contains any error message text D SDS D PSDSMG 91 170 C* Declare the cursor (sorta like an F spec...) C/EXEC SQL C+ DECLARE DYNFIL SCROLL CURSOR FOR C+ SELECT t.dbname, t.name, t.type, t.label, C+ c.name, c.coltype, c.length, c.scale, c.label C+ FROM systables t join syscolumns c on C+ t.name = c.tbname and t.dbname = c.dbname C+ WHERE t.name = 'QRPGSRC' and t.dbname = 'QGPL' C/END-EXEC C* Open the access path C/EXEC SQL C+ OPEN DYNFIL C/END-EXEC C* If an error occurs, dump and exit. C* Since this is a test-bed, this is all the error recovery we want C* C If SQLCOD <> 0 C 'Open Error' Dump C Eval *InLR = *On C Return C ENDIF C* Loop until EOF C DoW 1=1 C* For the SQL access path, FETCH is the equivalent of READ C/EXEC SQL C+ FETCH NEXT FROM DYNFIL FOR 10 ROWS C+ INTO :RtnData :RtnNullRaw C/END-EXEC * End of file C If SQLCOD = 100 C Leave C EndIf * Unknown error C If SQLCOD <> 0 C 'Fetch' Dump C Leave C EndIf * Print the details C 1 Do SQLER3 OccurNum 5 0 C OccurNum Occur RtnData C OccurNum Occur RtnNullRaw * Deal with the nulls C Move RtnNullRaw RtnNullNamed C If ColScaleNull = -1 * Test compare to a null value just to see what happens C If ColScale = 1 C 'One' Dsply 'BUCK' C Else C 'Not One' Dsply 'BUCK' C EndIf C If ColScale <> 1 C '<> One' Dsply 'BUCK' C Else C 'Not <> One' Dsply 'BUCK' C EndIf C Eval ColScalePrt = *Blanks C Else C Eval ColScalePrt = %editc(ColScale:'L') C EndIf C If ColTextNull = -1 C Eval ColText = '*null' C EndIf C Except PrtDtl C EndDo * Less records read than requested C If SQLER3 < 10 C Leave C EndIf C EndDo C* Close the SQL access path C/EXEC SQL C+ CLOSE DYNFIL C/END-EXEC C Eval *InLR = *On C Return C* Program error C* C* This is not really an error handler, so much as a "graceful" C* death. If an unexpected error happens, this will do a DUMP, C* then terminate. C *PSSR BEGSR C '*PSSR' Dump C MOVE '*CANCL' ACTION 6 C ENDSR ACTION OQsysprt Ef PrtDtl 1 O TblLib 10 O TblName 22 O TblType 24 O TblText 86 O ColName 97 O ColType 108 O ColLen L 119 O ColScalePrt 130 O ColText 182 This is NOT the best coded stuff in the world by any means. It's a very early test-bed. I can't post my employer's code, so this'll have to do... Hope it was some help, --buck
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.