Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 13 total
Thread Best strategy for deleting records from table?
Tue, Dec 9 2014 6:24 AMPermanent Link

kamran

Hi

I need to empty a table but it is not always possible as other apps that are running deny exclusive access to it.
Here is my code that works only when its the only app running.

try
 datafrm.tbTranDetailMem.Close;
 datafrm.tbTranDetailMem.Exclusive := True;
 datafrm.tbTranDetailMem.Open;
 datafrm.tbTranDetailMem.EmptyTable;
 datafrm.tbTranDetailMem.Close;
 datafrm.tbTranDetailMem.Exclusive := False;
 datafrm.tbTranDetailMem.Open;
 showmessage('All Data Records Deleted - Successfully');
except
 showmessage('Empty Table - Unsuccessful');
end;

1. Is there another better way ?

I have tried to cycle through the records in a while do loop and mark each record in the table using the delete operation but I think it still shows up (eg not officially deleted)

2. Is there a function to permanent delete a record so it does not show up (without having to optimise table) ?

Regards


Kamran
Tue, Dec 9 2014 7:09 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

kamran

>I need to empty a table but it is not always possible as other apps that are running deny exclusive access to it.
>Here is my code that works only when its the only app running.
>
>try
> datafrm.tbTranDetailMem.Close;
> datafrm.tbTranDetailMem.Exclusive := True;
> datafrm.tbTranDetailMem.Open;
> datafrm.tbTranDetailMem.EmptyTable;
> datafrm.tbTranDetailMem.Close;
> datafrm.tbTranDetailMem.Exclusive := False;
> datafrm.tbTranDetailMem.Open;
> showmessage('All Data Records Deleted - Successfully');
>except
> showmessage('Empty Table - Unsuccessful');
>end;
>
>1. Is there another better way ?

The only other way I can think of doing it would probably have catastrophic consequences. Just delete the files that make up the table using DeleteFile or ShellExecute and then recreate it.

>I have tried to cycle through the records in a while do loop and mark each record in the table using the delete operation but I think it still shows up (eg not officially deleted)
>
>2. Is there a function to permanent delete a record so it does not show up (without having to optimise table) ?

A deleted record shouldn't show up. What's possibly happening is that you need to call Table.Refresh in all the apps working with it since the buffers still contain old information.

Roy Lambert
Tue, Dec 9 2014 3:14 PMPermanent Link

Jeff Cook

Aspect Systems Ltd

Avatar

On 10/12/2014 1:09 a.m., Roy Lambert wrote:
> kamran
>
>> I need to empty a table but it is not always possible as other apps that are running deny exclusive access to it.
>> Here is my code that works only when its the only app running.
>>
< snip >
>>
>> 1. Is there another better way ?
>

Am I missing something?  Presumably Kamran is trying to open Exclusive
because he wants to use EmptyTable.  If another app instance has already
got exclusive access, then he can't delete anything - full stop.

But if the other apps only have ordinary non-exclusive opens then why
not simply use SQL

DELETE FROM KamransTable

Cheers

Jeff
Wed, Dec 10 2014 3:12 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jeff


>Am I missing something? Presumably Kamran is trying to open Exclusive
>because he wants to use EmptyTable. If another app instance has already
>got exclusive access, then he can't delete anything - full stop.
>
>But if the other apps only have ordinary non-exclusive opens then why
>not simply use SQL
>
>DELETE FROM KamransTable

One word - speed - now lots of words. EmptyTable essentially just chops off the files at the end of the headers. This is carried out at OS level so its fast. DELETE FROM has to wade through the entire table and set the flag that says the record has been deleted, deal with any and all pre and post deletion code, move to the next record and deal with any and all pre and post scroll code etc.

When I moved to ElevateDB it didn't have an EMPTY TABLE command (either table method or SQL) so, since the metatdata resides in the catalog I just used DeleteFile. The downsde was I just didn't have time for that cup of coffee anymore Frown

Roy Lambert
Wed, Dec 10 2014 8:35 AMPermanent Link

Raul

Team Elevate Team Elevate

On 12/9/2014 6:24 AM, kamran wrote:

> I need to empty a table but it is not always possible as other apps that are running deny exclusive access to it.

For empty table you need exclusive access - there is no alternative.

Using the other delete methods works just fine but of course slower than
empty table :
- either running a "delete * from <table>" query
- or looping thru the dataset and doing delete and next inside the loop.

> I have tried to cycle through the records in a while do loop and mark each record in the table using the delete operation but I think it still shows up (eg not officially deleted)

Can you be more specific here ? What you're describing should never happen.

What are you looping exactly:

- if you obtained the records using a query then make sure your dbisam
query has a live resultset (otherwise you're deleting records from the
temporary resultset and not actual underlying table). if resultset is
live then delete will either work or you'll get an error for records
that cannot be deleted.

- if you're looping on a dbisam table component then you will either
delete the record or it will throw an error if record is locked or such.

There is no scenario i've ever seen where delete succeeded but no record
was actually deleted (or required an optimize). At least not when
working with live resultsets or tables direct.


Raul
Wed, Dec 10 2014 8:57 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Raul


>- if you obtained the records using a query then make sure your dbisam
>query has a live resultset (otherwise you're deleting records from the
>temporary resultset and not actual underlying table).

Good point. The EmptyTable made me think it was a table but it could be a canned resultset hooked up.

Roy Lambert
Wed, Dec 10 2014 3:22 PMPermanent Link

Jeff Cook

Aspect Systems Ltd

Avatar

On 10/12/2014 9:12 p.m., Roy Lambert wrote:
> Jeff
> One word - speed - now lots of words.
Hi Roy

Understood but ...

OK, so he has a "big" table that he needs to clear of all records and
for some reason he wants other apps (or instances of this app) to be
writing to the same table ... sounds to me like a recipe for
inconsistent results at the very least.

Cheers

Jeff
Thu, Dec 11 2014 5:35 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Maybe Kamran can enlighten us as to what he's doing.

Roy Lambert
Thu, Dec 11 2014 9:20 AMPermanent Link

Raul

Team Elevate Team Elevate

On 12/10/2014 3:22 PM, Jeff Cook wrote:
> OK, so he has a "big" table that he needs to clear of all records and
> for some reason he wants other apps (or instances of this app) to be
> writing to the same table ... sounds to me like a recipe for

This is definitely a real world scenario and just like my bank account.
I'm trying to view the balance but other apps keep deleting it (taxes,
mortgage, due payments ,etc) Smile

Raul
Thu, Dec 11 2014 10:18 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Raul


Here - have the Best post of the year award

Roy Lambert
Page 1 of 2Next Page »
Jump to Page:  1 2
Image