Re: Embedded SQL examples

Hi Chris,

I have not had much luck doing what you want the way you want, but I have had 
success building the entire statement as a string in COBOL and using the 
"EXECUTE IMMEDIATE" SQL command to execute it.

The program type is SQLCBL and was compiled with the CRTSQLCBL command using  
COMMIT(*NC) since very few of our files are journaled.

More information on Dynamic SQL and the commands that can be used is available 
at:
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/sqlp/rbafymstdynmic.htm
 
That web page also contains a link to information on the "EXECUTE IMMEDIATE" 
statement.

Let me know if I can provide any further information.

Bob

*************   Program Start  ****************
       IDENTIFICATION DIVISION.                               
       PROGRAM-ID.             TESTSQL.                       
       ENVIRONMENT DIVISION.                                  
       CONFIGURATION SECTION.                                 
       SOURCE-COMPUTER. IBM-AS400.                            
       OBJECT-COMPUTER. IBM-AS400.                            
                                                              
       SPECIAL-NAMES.          SYSOUT IS SYS-LST.             
                                                              
       DATA DIVISION.                                         
                                                              
       WORKING-STORAGE SECTION.                               
                                                              
       77  DSPSQLCODE          PIC ------99.                  
                                                              
       77  STMT1               PIC X(100) VALUE               
           "DELETE FROM MYFILE WHERE MYFIELD < ""6126""".     
                                                              
       77  STMT2               PIC X(100) VALUE               
           "DELETE FROM MYFILE2 WHERE MYFIELD2 = ""35""".     
                                                              
       77  STMTSTRING          PIC X(100).                    
                                                              
           EXEC SQL                                           
              INCLUDE SQLCA                                   
           END-EXEC.                                          
                                                              
                                                              
       PROCEDURE DIVISION.                                    
       000-PROGRAM-START.                                     
                                                              
           EXEC SQL                                           
              WHENEVER SQLERROR GO TO 900-SQL-ERROR           
           END-EXEC.                                          
                                                              
           MOVE STMT1  TO STMTSTRING.                         
           PERFORM 100-EXEC-SQL.                              
                                                              
           MOVE STMT2  TO STMTSTRING.                         
           PERFORM 100-EXEC-SQL.                              
                                                              
           GOBACK.                                            
                                                              
       100-EXEC-SQL.                                          
           EXEC SQL                                           
              EXECUTE IMMEDIATE :STMTSTRING                   
           END-EXEC.                                          
                                                              
       900-SQL-ERROR.                                         
           MOVE SQLCODE TO DSPSQLCODE.                        
                                                              
           DISPLAY "SQL ERROR - ERROR:" DSPSQLCODE "  STATE:" 
                                SQLSTATE UPON SYS-LST.        
                                                              
           GOBACK.                                            

*************   Program End  ******************

   ----------------------------------------------------------------------

   date: Thu, 8 Jan 2004 21:07:32 -0000
   from: "Chris Chambers \(M&M Software\)"
                   <cchambers@xxxxxxxxxxxxxxxxxxxxxx>
   subject: [COBOL400-L] Embedded SQL examples

   Hi there,

   I'm trying to write an UPDATE statement within a COBOL program where the

   Filename as well as the values to update are all parameterised.

   I can get the FILENAME to work if I use the PREPARE statement but not the
   values - and I can get the values to work in a straightforward UPDATE but
   the FILENAME is then not allowed as a parameter.

   I had imagined I may be able to construct something like this:

   EXEC-SQL
      UPDATE    :FILENAME
          SET     :FIELDNAME1    TO    :VALUE1
              WHERE     :FIELDNAME2    =    :VALUE2
   END-EXEC  

   If I string it altogether as a prepared statement the resulting command
   does not execute from within the program as it assumes the values to be a
   fieldname but if I copy and paste the same command into STRSQL it works
   fine - so what am I doing wrong?

   Anyone got some examples that work??

   Many Thanks,

   Chris
_______________________________________________
This is the COBOL Programming on the iSeries/AS400 (COBOL400-L) mailing list

To post a message email: COBOL400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/cobol400-l 
or email: COBOL400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives
at http://archive.midrange.com/cobol400-l.