CODEWITHSUNDEEP
siebel
AJPerez
AJPerez

Reputation: 3595

How can I delete records in a loop?

I'm writing a business service which needs to iterate all the records in a BC, and delete some of them:

bc.ExecuteQuery(ForwardBackward);
var isRecord = bc.FirstRecord();
while (isRecord) {
  if (...) {
    bc.DeleteRecord();
  }
  isRecord = bc.NextRecord();
}

However, DeleteRecord moves the cursor to the next one, so I would be skipping a record for each one being deleted, which is not what I want. Is there any standard-ish way to solve this?

I guess I could just make two loops, one for the checks and another for the deletion... it would work, but it just feels silly to iterate twice over the records, there has to be a better way than this:

bc.ExecuteQuery(ForwardBackward);
var isRecord = bc.FirstRecord();
var list = [];
while (isRecord) {
  if (...) {
    list.push(bc.GetFieldValue("Id"));
  }
  isRecord = bc.NextRecord();
}

bc.ClearToQuery();
bc.SetSearchSpec("Id", "='" + list.join("' OR ='") + "'");  // "='1-ABCD' OR ='1-1234'"
bc.ExecuteQuery(ForwardBackward);
while (bc.FirstRecord()) {
  bc.DeleteRecord();
}

Upvotes: 1

Views: 3928

Answers (3)

Jcl
Jcl

Reputation: 28272

Since you are doing ForwardBackward, you could just add a PreviousRecord call after deleting:

bc.ExecuteQuery(ForwardBackward);
var isRecord = bc.FirstRecord();
while (isRecord) {
  if (...) {
    bc.DeleteRecord();
    bc.PreviousRecord();
  }
  isRecord = bc.NextRecord();
}

Or simply do the loop backwards:

bc.ExecuteQuery(ForwardBackward);
var isRecord = bc.LastRecord();
while (isRecord) {
  if (...) {
    bc.DeleteRecord();
  }
  isRecord = bc.PreviousRecord();
}

Upvotes: 1

Shubhraj
Shubhraj

Reputation: 21

Are are storing the value of a first record in var and then in the loop you are checking if records are presents. I suggest you go for forward and backward try. The value of var is incremented, So, Inside the while loop place the move back an element command and so var will now store the previous element. After executing the loop. You can easily increment to the next element.

Upvotes: 0

Eugene Shatilo
Eugene Shatilo

Reputation: 146

Solution will depend directly on your if (...) condition:

  • If it's a simple condition, which can be converted to a search expression, then you just simply can make a query and delete everything that was found.

  • If it's a complex condition, which cannot implemented as a searchspec, then you can do this in two ways (both has its own disadvantages): a) Use awkward combination of DeleteRecord and PreviousRecord. b) Create second method that will delete single record and call it for each record that satisfy the condition, something like that

    function deleteRecord(sId:chars) {
  ...
  bc.SetSearchSpec("Id", sId);
  bc.ExecuteQuery(ForwardOnly);
  if (bc.FirstRecord()) {
    bc.DeleteRecord();
  }
}
... 
bc.ExecuteQuery(ForwardBackward);
var isRecord = bc.FirstRecord();
while (isRecord) {
  if (...) {
    deleteRecord(bc.GetFieldValue("Id"));
  }
  isRecord = bc.NextRecord();
}
...

Your solution can be and option c), but it will not work when Id list will be too big, because such query will lead to a huge SQL statement and databases have some limitations on SQL query size (and DBA won't be happy to see such queries in logs).

I prefer option b). You can optimize it by creating a second instance of the same BC in your main function and pass it by reference to the deleteRecord function.

Upvotes: 1

Related Questions