Clearing test data from a database (.dbf)

[jkwrpc]

Is there an A5 command that will empty all the tables in the database. I need to get rid of my test data. I could zap each table in a script but if there a better method it would be worth knowing. I could not find anything in the documentation or on the message board, but I was probably using wrong search terms. My database is the native .dbf

[DaveM]

Re: Clearing test data from a database (.dbf)

Not that I know of.

Alternative: when you design tables for production, make a copy of them and when you are done all else, just copy the dbf and fpt back to the production and compact.

[Raymond Lyons]

Re: Clearing test data from a database (.dbf)

John,

I can't say I understand Dave's suggestion, but if it doesn't help, add a script with the code below in it, make a copy of the entire database (i.e., make sure you have a backup), then from the Control Panel code section, run the script, selecting all or a subset of the tables you want emptied. Note: I am not 100% sure the code to compact the DB is necessary but I think it is. Unless I am missing something, this should leave your database intact but empty of all data. Notice that you don't have to empty all tables if a few of them contain generic data you need to keep. The size of my dialog is fairly large (80,25)--you could adjust that if needed.

Raymond Lyons

EDIT: the code below has been replaced with better code in a later post below.

table_names = A5_TABLE_ENUM_ACTUAL(.f.)
DIM Shared  v_tbl_choice as c 
DIM choice_list as c 
choice_list = table_names
Dim headingText as c 
headingText = <<%txt%
%txt%
dim defaultValue as c 
defaultValue = ""
v_tbl_choice = ui_get_check2("Select Tables",defaultValue,choice_list,headingText,.t.,.t.,.t.,80,25,.t.,"Horizontal",.f.)
for each foo in v_tbl_Choice
	tbl = table.open(A_DB_CURRENT_PATH+foo.value,FILE_RW_EXCLUSIVE)
	TBL.ZAP(.T.)
	TBL.CLOSE()
next

a5_databasecompact(.F.)

[jkwrpc]

Re: Clearing test data from a database (.dbf)

Thanks Raymond I suspected I would have to write a script and now that I can make use of your sample it just got a whole lot easier.

[Raymond Lyons]

Re: Clearing test data from a database (.dbf)

John (and others),

The code I gave previously will work but a much better (and clearer) version appears below.

dim response as n
response=ui_msg_box("Warning","To empty tables they must be closed. If a form or browse based on a table (or set containing that table) is open, you must close it before proceeding."+crlf(2)+"Have you closed all relevant forms and browses?",UI_QUESTION_SYMBOL+UI_YES_NO)
if response = 7	'no
	end 
end if
dim table_names as c
table_names = A5_TABLE_ENUM_ACTUAL(.f.)
'now alphabetize the table names
table_names = sortsubstr(table_names, crlf())
Dim headingText as c 
headingText = <<%txt%
If a table being open gives an error, that table and all those after it will not be emptied.
%txt%
dim defaultValue as c 
defaultValue = ""
DIM tables_to_delete as p		'returns .LastButton and .Selected
tables_to_delete=ui_get_check2p("Tables to Empty-Zap", defaultValue, table_names, HeadingText, .t., .t., .f., 80, 25)

If tables_to_delete.LastButton="".or.tables_to_delete.LastButton="Cancel"
	msgbox("Operation Status","Canceled")
	end 
end if

for each foo in tables_to_delete.selected
	tbl = table.open(A_DB_CURRENT_PATH+foo.value,FILE_RW_EXCLUSIVE)
	TBL.ZAP(.T.)
	TBL.PACK()
	TBL.CLOSE()
next

EDIT: What I previously said about for documentation for ui_get_check2() indicating that one should be able to check on which button of the dialog was last pushed was incorrect. I was looking at ui_get_check2P, and in the code above I have now used ui_get_check2P to check on the last button pushed. Sorry for the confusion. I'm not sure why the non-Pointer one even still exists except maybe for legacy reasons.

2nd Edit: Changed inaccurate "Delete" to "Empty-Zap" in code. One could probably use similar code for a multi-table delete routine, but that's not how I came up with the code.

Raymond Lyons

[jkwrpc]

Re: Clearing test data from a database (.dbf)

Raymond - thanks again

[Raymond Lyons]

Re: Clearing test data from a database (.dbf)

John (and others),

The code in Post 5 above has been edited to be a version that checks for the last button pushed to be "" or "Cancel".

Raymond Lyons

[jkwrpc]

Re: Clearing test data from a database (.dbf)

Raymond

I will put it to work tomorrow. :)

[jkwrpc]

Re: Clearing test data from a database (.dbf)

Raymond - Just finished using the script - it works like a dream and I like the U/I. Thanks so much, for anyone wanting to clear test data this code is worth saving!!!!