We help IT Professionals succeed at work.

FoxPro Delete Command

ASPDEV
ASPDEV asked
on
Hello,

I have a FoxPro 6 , where i need to delete all the rows from Test.DBF file.

When I tried, DELETE FROM TEST it works, I test with SELECT Statement.But when I open or Browser through Data Session Menu in Foxpro 6 I still see the data from the Test Table.

I'm just wondering, what it actually doing...
Is at any way I can COMMIT just like in SQL, ORACLE....

Comment
Watch Question

Commented:
when you delete records, they are not really "deleted"
you need to perform a pack command on the database which will REALLY delete them
JF0

Commented:
DELETE only marks the records for deletion. You then issue a PACK command to physically remove the records from the table.

If you really want to delete every record in the table in one command then use ZAP
JF0

Commented:
Also, to suppress any warning prompts when issue PACK or ZAP try this:

Set safe off
zap/pack
set safe on
Always keep SET DELETED ON for every datasession. That way you won't see the deleted records.

Author

Commented:
I need it through an Interface, just like SQL Analyzer.When I tried all your COMMANDS seem's to work, when I browse the Test.DBF file still shows me the Data.

Any answers?????

Commented:
if you have packed the data or zapped the file there is no way the data can still be there - did you close the session where you did the pack or delete? You may need to do that in order for the data to get actually written to the file...
Is the record marked for deletion. Go to a record that you feel is deleted and check the value of DELETED(). Also check the value of SET('DELETED')
Commented:
run:
select TEST
ZAP
REINDE ( if you have any index on that table )

that's all, you won't see any deleted record anymore
Olaf DoschkeSoftware Developer

Commented:
If you're coming from ASP and using the vfp database through a ODBC or OLEDB connection you can set the DELETED setting at connecting and should set it to ON, otherwise you see and select deleted data.

See http://connectionstrings.com/visual-foxpro you need DELETED=YES in a connection or you set it by sending a non sql command query to foxpros ODBC or OLE DB driver, which is SET DELETED ON.

PACKING is only a valid solution, if you have exclusive access to the table, which normally is not the case in an online application which is always online. You need a maintainance window to PACK dbfs, as this actually means a rewrite of the dbf file without all the deleted rows.

In short DELETED=YES in the conncetion or SET DELETED ON as a nonsql query will turn off deleted records from further queries on this table.

DBFs are implemented that way to prevent file rewrite operations in case of deletions, as they are very costy. The default is DELETED = NO /OFF unfortunately, wich also has performance advantages.

Bye, Olaf.
GreatSolutions

ZAP will delete all records Those marked for deletion and those that are not marked for deletions too
Olaf DoschkeSoftware Developer

Commented:
As you say you browse the DBF file. Do you do that in VFP? Then SET DELETED ON before, too. And you'll not see delted records in the browse window. If you SET DELETED OFF you'll see them, but there is a deletion mark, a slim column with deleted records having a black mark in there, undeleted records a white mark.

If you deleted recors from ASP eg from a recordset, you may not have written that back to the DBF backend. Seeing how you do things in your ASP code might help us find out what's wrong.

Bye, Olaf.

Commented:
@tushar
He says in his original question he wants to delete *all rows* from the test.dbf file :-)
@GreatSolutions

Ooops yes. You are right. Though ZAP will require exclusive use and the REINDEX should not be necessary.

Commented:
About REINDEX, i'm used to do it after ZAP, since once, 15 years ago, i noticed the size of some index was still huge after ZAP, so got used to do REINDEX since then :-)

Author

Commented:
Excellent .

It solved my problem.


Thanks
Olaf DoschkeSoftware Developer

Commented:
ASPDEV, if you really did ZAP, you lost all data of that table.

Bye, Olaf.