Using Windows in subfiles and deleting records from subfiles

[JustinWithoutAnE]

I'm trying to make a subfile program which will display a list of accounts. Users will be able to do five things: select option 2 to edit the record, select option 4 to remove the record from the list, select option 5 to view the account details press F3 to exit the program and press F5 to refresh the screen.

If the user picks either option or 5 a window will popup showing the account details. They can leave the window by pressing F12(Cancel) or press Enter in option 2 to save changes and return to the list. I am encountering an issue where after I exit the popup window and press F5 the program will hang. F3 still works to exit the program but the options can no longer be used. I did some debugging and found that if I exit the popup window and execute LoadSubfile my Fetch statement will get the first record as it is supposed to but my checking for %eof returns true and my program does not continue the loading. Below is the code I am using:

...
LoadSubfile();
if (Dspf.Exit);
    leave;
elseif (Dspf.Refresh);
    LoadSubfile();
    iter;
endi;

if (Dspf..SflDsp);
    ReadSubfile();
endif;

*inlr= *on;

dcl-proc LoadSubfile;
    SflDspCtl = *off;
    SflDsp = *off;
    SflClear = *on;
    write CTL01;
    SflDspCtl = *on;
    SflDsp = *on;
    SflClear = *off;

    @opt = '';
    clear #RRN;
    exec sql declare ListOfAccts cursor for
        select distinct(acctnum)
        from TRANSFILE TF
        where TF.prod in (33,34)
        and TF.banknum = :#banknum
        and TF.postdate = :#datejul;

    if (checkSQL() = *off);
        exec sql open ListOfAccts;
        if (checkSQL() = *off);    
            exec sql fetch ListOfAccts into :@acctnum;
            dow checkSQL() = *of;
                if (%eof);
                    leave;
                endif;
                exec sql
                    select cusnum
                    from CustFile CF
                    where TF.banknum = CF.banknum
                    and CF.account = :@acctnum;
                if (checkSQL() = *off);
                    #RRN = #RRN + 1;
                    write SFL01;
                endif;
                exec sql fetch next ListOfAccts into :@acctnum;
            enddo;    
        endif;
    endi;    

    if (#RRN > 1);
        Dsp.SflDsp = *on;
    endif;
end-proc;    

dcl-proc ReadSubfile;
    dow (1-1);
        readc sfl01;
        if (%eof);
            leave;
        endif;

        if (@opt = '4');
            confirmation();
            if (#confirm = 'Y');
                <code to delete record rom sfl01>
            endi;
        endif;

        if (@opt = '5');
            dow (1=1);
                if (Dspf.CANCEL);
                    leave;
                endi;
                exfmt dtlwin;    
            enddo;
        endif;
        @opt = '';
        update sfl01;
    enddo;
end-proc;

This code is based on the sample program from the article http://www.rpgpgm.com/2016/05/exampl...odern-rpg.html

I've been trying to fix this for half a day but I seem to be stuck. Am I missing something?
Also, any suggestions on how to delete records from the subfile without deleting records from the PF? My user wants to be able to delete records from the subfile because they plan on writing the contents of the subfile to a PF. They don't want to delete them from the source file, just exclude some records from being written to the destination file.

[wegrace]

I believe that you are getting the eof% immediately because the sql cursor is not being closed and re-opened before being used again.

This leaves the cursor pointer at the end of returned list of rows.

Regarding deleting a sfl record, look at this article.

Walt

[JustinWithoutAnE]

Which is better: implicitly closing the cursor before I declare it or closing it after the first endif after the do loop? And do you know if they posted the sample code they mentioned in the thread anywhere?

[wegrace]

For this case, you should explicitly close the cursor after 'the first endif after the do loop'.

You should also check your sql option settings and make sure that CloSqlCsr=*ENDMOD is set.

This will allow the system to close the cursor if the program blows up between the open and the close.

Otherwise the program will blow up next time you run it, stating that the cursor is already open.

I don't know the answer to your last question. Perhaps messaging the OP of that post.

Walt

[JustinWithoutAnE]

How do I check my sql option setting?

[B.Hauser]

Which is better: implicitly closing the cursor before I declare it or closing it after the first endif after the do loop? And do you know if they posted the sample code they mentioned in the thread anywhere?

I'd close the cursor before opening (just to make sure that the cursor IS closed) it and close it after the EndDo (which is the correct place).

You should also check your sql option settings and make sure that CloSqlCsr=*ENDMOD is set.

I'd NOT suggest to change the compile option CLOSQLCSR from *ENDACTGRP to *ENDMOD.
When executing a program compiled with *ENDACTGRP the ODPs (Open Data Path) are kept alive as long as the activation group exists.
When executing a program compiled with *ENDMOD, the ODP is deleted immediately after the program ends, i.e. the module is left. That means each time the program is called FULL OPENs (for all SQL-Statements) for all SQL Statements must be performed.

A FULL OPEN means a complete optimization must be performed, i.e. validating or generating the access plan, estimating the existing access paths, interviewing the statistics manager and as soon as the access plan is built, opening the ODP.
Opening the ODP means the temporary objects described in the access plan must be built, linked together and populated with data.
Opening the ODP is the most time consuming part in executing an SQL Statement.

With a PSEUDO Close and PSEUDO open, the ODP is kept alive. With the next execution of the query, only the data in the ODP is updated, which is 10-20 times faster than a FULL open.
PSEUDO Opens are performed, if the program is compiled with *ENDACTGRP (and the program is not executed in Activation group *NEW).

Birgitta

[wegrace]

Birgitta, that is generally what I do; *ENDACTGRP, Close, then prepare and open.

However, I read an article last year that recommended using *ENDMOD instead of *ENDACTGRP; the reasons given escape me now. So, I created a second copybook with the *ENDMOD option.

I'll have to find that article and re-read it since I may have misunderstood what was being communicated.

Thank you for the ODP refresher.

Edit: I found the link to the article, *ENDMOD was being recommended for dynamic sql statements for cases where the module ended abnormally. Do you have any thoughts on this, Birgitta?


Justin, to set sql options you must have a 'set option' sql statement in your program.

Put them in a copybook for system consistency, and copy them into each sql program. Below is the sql options in the copybook that I use.


I would love to hear anyones suggestions on changes/improvements to this Set Option statement.

       exec sql
         set option Commit=*NONE,
                    DatFmt=*ISO,
                    DynUsrPrf=*Owner,
                    UsrPrf=*OWNER,
                    DlyPrp=*YES,
                    CloSqlCsr=*ENDACTGRP,
                    SQLpath=*LIBL,
                    DftRdbCol=*NONE,
                    SrtSeq=*LANGIDSHR,
                    Naming=*SYS,
                    AlwBlk=*ALLREAD
       ;

[CRinger400]

FWIW, these are my old notes for reusing ODPs in RPG:

* * * ODP Reuse * * *
For embedded static SQL, DB2 Universal Database for iSeries
only reuses ODPs opened by the same statement *number*.

Use CLOSQLCSR = *ENDACTGRP or *ENDJOB to keep ODP alive.

Try keep SQL string a constant length.

Use ? parameter markers in SQL string.

If eligible to be reused, job log messages are:
1st time SQL is run: ODP Created, ODP Deleted.
2nd time SQL is run: ODP Created, ODP Not Deleted.
3rd time SQL is run: ODP Reused.

The current SQL statement must be very similar to last ODP.

Ringer