Sqlrpgle Update

[bryce4president]

I have to update a field in certain records based on a user's input. I've been fishing around a bit and I think I have a bead on it. I would like some confirmation though...

I think it will look something like this....

/EXEC SQL
+ DECLARE C1 CURSOR FOR SELECT  [I]fields go here[/I]
+                                           FROM
+                                           WHERE
+                                            FOR UPDATE
/END-SQL


/EXEC SQL
+ OPEN C1
/END-SQL

/EXEC FETCH
+ [I]FETCH STUFF GOES HERE[/I]
/END-SQL

[I]DO SOME PROCESSING ON THE INFORMATION[/I]

[B]/EXEC SQL
+ UPDATE  [TABLE-NAME] 
+ SET  [FIELD-NAME] = :[VAR-NAME]
/END-SQL      [/B] 

Is it really that simple? or am I missing something in my update??


P.S. This is SQL embedded in RPGLE

[jamief]

Re: Sqlrpgle Update

look: http://www.code400.com/forum/showthread.php?t=3114

~
  /Free                                     
     begsr $updatecursor;                   
                                            
                                            
  /end-free                                 
                                            
 c/exec sql                                 
 c+ DECLARE mycursor   CURSOR FOR           
 c+  SELECT order from qtemp/orderhdr       
 c/end-exec                                 
                                            
 c/exec sql                                 
 c+ open mycursor                           
 c/end-exec                                 
                                            
                                                       
 c/exec sql                                            
 c+  fetch mycursor                                    
 c+  into  :ordersql#                                  
 c/end-exec                                            
                                                       
                                                       
  /Free                                                
       dow SQLSTT = '00000';                           
      // if I would just put an update in this loop....
      // without (CURRENT OF &cursor) then the entire  
      // table would be updated.                       
      if  OrderSQL#  > 1000006;                        
  /end-free                                            
                                                       
 c/exec sql                                            
 c+ update qtemp/orderhdr set VARYFLD = 'Jamie'        
 c+   WHERE CURRENT OF mycursor                        
 c/end-exec                                            
 /Free                     
     endif;                
                           
 /end-free                 
c/exec sql                 
c+  fetch mycursor         
c+  into  :ordersql#       
c/end-exec                 
 /Free                     
                           
     enddo;                
 /end-free                 
                           
c/exec sql                 
c+ close mycursor          
c/end-exec                 
                           
 /Free                     
                           
    endsr; 

[bryce4president]

Re: Sqlrpgle Update

Can I update more than one field within that update and set statement?

     C/EXEC SQL
     C+ UPDATE BPCSFPG/RCM
     C+ SET CSAL = :NEWREP  
     C+     CLDTE = :TODAY
     C+     CLTME = :CURTIME
     C+     CLUSR = :USERID
     C+
     C+ WHERE CURRENT OF C1
     C/END-EXEC 

[jamief]

Re: Sqlrpgle Update

seperate them by "," comma

[bryce4president]

Re: Sqlrpgle Update

BOOM! Works like a charm.

Thanks Jamie
Congress will be hearing from me about your pay raise approval

[jamief]

Re: Sqlrpgle Update

cool glad it worked.....I seem to update the entire customer master to
semicolons every time I run sql here

[bryce4president]

Re: Sqlrpgle Update

haha, well one of the more experienced programmers here said that he doesn't like to use sql to update in rpgle because you could blow out the entire table if you aren't very careful and exact. he says he prefers to just use the update in rpgle because it only does one record at a time. but i like sql....so i'm not sure....then again, that's why i run things in the test environment first

[jamief]

Re: Sqlrpgle Update

Well Mr.President that is the way to sort out a programmer from a boll weevil.
A programmer by nature is very careful & exact....

Have your experienced guy read this

http://www.systeminetwork.com/article/w/1001/n/5054/i/6474/a/54567/index.html

and tell us what he thinks.

jamie

[bryce4president]

Re: Sqlrpgle Update

Very interesting article Jamie. All through school I tried to learn C++ and do as much development in it as possible. Although I had to use VB 6.0 *cringes* it served its purpose. I would love to develop some C and C++ for our iSeries, but there is one problem.....no one else here can maintain it. So if i would ever leave, or God forbid get fired or worse, then they would be in trouble. This system was built by older ERP guys that have used RPG since it was in version I. And i'm sure you know how hard it is to teach the old dogs new tricks. I think that if Infor would have released BPCSLX in C++ instead of having the code rewritten in RPGLE we could see some change. But no, they went from one dieing language (ASSET) to another (RPGLE).

[jamief]

Re: Sqlrpgle Update

I agree with some of what you are saying, but I can only say with conviction that: "I do like cheese very much."
(the subject of the death of RPG is not a thread you want to start

[littlepd]

Re: Sqlrpgle Update

Bryce,

You may find this helpful: How to Include Source Code in Your Posts