Re: V3R2: EMBEDDED SQL

["Ray O'Leary" <roleary@xxxxxxxxxxxxxxxxxxx>]

You need to FETCH rows from your declared cursor, depositing the data into a 
data structure.  Here's one I prepared earlier ;) ...

      C/EXEC SQL                                                         
      C+ DECLARE C1 CURSOR FOR                                           
      C+    SELECT * FROM ELA, ILM                                       
      C+    WHERE ELA.AORD = :W@ORDR                                     
      C+    AND ELA.ALOC = ILM.WLOC                                      
      C+    AND ELA.AWHS = ILM.WWHS                                      
      C+    ORDER BY ELA.AORD, ILM.WZONE, ILM.WPIKS, ELA.ALINE           
      C/END-EXEC           

                                                   
       *                                               
       *  Open up the SQL query defined above.         
       *                                               
      C/EXEC SQL                                       
      C+ OPEN C1                                       
      C/END-EXEC                                       
       *                
      C                     CLEARCMBDS    Clear receiving data structure
       *                                               
       *  Fetch a row from the SQL query.              
       *                                               
      C/EXEC SQL                                       
      C+ FETCH C1 INTO :CMBDS                          
      C/END-EXEC                                       
       *                                               
                                                       
     CMBDS is just a data structure with the same 
field definitions as files ELA and ILM.  I clear CMBDS 
before each fetch, and if the fields are empty after 
the fetch, I know that's the end of my selection.

Also, a gotcha - if the program variable W@ORDR 
changes, you need to close the SQL cursor and reopen 
before the SQL fetch will work correctly.

Regards,
Ray                                           
______________________________ Reply Separator 
_________________________________
Subject: V3R2: EMBEDDED SQL
Author:  MIDRANGE-L@midrange.com at INTERNET
Date:    15/04/97 16:32


I'm attempting to code an embedded SQL select 
statement for a cursor by building the SQL statement, 
preparing it, and then opening the associated cursor.

I keep getting no records returned.  The SQL select 
looks fine.  Can anyone provide some help?

Here's the code:
D SQLSTR          S            256A                    
D*                                                    
C/EXEC SQL                                             
C+ DECLARE WRKEMPCR CURSOR                             
C+    FOR DYNSQLSTMT                                   
C/END-EXEC                                             
C                   EVAL      SQLSTR = 'SELECT * FROM AFILE +         
C                                 WHERE FLD1 = ' +                    
C                                 VALUE1 +                             
C                                 ' ORDER BY FLD2, FLD3'             
C*                                                                      
C/EXEC SQL                                                              
C+                  PREPARE DYNSQLSTMT                                  
C+                    FROM  :SQLSTR                                     
C/END-EXEC                                                              
C/EXEC SQL                                                 
C+                  OPEN      WRKEMPCR                     
C/END-EXEC                                                 
umidr

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *