Problem with updating status of PF1 in SQLRPGLE

440 pts.
Tags:
AS/400
SQLRPGLE
Please help. I am trying to execute the below code but the update doesn't seems to be happening. 

DDATS E DS EXTNAME(PF1) C/EXEC SQL C+ DECLARE TESTC CURSOR FOR SELECT * FROM PF1 ORDER BY NUMBER C+ FOR UPDATE OF STATUS C/END-EXEC *OPEN the CURSOR C/EXEC SQL C+ OPEN TESTC C/END-EXEC C/EXEC SQL C+ FETCH TESTC INTO :DATS C/END-EXEC /FREE DOU SQLSTT '00000'; /END-FREE C/EXEC SQL C/EXEC SQL C+ FETCH TESTC INTO :DATS C/END-EXEC /FREE DOU SQLSTT '00000'; /END-FREE C/EXEC SQL C+ UPDATE PF1 C+ SET STATUS = 'X' C+ WHERE CURRENT OF TESTC C/END-EXEC C/EXEC SQL C+ FETCH TESTC INTO :DATS C/END-EXEC /FREE ENDDO; C+ SET STATUS = 'X' C+ WHERE CURRENT OF TESTC C/END-EXEC C/EXEC SQL C+ FETCH TESTC INTO :DATS C/END-EXEC /FREE ENDDO; /END-FREE C/EXEC SQL C+ CLOSE TESTC C/END-EXEC C EVAL *INLR = *ON 

Using the above code, I'm unable to update my PF1. PF1 has the fields (Number & Status) and I am trying to update STATUS as 'X' for all the records. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    First, why are you using a cursor for this? All you need is a single UPDATE statement. A cursor is a very poor choice here. Actually, you should always assume that a cursor isn't needed. Only use one when you cannot do it any other way, which should be rare.   Second, why do you keep dropping in to and out of free-form? That makes it very hard to read. You must be using a very old OS version. I know free-form works well for embedded SQL at least back in V5R3.   Third, what were the compile options for the program? What commitment control is being established and how are you committing the updates?   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    SQLSTT '00000' is this missing an = sign as in SQLSTT = '00000'?  Is this repeated in your code?C/EXEC SQL  C+ FETCH TESTC INTO :DATS C/END-EXEC /FREE DOU SQLSTT '00000'; /END-FREE C/EXEC SQL C/EXEC SQL C+ FETCH TESTC INTO :DATS C/END-EXEC/FREE DOU SQLSTT '00000'; /END-FREE            and what is the commitment level ?                and as Tom said this could be replaced by a singe sql statement. 
    50,595 pointsBadges:
    report
  • philpl1jb
    Interesting format.
    50,595 pointsBadges:
    report
  • TomLiotta
    I think the SQLSTT expression is missing a "not equal" operator. Those characters could be scrubbed from the code because they look like an empty HTML tag. Just another difficulty in presenting code in a browser when browsers want to interpret the characters. -- Tom
    125,585 pointsBadges:
    report
  • juslikdat1
    SQLSTT ’00000′ is this missing an = sign as in SQLSTT = ’00000′?  Is this repeated in your code?While pasting, i made a mistake pasting the code twice while verifying sqlstt. And yes sqlstt have an <> operator in my code. Didnt notice that its not pasted here. Sorry for that. First, why are you using a cursor for this?I am learning sqlrpgle. As per my understanding in case you wanted to retrieve multiple records for processing (Update/delete) then you have to go for cursors. Thats the reason why Cursors have been used. Please correct me if the approach is incorrect.Second, why do you keep dropping in to and out of free-form?Since I am learning sqlrpgle, i preferred the fixed format initially. I shall not do this next time. Thanks for suggesting.Third, what were the compile options for the program? What commitment control is being established and how are you committing the updates?Compilation is done with the default options itself (took a 14 from wrkmbrpdm). And wrt commitment control I am not handling it. I have read that for sqlrpgle commit control is handled by default.  
    440 pointsBadges:
    report
  • philpl1jb
    DOU SQLSTT <>  ’00000  is okI haven't used the where current but it looks okCan you tell if you're actually looping through the file?  You maybe updating the file and commitment control is then undoing it.  Add an Sql Set Option Commit *None, check the format of this command, early in your program 
    50,595 pointsBadges:
    report
  • BigKat
    from a couple of issues trying to compile in the past, I believe the set option needs to be the first SQL statement in the source.  Maybe that has been fixed, I just automatically do it now. 
    8,330 pointsBadges:
    report
  • TomLiotta
    I haven't tested any of this. I only modified the source in Notepad in order to show the basic structure. Changing it to free-form looks essentially like this:
    DDATS E DS EXTNAME(PF1)
     /free
    
      EXEC SQL  DECLARE TESTC CURSOR FOR SELECT * FROM PF1
                  FOR UPDATE OF STATUS ;
    
      // OPEN the CURSOR
      EXEC SQL  OPEN TESTC ;
      EXEC SQL  FETCH TESTC INTO :DATS ;
    
      DOU SQLSTT <> '00000';
        EXEC SQL  UPDATE PF1 SET STATUS = 'X'
                    WHERE CURRENT OF TESTC ;
        EXEC SQL  FETCH TESTC INTO :DATS ;
      ENDDO;
    
      EXEC SQL  CLOSE TESTC ;
      *INLR = *ON ;
    
     /END-FREE
    That might make it a little easier to see what the structure is.   If free-form for embedded SQL isn't available because the OS release is too old, it can be useful to place each of the SQL EXEC blocks into fixed-form subroutines. Then you can use free-form EXSR instructions in the mainline. The mainline structure will then still look clean, and the fixed-form statements can be maintained by themselves.   The cursor and FETCH loop isn't needed for this program. But if there were conditions that controlled the UPDATE so that sometimes rows were updated and other rows were skipped (and you couldn't use a WHERE clause) or if different columns were updated in different rows, the a cursor and FETCH could be the right approach.   If this is just a learning program, it's fine to show it as an example of how cursors look in program instructions.   For the issue of commitment control, try running the program again. If the same results are seen, then run WRKJOB and take option 16, 'Display commitment control status, if active' to see if any show up. If any do show up, then the UPDATEs may need to be committed before the program ends.   If the program works when running it again, you might need to start over from the beginning of creating the table. Log on to a fresh session, delete the PF1 table, log off and back on, then create PF1 again, add any rows that you think should be in the table, and call the program. See if it updated the rows.   The most likely reason that the UPDATEs didn't take is that a commitment definition was started at some point. I don't know when it happened because I didn't see every step. You'll need to investigate to see where it might have started.   Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following