How to delete record if table is "shared" 1

[Mandy_crw]

I have initialised sms table alias tsulat in "shared" mode, however in my delete procedure every time i issue pack, it always say file must be exclusive, i tried putting set exclusive on, but still it says it must be exclusive... please help... Thanks and God Bless...

PROCEDURE CmdDelete.Click()

LOCAL lclname as Character

SELECT TSULAT

lclname = CsrDemo.idnum

SEEK lclname

IF FOUND()

FLOCK()

DELETE
PACK

UNLOCK IN TSULAT

ENDIF

SELECT idnum, ALLTRIM(sname) + ", " + ALLTRIM(fname) FROM SMS ORDER BY sname, fname INTO CURSOR csrdemo READWRITE

This.Parent.text1.value = " "
This.Parent.text2.value = " "
this.Parent.text3.value = " "
this.Parent.text4.value = " "
This.Parent.text5.value = " "
This.Parent.text6.value = " "
this.Parent.CboIdnum.value = " "

MESSAGEBOX("Record deleted from to Main Table",0+64,"Student's Record!")

ENDPROC

 

[mjcmkrsr]

Hi Mandy,

Don't use PACK in a multiuser environment as it needs EXCLUSIVE access to the file - please see PACK in the Help File. You also don't need FLOCK() and UNLOCK, but if you are a "belt and suspender type" you may use them.

IF FOUND()

FLOCK()

DELETE
[s]PACK[/s]

UNLOCK IN TSULAT

ENDIF

hth

MarK

 

[Nigel Gomm]

Mandy,

as Mark says, save PACK for an afterhours housekeeping job. You don't need it during 'normal' use and it means shutting all users out of your application to do it.

If you are seeing excessive bloat of memo fields and reaching the 2gb limit...... there are other ways to circumvent that.

hth

n

 

[Mike Lewis]

Just to add to what the others have said ...

Just executing SET EXCLUSIVE ON does not guarantee exclusive use. The setting must be in force before you open the table, and you will then only be able to open the table if no other user or process already has it open. That's why you should never do a PACK as part of day-to-day operation if you are in a multi-user environment.

In any case, there is no reason to pack the table every time you delete a record. Apart from the multi-user implications, it will also slow down your operation considerably (PACK can take a long time with a big table). Just do the PACK as part of an occasional clean-up routine and ideally outside normal working hours.

I have applications in use where the PACK is only done perhaps once a month. Of course, that depends on the size of the tables and how often users delete records, but it is not often necessary to do it more frequently.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Mandy,

In addition to my former post, to show what PACK does and why it slows dramatically down your application. From Hacker's Guide (more than once referred to)

PACK

PACK works by copying all records not marked for deletion to a temporary file. It then renames the old file to a new name, the new file to the original name, blows away the old file, and, if the table belongs to a database container, fixes the header of the new file so it properly points to the DBC.

Some gurus maintain that there exists a critical portion of time between the first rename and the second when a catastrophic system failure (such as the complete loss of power) could leave us with no data files whatsoever. Since in fact we've never heard a single person ever say they lost their data by packing (the window for failure is extremely small) and since issuing the equivalent commands ourselves essentially duplicates the internal process using slower VFP code instead of faster C code, we think this belongs in the realm of "urban myth." However, we feel due caution is wise, considering how painful such a loss could be. You might want to squirrel away a copy of the table just before issuing PACK so if something horrible occurs, you can get the data back.

hth

MarK

 

[Mandy_crw]

Hi Mark, Mike and Nigel.... Thank you for all your helpful answers.... now i understand it well.... i keep on researching.... but no substantial explanations as what you have given... thanks again...

 

[mjcmkrsr]

Hi Mandy

I'm very sorry but I do not understand what you mean by this:

... but no substantial explanations as what you have given ...

MarK

 

[Mandy_crw]

Hi Mark, I mean I tried looking for answers in google, ive reasearched... but i did not find answers... it was you, mike and nigel who gave the answers that i have understood it well.... Thanks so much Mark...

 

[mjcmkrsr]

Hi Mandy,

Please use the help file first - topics are usually rather well explained there - e.g. PACK. I you have doubts please have a look at "Hacker's Guide 7" and "What's new in Nine" or try to get an electronic copy of them - both are available from Hentzenwerke.com

hth

MarK

 

[Mike Lewis]

please have a look at "Hacker's Guide 7"

I second that. But you don't have to buy the book. There is a (legal) open-source version on Github.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

To be fair, usage of SET EXCLUSIVE ON suggests that Mandy did read up on needing exclusive access. The help on PACK refers to SET EXCLUSIVE, see for yourself, MarK:

https://www.vfphelp.com/vfp9/html/acbd9f3d-a745-4914-bdd8-b613dcd94bee.htm

The eye opener is that you can't enforce exclusive access by SET ESXCLUSIVE ON nor by USE some.dbf EXCLUSIVE.

Mandy, what should have told you to not PACK is the way it's described as rewriting the whole dbf without deleted records. If you just have one deleted record in a DBF with all else undeleted, as you always PACK after each deletion, it's not a good idea to do that rewriting. You don't just add a marker to the deleted record, you write the whole DBF without that one deleted row, which is very disproportionate.

The essential thing to know is that you can tell Foxpro to disregard records marked for deletion, by SET DELETED ON:

https://www.vfphelp.com/vfp9/html/456fe115-d38a-4e98-b040-4a00308d9e5a.htm

The reason you likely started to use PACK in development was that SET DELETED is OFF by default in FoxPro. The idea likely is: As the developer and admin of your data you should see deleted rows within BROWSE etc., but if you ask me it's counter productive as the deletion flag than doesn't work as intended.

Also in uual server databases like MS SQL Server, deleted rows don't shrink the data file, ie in MS SQL that's the mdf file and there you have DBCC SHRINKFILE. If you had experience with that you'd know it's natural to only shrink database files as maintenance routine.

There's more to learn about records marked for deletion, but that would lead too far off. I agreee with the overall sentiment of MarK that reading the help topics is useful before asking, but in this case there are many things working together for which asking in a forum helps more, so continue asking "stupid" questions, Mandy. There are no stupid questions.

The help is a reference, not a tutorial, therefoere MarK's other advice to have a Hentzenwerke Book or the Hackers's guide helps more to understand the FoxPro concepts, I totally second that recommendation.

Chriss

 

[Mandy_crw]

Thanks so much Chris... that was enlightening....