10. Reading Data from a Database File

Retrieving a Field by Name or Number
Reading a Record into a Container
Retrieving a Memo Field
Retrieving a Record into Global Variables
Reading a Record into a Card

There are several ways you can retrieve information from a database file. These are discussed in the following order:

a field at a time, by name or number
a record at a time into a container, (which also requires a separate step to retrieve any memo fields)
a complete record into global variables
a record (complete or partial) at a time into a card (HyperCard/SuperCard only)

Remember that FileFlex acts as a "back-end" to your application or multimedia production. As a result, you're going to be dealing with fields of different sorts. FileFlex itself is organized into records and fields, with each field being the smallest chunk of addressable data. Most development environments also have their own internal storage areas, also often called fields. For example, in Director, a text cast member (also referenced as a field) and in HyperCard, there are card and background fields.

Your development environment actually displays it's own fields, but does not display FileFlex fields. As a result, retrieving data is an effort of moving data from FileFlex fields to your host application's fields. Often this takes place via an intermediate step of retrieving the data from a FileFlex field into a variable, processing the variable data, and then placing it appropriately in the host application's display field.

Retrieving a Field by Name or Number

The simplest method of data retrieval in FileFlex is to read a specific field's contents into a variable or container. The contents of a field in the current record may be found using the field's name or number directly, and the FileFlex functions DBGetFieldByName and DBGetFieldByNum. The first requires the name of a database field as an argument; the second requires a number corresponding to a database field number in the current file.

Both of these functions return the specified field contents into the container named in the put command associated with the function call:

  put DBGetFieldByName("NAME") into field "Employee"  put DBGetFieldByNum(5) into myCount

This is an easy way to retrieve data, but it might not be the fastest. If you use either DBGetFieldByName or DBGetFieldByNum, you'll be retrieving one field at a time, for each record. Alternatively, if you use one of the functions described below, you'll be able to get the entire record at a time. A good rule of thumb is to use DBGetFieldByName if you just need one or two fields and you want to explicitly place the value of FileFlex fields into a specific container.

Note: The function DBGetFieldByNum has been part of FileFlex since it was released. It remains for backward compatibility. However, we don't recommend you use it for data retrieval because the potential for an error by misassigning the field number is pretty measurable.

Reading a Record into a Container

The next method of FileFlex data retrieval reads the entire current record (except for memo fields, which are discussed later) into a designated variable or field. You may then use "chunking expressions" to separate these contents into elements for use in your scripts.

You will use the same FileFlex function, DBGetCurrRecVal, to retrieve the entire record's contents, whether you are placing these contents into a container or whether you are using a card layout as a template to decide which fields to retrieve, and where to place their contents. This latter process is discussed below.

DBGetCurrRecVal is a very powerful and complex function. It controls how data is retrieved from FileFlex. The function operates on the current record. The first parameter is an option character that tells FileFlex how the data is to be retrieved. These characters include:

Option Character     Description
----------------   -----------------------------------------
      G            Retrieve data into matching global
                   variables
 
      C            Retrieve data into matching card fields
                   (HyperCard/SuperCard only)

      B            Retrieve data into matching background
                   fields (HyperCard/SuperCard only)

      D            Decrypt data as retrieving (used in 
                   conjunction with other option characters)

      X            Retrieve all fields into data chunk
    (any char)     You can use any character other than those
                   above.  We recommend 'X', 'L', or 'A'.

To retrieve the current record's contents into a field or variable, you should call the DBGetCurrRecVal function with a single character parameter (we recommend "X"). This operation will place into the designated variable or container the contents of the current record in the following format:

  Line 1          = Record Number
  Line 2          = Delete Flag (Y/N)
  Line 3 - Line n = FieldName, FieldType, FieldValue

Lines 3 to the end of the container will each contain the value of a specific field in the database except in the case of a memo field. If a memo field is encountered, its name and type ("M") will be returned but the third item in the line, which normally contains the field's value, will be empty. You can then use the FileFlex DBGetMemo function to retrieve the memo field.

Here is an example, using the DBGetCurrRecVal function, and the contents of a variable called currRecContents after a sample record has been read (notice we use "L" this time):

  put DBGetCurrRecVal("L") into currRecContents

The variable currRecContents will have contents similar to these:

  7
  N
  Name,C,Dennis Wight
  Salary,N,100000.00 
  Birth,D,19631030 (YYYYMMDD)
  Single,L,T (T/F)
  Note,M,

In the above example, the seventh field is a memo field called "Note"whose contents must be retrieved separately. DBGetCurrRecVal also has the ability to retrieve data and decrypt it. See the chapter on Office Quality Encryption for details.

Retrieving a Memo Field

If you need to retrieve a memo field, you should use the DBGetMemo function. It takes a single argument, the name of the memo field to be read. Here is a sample script line to retrieve a given memo field from the current record:

  put DBGetMemo("NOTES") into field "Memo Field"

DBGetMemo also has the ability to retrieve data and decrypt it. See Office Quality Encryption for details.

Retrieving a Record into Global Variables

FileFlex has the ability to place the contents of FileFlex data file fields into corresponding global variables. In most development environments, if the global variable doesn't exist, FileFlex will create it. You should note, however, that this is a function of how the development environment works. As a result, it's often good practice to pre-create the global variables (by putting some value into each corresponding variable) or by declaring the variables as global.

Use DBGetCurrRecVal by using the option character "G", as in the following example:

  put DBGetCurrRecVal("G") into DBResult

Reading a Record into a Card

In HyperCard or SuperCard, if you make the call to DBGetCurrRecVal with an argument of "B" or "C", then the function places each field in the FileFlex data file into a field in the card or background, respectively, whose name corresponds exactly to the name of the field in your scripting environment. Using the above example, assume you have background fields called Name, Salary, and Note and make the call as follows:

  put DBGetCurrRecVal("B") into currRecContents

Then the background field called "Name" would have the value "Dennis Wight" in it, the field "Salary" would have "100000.00"and the field "Note" would have the contents of that database memo field. The other values in the database would simply be ignored.

Essentially, this use of the DBGetCurrRecVal function treats the current card's card or background fields (depending on which argument you use) as a template for the retrieval of information from the database. This is a very powerful feature of FileFlex. You could, for example, design a project that consisted of a series of cards, each of which had a different collection of card fields named after database fields, and then allow the user to select any one from a list for examination. Then you can use the navigation commands (go, for instance) in the scripting language to go to that card and execute a single command that is the same for all of the various card formats.

  [Previous Chapter]    [Table of Contents]    [Next Chapter]