Re: Question on embedded SQL in RPG with Update

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Tom,

On your declare cursor/select statement, you need "for update of 
XXXPY70P"... for example:

 C/EXEC sql
 C+ DECLARE A CURSOR FOR
 C+ SELECT *
 C+ FROM XXXPY70P
 C+ WHERE SUBSTR(DEDSC1, 68, 3) > '   '
 C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'
 C+ FOR UPDATE OF XXXPY70P
 C/end-exec

And on the update, you need to use the filename -- NOT the record format 
name.  (You never use the record format name in SQL)

 C/exec sql
 C+    update xxxpy70p
 C+ set dedsc1= substr(dedsc1,1,67)
 C+ WHERE CURRENT OF A
 C/end-exec

Though, I wonder if you really need to declare a cursor and all of that. 
 Do you need the fields in your RPG logic?  If not, you can just do this:

 C/exec sql
 C+    update xxxpy70p
 C+ set dedsc1= substr(dedsc1,1,67)
 C+ WHERE SUBSTR(DEDSC1, 68, 3) > '   '
 C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'
 C/end-exec

Good luck


Tom Deskevich wrote:
> I am trying to do an update while in a loop with embedded SQL in RPG.
>
> When I enable the update, SQLSTATE returns 24501 and nothing seems to be put
> in the buffer.
>
> When disable the update, it runs fine.
>
> I cannot find any examples of updating using embedded SQL.
> I have the file format referenced in the UPDATE. I had the name of the
> physical file.
> Worked the same. I also tried using the name of the external data structure,
> same results.
>
> Here is my code:
>
> FQsysprt   O    F  120        PRINTER OFLIND(*InOF)         
> DPRODOCKE       E DS                  EXTNAME(XXXPY70P)     
> C/EXEC sql                                                  
> C+ DECLARE A CURSOR FOR                                     
> C+ SELECT *                                                 
> C+ FROM XXXPY70P                                            
> C+ WHERE SUBSTR(DEDSC1, 68, 3) > '   '                      
> C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'                         
> C/end-exec                                                  
> C/EXEC sql                                                  
> C+   OPEN A                                                 
> C/end-exec                                                  
> C     SQLSTATE      Downe     '02000'                       
> C/EXEC sql                                                  
> C+   FETCH A INTO :PRODOCKE                                 
> C/end-exec                                         
> C                   If        sqlstate='02000'     
> C                   leave                          
> C                   Endif                          
> C/exec sql                                         
> C+    update xxxpy70f                              <<< when enabled, no recs
> end up in buffer   
> C+ set dedsc1= substr(dedsc1,1,67) CONCAT '   '    <<< disabled, prints all
> recs that qualify
> C+ WHERE CURRENT OF A                              
> C/end-exec                                         
> C**                 except    print                
> C                   Enddo                          
> C/EXEC sql                                         
> C+  CLOSE A                                        
> C/end-exec                                         
> C                   eval      *inlr=*on            
> OQsysprt   E            print          2           
> O                       deccy                      
>
>
>
> Tom Deskevich
> Infocon Corporation
> Phone 814-472-6066
> Fax 814-472-5019