Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 10 of 13 total |
Best strategy for deleting records from table? |
Tue, Dec 9 2014 6:24 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 Roy Lambert |
Wed, Dec 10 2014 8:35 AM | Permanent Link |
Raul 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Maybe Kamran can enlighten us as to what he's doing.
Roy Lambert |
Thu, Dec 11 2014 9:20 AM | Permanent Link |
Raul 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) Raul |
Thu, Dec 11 2014 10:18 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Raul
Here - have the Best post of the year award Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, April 17, 2024 at 10:35 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |