There are several ways you can retrieve information from a database file. These are discussed in the following order:
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 is Director's internal persistant storage, and it's also referenced as a field -- probably a holdover from some of it's HyperCard influences.
Your development environment actually displays it's own internal cast members on screen, but does not display the contents of 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
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 directly with the FileFlex function DBGetFieldByName. This function requires the name of a database field as an argument.
DBGetFieldByName returns the specified field contents into the container named in the put command associated with the function call:
set the text of member "employee" to DBGetFieldByName("NAME")
This is an easy way to retrieve data, but it might not be the fastest. If you use DBGetFieldByName, 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.
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 FileFlex function DBGetCurrRecVal to retrieve the entire record's contents, whether you are placing these contents into a container or you are returning the FileFlex data into globals. 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
D Decrypt data as retrieving (used in
conjunction with other option characters)
A Retrieve all fields into data chunk
To retrieve the current record's contents into a field or variable, you should call the DBGetCurrRecVal function with the "A" character parameter. 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:
put DBGetCurrRecVal("A") into currRecContents
The variable currRecContents will have contents similar to these:
7 N Name,C,Dennis Wight Salary,N,100000.00 Birth,D,19631030 <== format is YYYYMMDD Single,L,T <== value is either T or 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.
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:
set text of member "Memo Field" to DBGetMemo("NOTES")
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 (like Director), 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
Retrieving a List of Matches
FileFlex 3 introduces the much-desired capability of retrieving a list of matches into a container. For example, if you did a search on a company name, you could now retrieve a list of employees matching that company name with one call the the function DBGetMatchList.
The idea behind DBGetMatchList is that it returns a "column" (or more than one) of data from the database. A column is defined as a list of fields from subsequent records, usually separated by a carriage return. When DBGetMatchList runs, it retrieves the contents of a column into a specified global variable. The function can retrieve up to 32 columns at a time (making for a very powerful data retrieval function).
You pass DBGetMatchList two parameters: a spec variable, and a max-lines parameter. The spec variable consists of a number of lines, each containing the name of a global, and a field. The field is the field from the FileFlex database to be retrieved and the global is the global into which is placed the column from the corresponding field.
It's important to realize that DBGetMatchList works off the current sort order. So if you have an index or relation active, DBGetMatchList will traverse the list in index or relation order.
Further, if you have an active relation, you can specify that a column be retrieved from a related database that is not the current database selected with DBSelect. You do this by using the arrow format (i.e., "->") in the field specification of the spec variable (as in FILE->FIELD. If you wanted to retrieve the salary information from the currently open employee database, and place that into the global "bucks", you'd use this as the line in the spec variable:
bucks,EMPLOYEE->SALARY
The second parameter for DBGetMatchList is a max-lines parameter. DBGetMatchList has the potential to consume a large amount of temporary memory (it's freed up upon exiting the function). If the second parameter is set to 0, DBGetMatchList will retrieve as much data as will fit in a 32K buffer. This means that for each global you request, the function temporarily allocates 32K. To reduce this, specify the maximum lines. For example, if you specified 50 lines maximum and the field you're retrieving is 10 chars in width, you'd temporarily allocate 500 bytes instead of 32,000.
Once you get the columns of matches and they're placed in globals, you can use a variety of functions to help prepare a formatted column (remember, it's always best to display a formatted column using a monospace font like Monoco on the Macintosh). Here are some helper functions you might want to explore:
Combining all these functions together, you can create some very impressive and powerful tables.
Discuss this chapter on the FileFlex Boards.