Problem with multiple deletes

[JerryBrightbill]

I am looking for a way to check if the deletion process is complete when a number of records in a child browse in a set are highlighted and deleted. When the records are all deleted, I need to pack the child table and clean up indexes. I have a set that must use filtered indexes (I wish I had a better way, but) and I get the dreaded Heap Lock Failure after I delete a number of records. Deleting one record at a time is no problem as I can attach a script to the OnDelete event in field rules, but multiple deletes fire the OnDelete event on each delete and the program crashes.
Anyone with a workaround?

Jerry

[Daryl Grace]

RE: Problem with multiple deletes

Jerry,

I think there is a command .delete_range() at the table level. I have used it and it works well! You might check it out.

Daryl

[whanigsberg]

RE: Problem with multiple deletes

Jerry,

That's a good suggestion Daryl made but note that delete_range will not trigger your on-delete code which can be a good thing or a bad thing depending on circumstances.

I have never had any hangups or issues with the command.

I discovered this while preparing the little article on querying child records which was recently posted at learn alpha.com. It's a side issue but I discuss it a bit in the article.

Bill

[Jim Rowlen]

RE: Problem with multiple deletes

Where our accountant has to delete records from a report we use a form with an embedded browse. She then marks ([Ctrl][M]) each record/row as needed. After marking all applicable records a button is clicked which runs a global script to delete marked records. Selection criteria used is

marked("FileNameWithoutExtension")

After this she clicks a button which performs a pack operation. Xbasic, but don't remember it off hand. Something like

TblPtr.pack(.T.)

As far as I know, we haven't had heap locks using this method. If you use an index with a filter checking for marked records you might. I've noticed when deleting ([Ctrl][D]) a number of records a heap lock can be generated which, if the table's indexes aren't rebuilt, can result in index corruption forcing you to rebuild them. The method above has not, to my knowledge, resulted in any problems.

[JerryBrightbill]

RE: Problem with multiple deletes

Lots of good suggestions. The problem with delete_range() is that I want to only delete child records that are selected by the user from an embedded browse. As far as marking the records, I tried that method also, but for some reason this doesn't work in my set for child records. [CTRL-M]has no effect on the records. The set I use has the same child table linked four times to the same parent with different filtered indexes. This obviously also causes some problems when records are saved, but I have a workaround for that.

If I eliminate the OnDelete field rules, I can highlight multiple records and delete them, but then the remaining records don't link correctly until the indexes are reindexed. I may have to resort to adding a button to manually pack the tables, but I was hoping to automate the process to make it more transparent.

Thanks for the ideas.

Jerry

[Peter.Wayne]

RE: Problem with multiple deletes

If you have a script that deletes the records after they have been selected, then the script will work without the Heap Lock error if you can issue a
batch_begin() and batch_end() around the code that does the deletes. The success of the batch_begin() and batch_end() requires exclusive access to the table.

[JerryBrightbill]

RE: Problem with multiple deletes

Peter,

Thanks for the idea about batch_begin() and batch_end(). I had forgotten about their use. I think my initial post was somewhat unclear about my problem. I was looking for a way to determine when the deletion process was complete for all of the highlighted records in a child browse. The reason I wanted to do this was to run a script to automatically pack the tables when the deletion was complete. Since the original post, I have come up with a workaround that might be of use for others. I have posted the solution and explanation below.

I initially came up with a solution which worked if only one child record was deleted. Since pack() requires exclusive use of a table, I had to close the original form, run .pack(), and then reopen the form to the original parent record. I attached a script to the OnDelete field rule for the child table. I have a table in my database with only one dummy record. I use this table for various menu forms and dialog forms. The OnDelete script would open a form in the dummy table that looks just like the form for the set with all fields removed and replaced with a message "Please Wait - Creating File Data". When this form would open, it's OnActivate event would close the original form. When the original form closed, its record number was saved in a global variable. Then I used an OnTimer event on the dummy form to run the .pack() for both the sets parent and child tables. When the pack was done, the OnTimer script would reopen the original form and use the global variable to find the original record. The original form would then close the dummy form. This was fine for only one deletion, but would lock the program if multiple lines were selected for deletion.

After some testing, I isolated one problem to the main form closing before all lines were deleted. I wanted to wait to fire the OnDelete event until all records were deleted. A second problem was that the OnDelete event was trying to activate the dummy form on each delete. The solution to the first problem was simple. If I couldn't determine when all child records were deleted, I simply need to wait an appropriate amount of time to close the main form. Since the main form was being closed by my dummy form, I moved the form.close() lines to the OnTimer event. I set the timer interval to an appropriate time - about 3 sec - and now there is plenty of time for all deletions before the form closes. The second problem was also simple. I used is_object to check if the dummy form was open. If it was, the OnDelete event would end. If not, it uses form.view() to open the dummy form. I found that these solutions work even if the dummy form is loaded with form.load() and never activated. The user never knows what is happening in the background.

I can post the code to each event if anyone wants it. Opening a dummy form and using OnTimer is one way to close a form, work with the tables in exclusive mode, and then reopen the original form with no intervention from the user.

Jerry