Handling CI without going to NoSuccess !!

Often we Come across a situation where in our program goes to No Success (one of the Reason can be Wrong Prompt Record Value) , if don't handle CI code properly in Application Engine, and we usually end up doing cosmetic changes like using Execute Edits ,SQL exec to resolve the issue, which often increases the CI time, below are the Tips/Step if followed can handle the CI Programs Efficiently.


The following are some important things to remember about CI behavior in AE programs.


1. When running inside of an AE program, a CI's Save() call will not actually commit the data to the database. The commits are under the AE's control. Therefore, if there is a problem with invalid data in a CI property, everything will be rolled back to the previous committed transaction.


2. Required fields in a CI that use a prompt table and have a default value appear to behave in a peculiar way. If an invalid value is entered for the field, the system will change the value to the default value, roll back the rows to the previously committed transaction, and then commit this row to the database. It is a good practice to write errors (such as CI errors) to a log file so that you can go back later and re-enter the record correctly.


3. When a CI fails due to invalid data open cursors are sometimes closed or are left in an invalid state, and subsequent calls to these cursors cause the AE to abend. Testing and debugging help determine what the appropriate solution might be. For example, in some cases the solution may be to ensure that your cursors are open before doing the next fetch (if appropriate). At other times (where the cursor is in an invalid state) you'll need to fetch the rows into an array of records first, and then process them using the CI. This is because even thought the cursor is open (IsOpen call returns true), it is unusable.


RESOLUTION:

To work around the above issues, do the following.

1. Use CommitWork() calls at appropriate intervals (every row or a set number of rows). Remember that to use CommitWork(), (a) "restart" must be disabled; and (b) don't use CallAppEngine() to call the AE program.


2. In the event of a CI error, perform (a) or (b) below.


(a) Check to see if your cursors are open, and if they're not, open them (if appropriate) before doing your next fetch. The following PeopleCode snippet shows an example of how this can be done.


If Not &MYSQL.IsOpen Then

&MYSQL.Open(SQL.MYSELECT);

End-if;


(b) If the cursor is still open but unusable, then you'll need to fetch the rows into an array of records first, and then process them using the CI.


3. Write errors to a log file.


The basic steps (using 2b to fetch the records into an array) would be as follows.


1. Disable restart (and not use CallAppEngine() to call AE)

2. Use a single step of People Code instead of a DoSelect

3. Fetch the records into an array of records and close the cursor

4. Run a For loop calling the CI for all the records in the record array.

5. Do a CommitWork() call after calling save() on the CI for every row (or several rows if performance may be an issue).

6. Do a cancel() call on each row (CI instance) after you called save() and CommitWork()


Warning; If the CommitWork() function is used, you will have to develop a plan in how to handle if your program aborts and may have data half committed.