|
midrange.com
>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-2025 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.
