Siebel NextRecord method is not moving to the next record

Question

I have found a very weird behaviour in our Siebel 7.8 application. This is part of a business service:

var bo:BusObject;
var bc:BusComp;

try {
    bo = TheApplication().GetBusObject("Service Request");
    bc = bo.GetBusComp("Action");
    bc.InvokeMethod("SetAdminMode", "TRUE");
    bc.SetViewMode(AllView);
    bc.ClearToQuery();
    bc.SetSearchSpec("Status", "='Unscheduled' OR ='Scheduled' OR ='02'");
    bc.ExecuteQuery(ForwardOnly);
    var isRecord = bc.FirstRecord();
    while (isRecord) {
        log("Processing activity '" + bc.GetFieldValue("Id") + "'");
        bc.SetFieldValue("Status", "03");
        bc.WriteRecord();
        isRecord = bc.NextRecord();
    }
} catch (e) {
    log("Exception: " + e.message);
} finally {
    bc = null;
    bo = null;
}

In the log file, we get something like this:

Processing activity '1-23456'
Processing activity '1-56789'
Processing activity '1-ABCDE'
Processing activity '1-ABCDE'

Exception: The selected record has been modified by another user since it was retrieved.
Please continue. (SBL-DAT-00523)

So, basically, it processes a few records from the BC and then, apparently at random, it "gets stuck". It's like the NextRecord call isn't executed, and instead it processes the same record again.

If I remove the SetFieldValue and WriteRecord to avoid the SBL-DAT-00523 error, it still shows some activities twice (only twice) in the log file.

What could be causing this behaviour?

Answer 1

It looks like in business component "Action" you have join(s) that can return multiple records for one base record and you use ForwardOnly mode to query BC.

Assume, for example, in table S_EVT_ACT you have one record with a custom column X_PHONE_NUMBER = '12345678' and you have two records in table S_CONTACT with column 'MAIN_PH_NUM' equal to the same value '12345678'. So when you will join these two tables using SQL like this:

SELECT T1.* FROM SIEBEL.S_EVT_ACT T1, SIEBELS_CONTACT T2
WHERE T1.X_PHONE_NUMBER = T2.MAIN_PH_NUM

as a result you will get two records, with the same T1.ROW_ID.

Exactly the same situation happens when you use ForwardOnly cursor mode in eScript, in this case Siebel just fetches everything what database has returned. And that why it's a big mistake to iterate over business component while it's queried in a ForwardOnly mode. You should use ForwardBackward mode instead, because in this case Siebel will exclude duplicates records (it also true for normal UI queries, because it also executed in ForwardBackward mode).

Actually this is the most important and less known difference between ForwardOnly and ForwardBackward cursor modes.