Making a database.. Another Question.. 
Author Message
 Making a database.. Another Question..

Hello All,

    Thank you all for your help with my previous question.. I've got another
quick one... In all my days (trying to) program databases, I've removed
records form RANDOM access files this way..

find the record of the file I wish to remove(delete)
open two files the original and a temp file
read through, writing all records that dot match the one I wish to remove
into the temporary file, then on completion delete the original and rename
the temp with the original name.

that seems so slow to me.. is there a better way?

Thank you!

Stewart Brown



Fri, 08 Dec 2000 03:00:00 GMT  
 Making a database.. Another Question..

Quote:

>    Thank you all for your help with my previous question.. I've got another
>quick one... In all my days (trying to) program databases, I've removed
>records form RANDOM access files this way..

>find the record of the file I wish to remove(delete)
>open two files the original and a temp file
>read through, writing all records that dot match the one I wish to remove
>into the temporary file, then on completion delete the original and rename
>the temp with the original name.

>that seems so slow to me.. is there a better way?

Stewart, this is a fairly straightforward process.  You first use the TYPE
structure to define a 'record' layout which contains all the fields you want
to store.  The 'TYPE/END TYPE' structure is a descriptive 'template', it
does not actually create your record.  Then you use the DIM statement to
create the actual record and give it a name.  Here's an example:

TYPE RecType
   Field1 AS INTEGER       '  2 bytes
   Field2 AS SINGLE        '  4 bytes
   Field3 AS DOUBLE        '  8 bytes
   Field4 AS STRING * 10   ' 10 bytes
END TYPE                  ' 24 bytes total record size

DIM Rec AS RecType, TempRec AS RecType

This creates two records, one named Rec, the other named TempRec.  I
called the fields Field1, Field2, etc., but you can name them anything
you like.  To reference the fields use the format: recordname.fieldname
like this:

Rec.Field1 = 4
PRINT TempRec.Field3

Now, to store these record structures on disk, you open a disk file for
RANDOM I/O with a fixed record length and use the GET #/PUT # commands
to read and write records.  Assuming a file name of 'myfile.dat', and
your record size is 120 bytes, and you want to open the file as #1,
open the file with this format:

OPEN "myfile.dat" FOR RANDOM AS #1 LEN=120

You write records to the disk file using record numbers, starting with 1.
You can, but generally should not, leave gaps when you write records.
To write structure Rec to the above file as record 3 do this:

PUT #1, 3, Rec

To read the same record back into structure Rec do this:

GET #1, 3, Rec

If all you need is to add all the records to this file at one time,
then later read them any number of times, this is sufficient.
However, in real applications you want to add records again and
again, as well as modify and delete them.  To add records later,
you need to know where the end of the file is, and if you want to
delete records, you need to be able to keep up with deleted records.
The following explains how to create a simple file structure which
allows you to add, modify and delete records, and how to accomplish
that in such a structure.  This method keeps up with the deleted
records in a simple, fundamental structure called a 'linked list'.

You will be using a fixed length random file as described above, but
the file will contain three types of records.  Record number 1 is to
be set aside as a 'control record'.  The rest of the file will
contain the 'data records' you add, and 'deleted records'.  Each of
these records has a specific format, but all must be the same length,
the length of the largest record.  It is usually the data records
which are largest and define the size of the record.  But
occasionally you have only a small amount of data in each data
record, and the control record is the largest.  You might set up your
records something like this example of a name and address database:

TYPE ControlType  ' 91 characters
   HighestRecord AS INTEGER    ' Number of highest record written
   LastDeleted AS INTEGER      ' Number of last deleted record
   RecordCount AS INTEGER      ' Number of good records in file
   FILLER AS STRING * 85       ' Fill out record to 91 bytes
END TYPE

TYPE RecType      ' 91 characters
   DeletedFlag AS STRING * 1   ' Good record = space, deleted = "D"
   Name AS STRING * 30
   Street AS STRING * 30
   CityStZip AS STRING * 30
END TYPE

TYPE DeletedType  ' 91 characters
   DeletedFlag AS STRING * 1   ' Deleted record = "D"
   NextDeleted AS INTEGER      ' Number of next deleted record in list
   FILLER AS STRING * 85       ' Fill out record to 91 bytes
