by Tom Cone
| Tom shows us how he copies selected records to diskette, first making sure there's enough room on the diskette to store the table. |
Have you ever wanted to copy records to a new table on a diskette? If so, you may have wondered if there were some way to determine in advance whether the new table will fit within the space remaining on the diskette. Well, there is. It requires two relatively simple calculations:
By comparing these two numbers you can tell whether the new table will fit on the diskette.
The structure of a DBF file is composed of two distinct parts, a header, and the data themselves. We'll come back to the header in a minute. Let's examine the storage requirements for the data first:
Alpha Five uses fixed length records in its database tables. The basic table format follows the old dBaseIII+ and FoxPro 2.6 formats. Your DBF file structure is the same with Alpha Five as it was with Alpha Four. Each record in the table is the same width, determined by the number and width of the fields in your design. In a simple case, with a table structure like this:
Firstname C 20 Lastname C 20 Company C 32 Phone C 20
...each record would be 92 characters wide. (92 is the sum of the widths of the four fields) Each character requires one byte of file storage, so it's easy to see that each record in this table would require 92 bytes of file space. However, each record also has an associated invisible field (sometimes called the "zeroth" field) which is one character wide. This invisible field is used to mark records for deletion. This zeroth field requires one additional byte of file storage space for each record in the table. So, using our example if the copy operation involves 100 records we know now that the data will require (92 + 1)bytes per record * (100) records per file = 9,300 bytes per file.
Now let's return to the header portion of the DBF table structure. Alpha Five stores information about the table in a series of bytes at the beginning or top of each DBF file. You never see this information when you work with the table, but it includes a variety of information Alpha Five needs to work with the table, including the field names and the characteristics of each field in the table. The header's overall length varies from table to table, depending on the number of fields in each DBF table. Tables with lots of fields have a longer header than tables with fewer fields. It turns out there is a simple formula which can be used to determine the length of any DBF header:
Header length in bytes = (Number of Fields * 32) + 34Using our example, with four fields the space required for the header portion of the DBF table will be (4 * 32) + 34 or a total header length of 162 bytes.
If our table will contain 100 records we will need 9,300 bytes of space for the data and 162 bytes of space for the header. Our total file size will be 9,462 bytes.
Alpha Five's diskspace() function will return the number of
free bytes available on the disk where the current table is
stored. We can't use it here since our current table is stored
on the hard disk and we want to know free space on the diskette.
Is there a solution?
Yes.
The Filefind.first() function can be used to find all the files on
the diskette. As each one is found its size can be determined
using the <file>.bytes_get() function. All that is necessary is
to keep a running total as your script works its way through all
the files on the diskette, like this:
old_directory = dir_get() 'save location of current working directory
dir_put("A:") 'make floppy the current working directory
attributes = file_find_normal + file_find_readonly + file_find_hidden\
+file_find_system + file_find_label + file_find_directory\
+file_find_archive 'this is 63 for all file types
files = filefind.first("*.*",attributes) 'check all file types
totalbytesused = 0
while .not. files.eof()
currentfilename = files.name()
file_pointer = file.open(currentfilename,FILE_RO_SHARED)
totalbytesused = totalbytesused + file_pointer.bytes_get()
file_pointer.close()
files.next()
end while
dir_put(old_directory) 'restore original working directory
When this script has run the variable 'totalbytesused' contains the sum total of all the file sizes on the diskette. To find out how much storage remains free you subtract this figure from the size of an empty diskette. On my system an empty 1.44 mb diskette can hold 1,457,664 bytes. Using this figure the free space on a diskette is calculated by subtracting 'totalbytesused' from 1,457,664.
The following script will copy 10 fields from the Invoice Sample Customer table to a new file called "WPDATA.DBF" on diskette. It is intended to be placed in the OnPush Event of a button on the Customer_Information form that shipped with the Sample Invoice Database. The records that will be copied will correspond to the current "view" of the Customer Table when the button is pushed. If you use QuickFilter or a Query to narrow the current "view" only records matching that criteria will be copied to the diskette.
''XBasic
' by Tom Cone, 10-5-99
'Creates Word Processing Merge Data File on Diskette (a DBF file)
'This script must be run from a button on the Customer_Information
' form. It uses the Customer Table.
'This script will create a Word Processing Data File
' It is intended that the resulting Data File be used in
' word processing merge functions in WordPerfect or Microsoft Word.
' The resulting Data File is a FoxPro v 2.6 DBF file, just like the
' native Alpha Five and Alpha Four tables.
'This script will be run from the Customer_Information form, so the
'Customer Table is open at outset.
'This also means that this script should not close the current table
'when it stops.
'
'This script will create the Data File using the current view or
'query applicable to the Customer table when this script is invoked.
'All output is to Floppy Diskette, Drive A or B.
dim finishok as L
'flag for whether copy ended normally
finishok = .f.
old_directory = dir_get()
'preserve current working directory, will restore at end of script
filename = "WPDATA"
'will over-write files on diskette with same name
code = ui_yes_no + ui_attention_symbol
response =ui_msg_box("Merge Data File",\
"The program is preparing to copy names\
and billing addresses from the current view\
of the Customer Table to a diskette.\
They will be stored in a file called\
'WPDATA.DBF'. If the diskette has a\
file with that name on it already that\
file will be erased and over-written by \
the new one created now. Is it ok to proceed?",\
code)
if response = ui_no_selected then
end
end if
tbl = table.current()
'script must be called from Customer_invoice form
idx = tbl.index_primary_get()
'get pointer to current index or query list
datasetcount=idx.records_get()
'get number of records in current view
'determine file storage space beforehand:
' for 10 fields, all char type
' with combined field widths of 242 characters
datasize = 243 * datasetcount
'243 is combined field width, plus 1 byte for zeroth field
dataheadersize = 10 * 32
'32 bytes per field times number of fields in the new table
dataheadersize = dataheadersize + 34
'standard adjustment for each DBF table
'now combine them
datasize = datasize + dataheadersize
'code = ui_yes_no + ui_attention_symbol
response =ui_msg_box("Records in current view",\
"There are "+alltrim(str(datasetcount))+\
" names in the current view of the Customer Table.\
Are you sure you want to copy this many to the WPDATA file?",code)
if response = ui_no_selected then
end
end if
'which destination? Drive A: or B: ?
diskette = ui_get_radio("Specify Diskette Drive.",1,"A:","B:")
if diskette = "" 'user cancelled
dir_put(old_directory)
end
else
filename = diskette + chr(92)+filename
'default name is A:\WPDATA.DBF
code = ui_ok_cancel + ui_attention_symbol
oktocopy = ui_msg_box("Prepare Diskette",\
"Insert a blank diskette. Press OK to copy the data to\
the diskette.",code)
if oktocopy = ui_cancel_selected then
dir_put(old_directory)
end
else
'user wants to copy, so check to see if there's room
hourglass_cursor(.t.)
statusbar.clear()
statusbar.set_text("Check diskette for available space.")
ON ERROR GOTO diskette_error
dir_put(diskette)
files = filefind.first("*.*",63)
'check all file types
totalbytesused = 0
while .not. files.eof()
'total the number of bytes in files on diskette
currentfilename = files.name()
file_pointer = file.open(currentfilename,FILE_RO_SHARED)
totalbytesused = totalbytesused + file_pointer.bytes_get()
file_pointer.close()
files.next()
end while
ON ERROR GOTO 0
EmptyDiskAvailableBytes = 1457664
'available bytes on empty diskette on my system
availablespace = EmptyDiskAvailableBytes - totalbytesused
if datasize > availablespace then
'not enough room avail on diskette
statusbar.clear()
hourglass_cursor(.f.)
code = ui_attention_symbol
ui_msg_box("Sorry",\
"The names and addresses in the current view of the\
Customer Table will not fit in the available space on \
your floppy diskette.",code)
dir_put(old_directory)
'restore orig. working directory before end
end
end if
dir_put(old_directory)
'restore orig. working directory before copy begins
statusbar.clear()
hourglass_cursor(.f.)
end if
end if
'script gets here only if there is room available for
' the copy to be made on diskette
'Remember: source table was opened when form initiated,
'view set by user already
'create destination table
table.create_begin("Firstname","c",20)
table.field_add("Lastname","c",20)
table.field_add("Company","c",32)
table.field_add("Phone","C",20)
table.field_add("Fax","c",20)
table.field_add("BillAddr1","c",40)
table.field_add("BillAddr2","c",40)
table.field_add("BillCity","c",20)
table.field_add("BillState","c",20)
table.field_add("BillZip","c",10)
new_tbl = table.create_end(filename)
'Notice: fieldnames in target result table all have
'10 or fewer characters.
' This is necessary to prevent creation of supporting
' dictionary files (*.ddd, *.ddm, and *.ddx). Long
' fieldnames are stored in the dictionary which will be
' created on floppy diskette if needed... so do not use long
' fieldnames if you wish to suppress creation of the
' supporting dictionary files.
on error goto tablecopyerror
statusbar.clear()
statusbar.set_text("Copying records in progress, standby.")
hourglass_cursor(.t.)
tbl.fetch_first() 'customer table
new_tbl.batch_begin()
while .not. tbl.fetch_eof()
new_tbl.enter_begin()
'result table on diskette
new_tbl.firstname = tbl.firstname
new_tbl.lastname = tbl.lastname
new_tbl.company = tbl.company
new_tbl.phone = tbl.phone
new_tbl.fax = tbl.fax
new_tbl.billaddr1 = tbl.bill_address_1
new_tbl.billaddr2 = tbl.bill_address_2
new_tbl.billcity = tbl.bill_city
new_tbl.billstate = tbl.bill_state_region
new_tbl.billzip = tbl.bill_postal_code
new_tbl.enter_end(.t.)
tbl.fetch_next()
'get next record in source table
end while
new_tbl.batch_end()
new_tbl.close()
'close the target result table, but
'leave tbl open, cause form needs it
on error goto 0
finishok = .t.
endofscript:
filename = ""
statusbar.clear()
hourglass_cursor(.f.)
dir_put(old_directory)
'restore original working directory
if finishok then
ui_msg_box("Processing Finished",\
"The WPDATA file has been created.")
end if
end
tablecopyerror:
ui_msg_box("Error Message",\
"The program was not able to finish copying records into the \
WPDATA file. ")
dir_put(old_directory)
filename = ""
new_tbl.close()
end
diskette_error:
statusbar.clear()
dir_put(old_directory)
ui_msg_box("Sorry",\
"An error occurred accessing your diskette. Try again.")
end
| Questions? You can write to Tom at tcone@ix.netcom.com |
10/5/99
Don't forget, we need your feedback to make this site better!