× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@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 thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.