How I Pack Table in multiuser (buffermode override 5 mode) 1

ERRY · Feb 8, 2000

When I in multiuser platform (buffermode override 5), I want to pack the table. But I always have a problem

martinr · Feb 11, 2000

I am not sure what 'buffermode override 5' is, & what your problem is, but before you pack the table you have to use it exclusively (use xyz excl) and it shouldnt be open by any other user.

dgrewe · Feb 14, 2000

Like Martin Said You can not pack a multiuser database when it is opened by oher people. My favorite trick is to run the application with SET DELETED ON then at night run a reindex routine that removes deleted records. I have a tables of tables to be reindexed at night and a routine that goes into a endless loop until the reindex is complete. 
 
Following is the code and the table structure. 
 
 
 
*/*************************************************************************** 
*/Program : Sysindex 
*/System : Fox Library 
*/Purpose : Rebuild cdx files 
*/Syntax : do sysindex 
*/Returns : nothing 
*/Parameter : nothing 
*/Defaults : none 
*/Requires : nothing 
*/Changes : nothing 
*/Calls : nothing 
*/Version : 1.0 
*/Dated : 28 July 1997 
*/Written By: David W. Grewe 
*/*************************************************************************** 
*& File Handling-Index 
*/*************************************************************************** 
*/ 
*/*************************************************************************** 
* 
* Set enviroment 
* 
close data 
set exclusive on 
set deleted off 
set safety off 
set deleted off 
set talk off 
*set step on 
* 
* open the sysindex in directory you started up in BEFORE going to C: drive 
* 
? "Reseting Sysindex File" 
L_ERROR=0 
on error do TRAPERR 
use SYSINDEX alias SYSINDEX exclu 
if L_ERROR=0 
pack 
replace all completed with {} 
replace all avail with .t. 
endif 
use SYSINDEX alias SYSINDEX shared 
set filter to empty(completed) 
* 
* set default to c: drive there is a reason for this see diskspace() command 
* 
L_DEFAULTDIR = sys(5) + sys(2003) 
set default to c:\ 
* 
* program memvars 
* 
C_CDXPRG = "C:\CDXPRG.PRG" 
C_CDXFXP = "C:\CDXPRG.FXP" 
L_ERROR = 0 
* 
* do forever until all records have a date in sysindex.completed field 
* 
do while .t. 
select SYSINDEX 
goto top 
do while !eof() 
* 
* tell user what is up and loop 
* 
L_PERCENT = str(((recno() / reccount()) * 100) , 6 , 2) 
L_MSG = "Percent Complete=" + alltrim(L_PERCENT) 
wait L_MSG window nowait 
clear 
* 
if SYSINDEX.NAME = "SYSINDEX" 
replace SYSINDEX.AVAIL with .T. 
replace SYSINDEX.COMPLETED with date() 
skip 
loop 
endif 
* 
if SYSINDEX.AVAIL = .f. 
skip 
loop 
endif 
* 
if !empty(SYSINDEX.COMPLETED) 
skip 
loop 
endif 
* 
* define loop memvars 
* 
replace SYSINDEX.AVAIL with .F. 
? "Working On " + alltrim(SYSINDEX.name) 
L_ERROR = 0 
L_TGTDBF = alltrim(SYSINDEX.WORKAREA) + alltrim(SYSINDEX.NAME) + ".DBF" 
L_TGTCDX = alltrim(SYSINDEX.WORKAREA) + alltrim(SYSINDEX.name) + ".CDX" 
L_TGTFPT = alltrim(SYSINDEX.WORKAREA) + alltrim(SYSINDEX.name) + ".FPT" 
L_SRCDBF = alltrim(SYSINDEX.DRIVE) + alltrim(SYSINDEX.directory) + alltrim(SYSINDEX.name) + ".DBF" 
L_SRCCDX = alltrim(SYSINDEX.DRIVE) + alltrim(SYSINDEX.directory) + alltrim(SYSINDEX.name) + ".CDX" 
L_SRCFPT = alltrim(SYSINDEX.DRIVE) + alltrim(SYSINDEX.directory) + alltrim(SYSINDEX.name) + ".FPT" 
* 
* set up the error trap 
* 
select 2 
on error do TRAPERR 
* 
* Will the file fit on the hard drive 
* 
L_handle = fopen(L_SRCDBF) 
L_Size = fseek(L_handle,0,2) 
= fclose(L_handle) 
* 
if file(L_SRCCDX) 
L_handle = fopen(L_SRCCDX) 
L_Size = L_Size + fseek(L_handle,0,2) 
= fclose(L_handle) 
endif 
if file(L_SRCFPT) 
L_handle = fopen(L_SRCFPT) 
L_Size = L_Size + fseek(L_handle,0,2) 
= fclose(L_handle) 
endif 
* 
if L_Size > diskspace() 
* 
* file will not fit 
* 
? "File Will Not File On Drive C:, Start the program on a computer With More Drive Space" 
use 
select SYSINDEX 
replace SYSINDEX.avail with .T. 
skip 
= inkey(2) 
loop 
endif 
* 
* file fits 
* 
use (L_SRCDBF) exclu 
on error 
* 
* if file is in use skip and got to next database 
* 
if L_ERROR = 1 
? "Can't Open File, Either It's: 1. Open, 2. Location Problem, 3. Corrupt" 
select SYSINDEX 
replace SYSINDEX.AVAIL with .t. 
skip 
= inkey(2) 
loop 
endif 
* 
* change enviroment 
* 
if SYSINDEX.pack = .T. 
set deleted on 
else 
set deleted off 
endif 
* 
* if there are no indexes on the file 
* 
if empty(SYSINDEX.CDXPRG) 
if SYSINDEX.pack 
? "No Index on File, Removing Deleted Records" 
pack 
else 
? "No Index on File, Pack Not Requested." 
endif 
use 
select SYSINDEX 
replace SYSINDEX.completed with date() 
skip 
? "Action Completed" 
? "" 
loop 
endif 
* 
if !empty(tag(1)) 
set order to (tag(1)) 
endif 
* 
* No one has the file open, copy to C drive to work on 
* 
? "Making A Backup Copy In Case Something Goes Wrong" 
set talk on 
copy to (L_TGTDBF) 
* 
select 3 
use (L_TGTDBF) exclu 
* 
* create a index file and reindex to file 
* 
? "Starting Reindex On Backup Copy" 
copy memo SYSINDEX.CDXPRG to (C_CDXPRG) 
set talk off 
comp (C_CDXPRG) 
wait clear 
set talk on 
do &C_CDXPRG 
set talk off 
select 3 
use 
* 
* move files back to their orginal location 
* 
? "Replacing Orginal File With Reindexed Backup Copy" 
select 2 
use 
delete file (L_SRCDBF) 
delete file (L_SRCCDX) 
delete file (L_SRCFPT) 
* 
set talk on 
if file(L_TGTFPT) 
copy file (L_TGTFPT) to (L_SRCFPT) 
endif 
if file(L_TGTCDX) 
copy file (L_TGTCDX) to (L_SRCCDX) 
endif 
copy file (L_TGTDBF) to (L_SRCDBF) 
set talk off 
if SYSINDEX.CLEANUP = .T. 
delete file (L_TGTDBF) 
delete file (L_TGTCDX) 
delete file (L_TGTFPT) 
endif 
delete file (C_CDXPRG) 
delete file (C_CDXFXP) 
* 
* update database 
* 
select SYSINDEX 
replace completed with date() 
skip 
 
enddo && (! eof sysindex) 
* && Reset enviroment 
wait clear 
set deleted on 
goto top 
if eof() 
exit 
endif 
wait window "Waiting 5 seconds for next loop"+chr(13) + "Ctrl+End to stop Loop" timeout 5 
if lastkey() = 23 
exit 
endif 
* 
enddo 
* 
set default to (L_DEFAULTDIR) 
clear 
return 
* 
* 
* 
procedure TRAPERR 
****************** 
L_ERROR = 1 
= inkey(1.0) 
return 
 
 
 
 
Structure for table: D:\BIDSYS\DBF\SYSINDEX.DBF 
Number of data records: 96 
Date of last update: 02/11/00 
Memo file block size: 64 
Code Page: 1252 
Field Field Name Type Width Dec 
1 DRIVE Character 2 
2 DIRECTORY Character 25 
3 NAME Character 10 
4 WORKAREA Character 25 
5 PACK Logical 1 
6 CLEANUP Logical 1 
7 AVAIL Logical 1 
8 COMPLETED Date 8 
9 CDXPRG Memo 4 
10 MAINTPRG Memo 4

Orion7 · Feb 15, 2000

Sometimes it is necessary to do a little system maintenance. Sometimes the middle of the night just doesnt cut it. If you want to do and index and pack in your code, you should verify that you can use the tables exclusivly. 
Here is an example of such code. 
 
* Create an array of all tables in the database and scan them 
FOR i = 1 TO 3 
IF !EMPTY(latables) 
ls_retry = "RETRY" 
ls_table = latables 
IF !lb_quit_program 
DO WHILE ls_retry = "RETRY" 
* If the file exists, check it by opening it 
IF FILE("data\"+ls_table+".dbf&quot

* Open the next table 
IF used(ls_table) 
USE IN (ls_table) 
ENDIF 
USE (ls_table) IN 0 EXCL && set_buf_optimistic takes care of this 
IF !CURSORSETPROP("BUFFERING",1,ls_table) 
lb_quit_program = .T. 
EXIT 
ENDIF 
IF USED(ls_table) 
* If the table is used, it is ok. Close it and try the next table. 
USE IN (ls_table) && new ah 9/23 
USE 
ls_retry = "OK" 
ELSE 
* If the table is not used, it had a problem. If retry is false, do not continue. 
IF ls_retry = "QUIT" 
lb_quit_program = .T. 
ENDIF 
ENDIF 
ELSE 
* If the file does not exist, an update is needed 
WAIT window "no file found" 
gb_update_needed = .T. 
ls_retry = "OK" 
ENDIF 
ENDDO 
ON ERROR 
IF lb_quit_program 
EXIT 
ENDIF 
ENDIF 
ENDIF 
ENDFOR 
 
SET EXCLUSIVE OFF 
IF lb_quit_program 
=messagebox("Unable to gain sole access to tables, process aborted.",48+0,"exclusive access denied&quot

ENDIF 
RETURN !lb_quit_program 
ENDFUNC

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How I Pack Table in multiuser (buffermode override 5 mode) 1

ERRY

Programmer

martinr

Programmer

dgrewe

MIS

Orion7

Programmer

Similar threads

Part and Inventory Search

Sponsor