END TYPE

DIM CRec AS ControlType, Rec AS RecType, DRec AS DeletedType

Assuming a file name of 'address.dat', and the record size is 91 bytes,
and you want to open the file as #1, open the file with this format:

OPEN "address.dat" FOR RANDOM AS #1 LEN=91

Here is the logic to create the file, and to add, modify and delete
records.  The fields like 'HighestRecord' and 'DeletedFlag' below are
actually those in the records.  I leave out the record names for
clarity.  'SaveNextDeleted' is a separate field used for a temporary
hold area.

To initialize the file:

Open file as above
HighestRecord = 1
LastDeleted = 0
RecordCount = 0
Write control record at 1  (e.g. PUT #1, 1, CREC)
Close file

To begin processing:

Open the file as above
Read control record at 1   (e.g. GET #1, 1, CRec)

To add a record:

If LastDeleted = 0
   Set up new data record (populate the fields in Rec)
   DeletedFlag = " "
   HighestRecord = HighestRecord + 1
   Write data record at CRec.HighestRecord
else
   Read data record at LastDeleted
   SaveNextDeleted = NextDeleted  (SaveNextDeleted is a temp hold area)
   Set up new data record (populate the fields in Rec)
   DeletedFlag = " "
   Write data record at LastDeleted
   LastDeleted = SaveNextDeleted
end if
RecordCount = RecordCount + 1

To delete the record at DeletePos:

Read data record at DeletePos
DeletedFlag = "D"
NextDeleted = LastDeleted
Write data record at DeletePos
LastDeleted = DeletePos
RecordCount = RecordCount - 1

When you complete processing:

Write control record at 1
Close the file

To read all the good records, scan through the file, records 2 through
HighestRecord, selecting all records with DeletedFlag = " ".  When records
are added, they first reclaim deleted space, then continue appending to the
end of the file.  The total record count is always available in the control
record in field RecordCount.  To find the number of deleted records:

DeletedRecords = HighestRecord - RecordCount - 1.

That's it.
--
Judson McClendon          This is a faithful saying and worthy of all
Sun Valley Systems        acceptance, that Christ Jesus came into the

(please remove numbers from email id to respond)



Fri, 08 Dec 2000 03:00:00 GMT  
 Making a database.. Another Question..

Quote:

> Thank you all for your help with my previous question.. I've got another
> quick one... In all my days (trying to) program databases, I've removed
> records form RANDOM access files this way..

> find the record of the file I wish to remove(delete)
> open two files the original and a temp file
> read through, writing all records that dot match the one I wish to remove
> into the temporary file, then on completion delete the original and rename
> the temp with the original name.

> that seems so slow to me.. is there a better way?

One possiblity would be to have a Deleted field in each record. When you wish to delete the record,
set the field to some value. You could then 'pack' the database daily or monthly using the method you
described above. You'll have to ensure that any programs that use the file skip the 'deleted' records.

At least, this was the method I used when I programmed in this type of BASIC back in the very early '80s. :)

Which leads me to ask a question that I've wondered about for all these years:

Why didn't Microsoft ever add better file handling to BASIC ?

Hope this helps !

Rob Leighton
Spirit River Software Company Ltd.



Fri, 08 Dec 2000 03:00:00 GMT  
 Making a database.. Another Question..

Quote:

> Why didn't Microsoft ever add better file handling to BASIC ?

It hurts to say so, but they actually tried to. PDS 7.x came with
"built-in" ISAM database handling and now you have MS Access with BASIC
inside to program it and of course VisualBasic that can use MS Access
tables.

All these attempts resulted in all these database related, visual
related (and to  me annoying off subject) messages in this group

Marnix.



Tue, 12 Dec 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. database properties and making databases replicable

2. Making a database based on SQL search in another database

3. Making the access database case sensitive

4. Making new Database

5. Making a function available from any database

6. Making Access Database Print Letters with Dates in Spanish

7. Making an apllication for an ACCESS97 Database.

8. HELP: Printing error makes database tables dissapear

9. Passwording a Access7 Database made simple

10. Making UNBOUND objects react to database

11. Error message in database made with Visual Basic

12. How to detect changes made to database?

 

 
Powered by phpBB® Forum Software