FAQ035 - Where Can I Find Information About the FIELD Statement and Random Access Files?
Answer provided by MystikShadows:
Random Access Files And The FIELD Statement
INTRODUCTION:
The very first type of field manipulation to ever be available with random access files were always done with the field statement. Hence it's very possible that a lot of code you'll find on the internet that work with random access files will use the FIELD statement. It wasn't until the advent of the first BASICs to support User Defined Types that another method using TYPE/END TYPE constructs was devised and made available to us. And more recent random access file examples will typically use TYPE/END TYPE constructs instead. Some people prefer the way FIELD works over the User Defined Type approach which is worth noting.
In this document random access files will be explained as well as the FIELD statement by creating a complete working example. By the end of this example and the explanations the FIELD statement and how to work with it will hold no secrets to you. So let's get right down to business shall we?
RANDOM ACCESS FILES:
A random access file is a special kind of file that is meant to do one thing and one thing only. And that is to save and manage one particular set of information. Ultimately this could mean one bit of information but its main advantage comes from being able to manage more than one group of related information. For example:
A Contact List to store your contacts in. Each record in this case would have information like Name, Address, telephone number, email and other information that relates to a given contact. Same thing for a CD or movie collection, recipes, home inventory. In all these cases you're managing the a set of information that pertains to a given group of relevant information.
In order to be able to work with random access files you need to consider the following:
- Each record in a random access file have a fixed length and that length is the same fir each and every record that is in the data file.
- The order of the different fields in a given record as well as the length of each of these fields are fixed for each record.
- When creating the field set for a random access file, the total length of all the field definitions must be equal to the length specified when opening the data file (you'll see what I mean in the example in the next section below).
THE FIELD STATEMENT DEFINITION:
The best way to describe the FIELD statement is to state that a field is a section of a complete record in a random access file. it is the tool you use to break up a complete record into its different bits of information if you will. In order to get a complete record from the random access file you need to create field statements that will take up the total length of a defined record. As I mentioned earlier, all records have the same length in a random access file.
At the end of the day the combination of record length and field statements will work together to get a complete record from the file then the program can process these FIELDs into variable of the right types to work with them. Let's see how that works in a concrete example.
A WORKING FIELD EXAMPLE:
For this example we will use a random access file as the beginning of a home inventory system. We will need some specific structure to keep the information organized. Here's how the home inventory structure is going to be defined:
1. ItemNumber: Long time, basically a numeric value that will always increased by one every time we add an item to the file.
2. BrandName: The brand or manufacturer of the item in question.
3. ModelNumber: Many items have a model number attached, most washing machines, stereo systems, televisions, VCRs, DVD reader/writers hence the more expensive stuff in a typical house will have a model number.
4. SerialNumber: Each product has a unique serial number which makes it easier to track in case the item gets stolen.
5. Description: A descriptive text about the item, maybe its color, dimensions, weight, etc etc to help describe the item.
6. BoughtWhere: A string field indicating where the Item was purchased.
7. PurchaseDate: A date when the item was purchased.
8. PurchasePrice: The amount paid when purchased.
9. HasReceipt: A One character field that indicates if the invoice or receipt of the purchase is found.
This should be enough to get started, maybe more fields could be added to this for a complete system but for this example, this is plenty to work with. First well create a set of variables that we'll use to work with the data in the program:
DIM ItemNumber AS LONG
DIM ItemBrand AS STRING * 40
DIM ModelNumber AS STRING * 30
DIM SerialNumber AS STRING * 30
DIM Description AS STRING * 60
DIM BoughtWhere AS STRING * 60
DIM PurchaseDate AS STRING * 10
DIM PurchasePrice AS DOUBLE
DIM HasReceipt AS INTEGER
DIM CurrentRecord AS LONG
Now, we need to give these field a specified length so that we know how many characters each field will have. Let's do that right now. Below is such a list as well as the expected data type we'll be giving each field.
ItemNumber 4 LONG data type.
BrandName 40 STRING data type
ModelNumber 30 STRING Data type
SerialNumber 30 STRING Data type
Description 60 STRING Should be enough to tell what the item is
BoughtWhere 60 STRING Location and name of the store
PurchaseDate 10 STRING with MM-DD-YYYY format
PurchasePrice 8 DOUBLE data type
HasReceipt 2 INTEGER data type
--------------------------------------------------------
Total Bytes 244
We now have all we need to define our FIELD statements and such. We'll first want to open our random access file and then create the FIELD statement that will work as a buffer between the program and the data file. Here is how the starting code will look like:
DIM FileHandle AS INTEGER
' --------------------------------------
' We first open the random access file
' --------------------------------------
FileHandle = FREEFILE
OPEN "INVENT.DAT" FOR RANDOM AS FileHandle LEN=244 ' 244, total bytes of a record
' --------------------------------------------
' We then Assign the different Field Lengths
' --------------------------------------------
FIELD #FileHandle, 4 AS Number$, 40 As Brand$, 30 AS Model$, 30 AS Serial$, 60 AS Desc$, 60 AS Where$, 10 AS Purchase$, 8 AS Price$, 2 AS Receipt$
And there you have it, our structure is now completely defined and our random access file is ready for action. Before we go ahead and start saving data and such there's a few other things we need to make sure we know about and understand. Now if your database happens to be all string variables it is simpler to handle in the long run, but most more complex data structures would use different types of data as I am doing in this example. Which brings us to our next section.
USEFUL FUNCTIONS TO WORK WITH THE DATA:
QuickBASIC has a complete set of instructions designed to work with the FIELD statement buffers to convert to and from the fields into their physical data types. It also has a a set of functions to properly insert the data into a FIELD buffer in order for the data to be valid when saved and retrieved from the random access file.
Conversion Functions:
A FIELD definition indicates how many bytes of a record is to be considered as a FIELD. All FIELD variables are treated as STRING variables when performing save and load to and from the random access file. If the field is to be used as a numeric data type it first needs to be converted to its respected designated data type. QB offers the following four functions to do the conversions of the FIELD values to their respective numeric data types.
- CVI: This function takes a 2 byte string and converts it to a standard INTEGER value that follow the MSB and LSB standard which would be very different than just doing a VAL of the same 2 byte string.
- CVL: Works the same way as CVI only it works with a 4 byte string and converts that to a standard LONG data typed 4 byte value
- CVS: This kind of works like CVI only it takes a 4 bytes string and converts that to a SINGLE data type value respecting both MSB and LSB conversion values as well as the decimal related conversion.
- CVD: Works the same as CVS but does it with an 8 byte string which it converst to a standard DOUBLE data typed value.
Likewise, after you've created or inquired values for the fields, in an input screen routine for example, you need to turn these numeric values back to its rightful valid FIELD definition value when assigning the variables to the FIELD buffers so they can be saved as valid numeric values for the data type they represent. Here again QB has the following 4 functions to accommodate this functionality:
- MKI$: This one takes an integer value and transforms it back into a 2 byte string so that the FIELD buffer can be assigned and saved properly.
- MKL$: This is like MKI$ but converts a LONG data value into a 4 byte string to ready the value to be saved in the field buffer.
- MKS$: As you probably guess this is the counterpart to CVS defined above and takes a SINGLE data type value and converts it back to a 4 byte string to be saved.
- MKD$: Same as MKS$ but this one takes a DOUBLE data type value and converts it to an 8 byte string for storage.
By using the CV* functions and the MK*$ functions it becomes quite trivial to do the transfer between the FIELD statements and the variables used in the program as you'll see below.
Assignment Functions:
The final two functions to learn about are there to help determine how a value is stored inside a specific FIELD buffer. When you're assigning a converted value or a regular string to a FIELD Variable you can define the way the assignment is to happen
- LSET: This is the statement that left justifies a string smaller than the size of a FIELD string variable. Note that LSET can be use just to align a string contents into a standard other string variable as well it is not just a FIELD related statement.
- RSET: As you have probably guessed, RSET performs the same assignment as LSET only it right justifies the contents of a string into the destination STRING variable.
SPECIAL NOTES: If the string value to be assigned happens to be longer than the length of the FIELD variable both RSET and LSET will truncate the string from the RIGHT side. Hence in both cases, if you are putting a string value that has 20 characters into a 15 character long FIELD variable, both LSET and RSET will end up putting the first 15 characters of the string into the FIELD variable. The LSET and RSET use spaces to pad the string with the rightful length of field needed.
PUTTING THESE FUNCTIONS TO USE:
OK it's now time to get back to our programming example. When the FIELD statements are executed it puts the program into record fetching mode. Hence, once the FIELD statements are read they are set until you CLOSE the file or RESET it. This means that our FIELD definitions above are set and stay set until we're done with them. Let's continue by inserting some values into our variables and some records in our table.
' --------------------------------
' Start up value for the records
' --------------------------------
CurrentRecord = 1
' ----------------------------------------
' Assign The Program Variables
' Could be a set of INPUT statements too
' ----------------------------------------
ItemNumber = 1
ItemBrand = "IBM"
ModelNumber = "Thinkpad 340"
SerialNumber = "423456234-234234" ' fictitious of course 
Description = "Pentium 3 450mHz Laptop with 64 Megs Ram and 10 Gig HD"
BoughtWhere = "EBay"
PurchaseDate = "12-25-2007"
PurchasePrice = 75.29
HasReceipt = 1 ' 1 meaning yes in this case.
' ----------------------------------------------
' Assign these values to the FIELDed variables
' for this example only LSET will be used.
' ----------------------------------------------
LSET Number$ = MKL$(ItemNumber) ' MKL$ because ItemNumber is a LONG variable
LSET Brand$ = ItemBrand
LSET Model$ = ModelNumber
LSET Serial$ = SerialNumber
LSET Desc$ = Description
LSET Where$ = BoughtWhere
LSET Purchase$ = PurchaseDate
LSET Price$ = MKD$(PurchasePrice) ' MKD$ because PurchasePrice is a DOUBLE variable
LSET Receipt$ = MKI$(HasReceipt) ' MKI$ because HasReceipt is an INTEGER variable
' ----------------------------------------
' Now we can save the buffer to the file
' ----------------------------------------
PUT #FileHandle, CurrentRecord
CurrentRecord = CurrentRecord + 1
' ----------------------------------------
' Assign The Program Variables
' Could be a set of INPUT statements too
' ----------------------------------------
ItemNumber = 1
ItemBrand = "Toshiba"
ModelNumber = "CT-430CD"
SerialNumber = "34536435-64534" ' fictitious of course
Description = "Pentium 100mHz Laptop with 12 Megs Ram and 4 Gig HD"
BoughtWhere = "EBay"
PurchaseDate = "12-25-2007"
PurchasePrice = 75.29
HasReceipt = 1 ' 1 meaning yes in this case.
' ----------------------------------------------
' Assign these values to the FIELDed variables
' for this example only LSET will be used.
' ----------------------------------------------
LSET Number$ = MKL$(ItemNumber) ' MKL$ because ItemNumber is a LONG variable
LSET Brand$ = ItemBrand
LSET Model$ = ModelNumber
LSET Serial$ = SerialNumber
LSET Desc$ = Description
LSET Where$ = BoughtWhere
LSET Purchase$ = PurchaseDate
LSET Price$ = MKD$(PurchasePrice) ' MKD$ because PurchasePrice is a DOUBLE variable
LSET Receipt$ = MKI$(HasReceipt) ' MKI$ because HasReceipt is an INTEGER variable
' ----------------------------------------
' Now we can save the buffer to the file
' ----------------------------------------
PUT #FileHandle, CurrentRecord
At this point we have 2 records in our database file. Opening a random access file means that you can perform both writing and reading operations without having to close and open the file in the new mode. This is also why when you define your FIELD statements they stay set until you close the file. These FIELD variables is your window into the whole data file a window that as wide as the record length you specify in the LEN parameter of the OPEN statement.
Hence, once we have two records in the file, if we want to review these records to make sure they were saved properly, we can do so as simply as the following:
' -------------------------
' First we get the record
' -------------------------
GET #FileHandle, 1
' ---------------------------------------------------
' Next we transfer the record to the work variables
' ---------------------------------------------------
ItemNumber = CVL(Number$) ' CVL because ItemNumber is a LONG variable
ItemBrand = Brand$
ModelNumber = Model$
SerialNumber = Serial$
Description = Desc$
BoughtWhere = Where$
PurchaseDate = Purchase$
PurchasePrice = CVD(Price$) ' CVD because PurchasePrice is a DOUBLE variable
HasReceipt = CVI(Receipt$) ' CVI because HasReceipt is an INTEGER variable
' ----------------------------------------------------
' At this point we can print or work with the record
' ----------------------------------------------------
PRINT RTRIM$(ItemBrand) + " " + RTRIM$(ModelNumber) + " - "
PRINT RTRIM$(SerialNumber) + " -> " + Description
' -------------------------
' First we get the record
' -------------------------
GET #FileHandle, 2
' ---------------------------------------------------
' Next we transfer the record to the work variables
' ---------------------------------------------------
ItemNumber = CVL(Number$) ' CVL because ItemNumber is a LONG variable
ItemBrand = Brand$
ModelNumber = Model$
SerialNumber = Serial$
Description = Desc$
BoughtWhere = Where$
PurchaseDate = Purchase$
PurchasePrice = CVD(Price$) ' CVD because PurchasePrice is a DOUBLE variable
HasReceipt = CVI(Receipt$) ' CVI because HasReceipt is an INTEGER variable
' ----------------------------------------------------
' At this point we can print or work with the record
' ----------------------------------------------------
PRINT RTRIM$(ItemBrand) + " " + RTRIM$(ModelNumber) + " - "
PRINT RTRIM$(SerialNumber) + " -> " + Description
' ----------------------------------------
' And let's not forget to close the file
' ----------------------------------------
CLOSE #FileHandle
SOME SPECIAL NOTES:
Basically, this examples saved some records and then displayed them again. You can, of course, change existing records if you need to. For example, if you made a mistake entering the data you could just bring it up again, reassign the variables, change them as needed and put them back in the FIELD variable and save them overwriting the old contents in the data file. here's a small example of that that changes the values of the second record in the data file.
' -------------------------
' First we get the record
' -------------------------
GET #FileHandle, 2
' ---------------------------------------------------
' Next we transfer the record to the work variables
' ---------------------------------------------------
ItemNumber = CVL(Number$) ' CVL because ItemNumber is a LONG variable
ItemBrand = Brand$
ModelNumber = Model$
SerialNumber = Serial$
Description = Desc$
BoughtWhere = Where$
PurchaseDate = Purchase$
PurchasePrice = CVD(Price$) ' CVD because PurchasePrice is a DOUBLE variable
HasReceipt = CVI(Receipt$) ' CVI because HasReceipt is an INTEGER variable
' ---------------------------
' Change A few Field Values
' ---------------------------
ModelNumber = "TR-323-A5444"
PurchasePrice = 129.99
' -------------------------------------------
' Put these values into the FIELD variables
' -------------------------------------------
LSET Number$ = MKL$(ItemNumber) ' MKL$ because ItemNumber is a LONG variable
LSET Brand$ = ItemBrand
LSET Model$ = ModelNumber
LSET Serial$ = SerialNumber
LSET Desc$ = Description
LSET Where$ = BoughtWhere
LSET Purchase$ = PurchaseDate
LSET Price$ = MKD$(PurchasePrice) ' MKD$ because PurchasePrice is a DOUBLE variable
LSET Receipt$ = MKI$(HasReceipt) ' MKI$ because HasReceipt is an INTEGER variable
' --------------------------------------
' Save the record back in the database
' --------------------------------------
PUT #FileHandle, 2
' ----------------------------------
' OF COURSE we close the data file
' ----------------------------------
Close #FileHandle
And it's as simple as that. Load the record, change the values, put the record back in the database and you're done. If you want to add records just remember where the next record goes so you don't accidentally overwrite an existing record and you should be fine.
IN CONCLUSION:
And there you have it this is how the FIELD statement works and this is what you need to know to use them. For the most parts it's safer for the data file to use another set of variables variables. This makes any safe or load a 2 step process which is as follows:
To load the data:
1. Load the FIELD Variables with the GET statement
2. Transfer the FIELD variables to the work variables using CVI, CVD, CVL and CVS as needed.
To save the data:
1. Fill the FIELD values from the work variables with MKI$, MKD$, MKL$ and MKS as needed.
2. Save the record in the data file with the PUT statement.
Today, with the use of User Defined Type instead of FIELD statements all this data conversion and the use of the CV_ and MK_$ functions aren't needed anymore making random access file so much easier to use than the FIELD statement especially when dealing with a record structure that has a long list of record elements in its definition.
MystikShadows
Stephane Richard