18. FileFlex API Reference
When developing an application using FileFlex, you'll be talking to the
FileFlex engine through an application programming interface (API). This
chapter describes each function in detail and should be used as your primary
source for how a function is called.
Functions by Name
DBAverage DBBottom DBBuildSeekExpr*
DBCheckIndex DBClose DBCloseAll
DBCloseIndex DBClosePlatform DBCloseSession
DBCopyright DBConvertCRLF DBCount
DBCreate DBCreateIndex DBCurrDBNum
DBCurrRecNum DBDatabaseExists* DBDecrypt
DBDeleteRecs DBEncrypt DBFindMemo
DBGetCurrRecVal DBGetFieldByName DBGetFieldByNum
DBGetGlobal* DBGetMemo DBGo
DBIndexExpr* DBInitPlatform DBListFields
DBListIndexFields* DBLocate@ DBMaxRecs*
DBOpenSession DBPack DBPlatform
DBQuery+ DBRecallRecs DBRecordDeleted
DBReindex DBSeek DBSelect
DBSelectIndex DBSetGlobal* DBSkip
DBSum DBTop DBUse
DBUseIndex DBVersion DBWriteMemo
DBWriteRec DBZapRecs
* New in 2.0
+ Name changed in 2.0
@ Obsolete in 2.0
Functions by Category
Initialization Functions
Creating Your Own Database Files
- Creating Databases the Easy Way
- Creating Databases Programatically (DBCreate)
Managing Database Files
Navigating in the Database
Retrieving Information
Updating Information
Using Index Files
Performing Calculations
FileFlex Version Information
Initialization Functions
Initializing the Host Platform (DBInitPlatform)
Syntax: DBInitPlatform()
There are very few differences you need to be concerned about when programming
in different platforms (i.e., Mac vs. Windows). We've added two standard
function calls that allows you to place the bulk of the platform-specific
code in one place. When you move your code from Mac to Windows, you'll want
to just look at this code.
On the Macintosh, DBInitPlatform is where you place the "OpenXLib"or "start using" command to grant you access to the FileFlex engine
as an external function.
- Director 5: If you install the Xtra inside the Xtras folder (recommended!),
you won't need to use the openXLib reference since Director will automatically
open FileFlex when Director loads. When you build a runtime version, if
you make sure your installer creates an Xtras folder at the same level as
your projector application and put the runtime Xtra into the folder, you'll
have the same benefits. (New in 2.0)
- Macintosh XFCN: If you install the XFCN inside your movie, project,
or stack, you won't need to externally reference the XFCN at all. Director
4 users should consider upgrading to Director 5 rather than using this method.
Users of FileFlex Lite have not been provided with an XFCN version.
A typical DBInitPlatform definition on the Mac looks like this in Director:
on DBInitPlatform
openXLib "FileFlex"
end DBInitPlatform
Under Director 4.0x for Windows, DBInitPlatform both provides access to
the FileFlex engine and initializes the FileFlex engine as a Director XObject.
Your DBInitPlatform call MUST look like the following:
on DBInitPlatform
global gDBGlobalPtr1030, FFxobj
put empty into gDBGlobalPtr1030
openXlib "FFDIR.DLL" -- < -- see comment below
set FFxobj = ff(mNew) -- do not tinker with this line!
end DBInitPlatform
Director 4.0x Users: There is often confusion about the location
of the FileFlex engine. When not provided a fully-realized path specification,
both MacOS and Windows looks for the FileFlex engine (FileFlex on the Mac
and FFDIR.DLL in Windows) in the current working directory. The current
working directory is the directory in which the currently running application
is located. Note that your stack or movie is not the application unless
you've turned it into a standalone. So, if you want to avoid providing a
fully specified pathspec in your start using or OpenXlib, put the FileFlex
engine into the same directory/folder as the application that calls it.
Better yet, consider upgrading to Director 5 and using the FileFlex Xtra.
Initializing FileFlex (DBOpenSession)
Syntax: DBOpenSession()
Before calling any FileFlex functions, you must notify FileFlex that you
intend to use its routines. To do this, put the following call into the
handler called most immediately after your project is opened (in Director,
this would be the startMovie handler). The variable called dbResult is arbitrarily
named; you can use any variable name you like. During debugging of a FileFlex
interface, you might even put these results into fields or the Message Box
so you can see exactly what is happening.
put DBOpenSession() into dbResult
Note: For those of you calling the FileFlex XFCN directly (in an
unsupported environment like AppWare or Oracle Media Objects) FileFlex expects
to set/check a global variable called gDBActive1030. Make sure you set gDBActive1030
to "true" before calling FileFlex(1), the direct XFCN equivalent
of DBOpenSession. If gDBActive1030 is already true, then don't call FileFlex(1).
Closing Down FileFlex (DBCloseSession)
Syntax: DBCloseSession()
After you are done using FileFlex, you should clean up your environment
by releasing memory used for buffers. To do this, put the following call
into the handler called when exiting your movie or project. (It may be placed
elsewhere, but this is the most logical place for its use.)
put DBCloseSession() into dbResult
This will free all the data structures in use by the FileFlex engine. Failure
to do this may cause all sorts of unpleasant problems.
Note: For those of you calling the FileFlex XFCN directly (in an
unsupported environment like AppWare or Oracle Media Objects) FileFlex expects
to set/check a global variable called gDBActive1030. Make sure you set gDBActive1030
to "" (empty) before calling FileFlex(2), the direct XFCN equivalent
to DBCloseSession. If gDBActive1030 is already empty, then don't call FileFlex(2).
Closing the Host Platform (DBClosePlatform)
Syntax: DBClosePlatform()
On the Macintosh, DBClosePlatform is where you place the "stop using"or "closeXLib" command to grant you access to the FileFlex engine
as an external function.
- Director 5: If you install the Xtra inside the Xtras folder,
you won't need to use the closeXLib reference since Director will automatically
close FileFlex when Director exits. When you build a runtime version, if
you make sure your installer creates an Xtras folder at the same level as
your projector application and put the runtime Xtra into the folder, you'll
have the same benefits. (New in 2.0)
- Macintosh XFCN: If you install the XFCN inside your movie, project,
or stack, you won't need to externally reference the XFCN at all. Director
4 users should consider upgrading to Director 5 rather than using this method.
Users of the Director 5 Special Edition have not been provided with an XFCN
version.
A typical DBInitPlatform definition on the Mac looks like this in Director:
on DBClosePlatform
closeXlib "FileFlex"
end DBClosePlatform
Under Director 4.0x for Windows, DBClosePlatform both closes access to the
FileFlex engine and disposes of the FileFlex engine as a Director XObject.
Your DBClosePlatform call MUST look like the following:
on DBClosePlatform
global FFxobj
put FFxobj(mDispose)
closeXlib "FFDIR.DLL" end DBClosePlatform
Note: Be sure to only close the host platform after you've closed
files and executed a DBCloseSession. Only call DBCloseSession ONCE!
Creating Your Own Database Files
Creating Databases the Easy Way
New in 2.0: The easiest way to create your own FileFlex databases
is to use the new FileFlex Database Designer included with FileFlex. See
Creating Databases for more details.
Runtime Note: You must pre-create all databases you distribute. You
may not include the FileFlex Database Designer itself with your application.
Creating Databases Programatically (DBCreate)
Syntax: DBCreate(<database name>, <# fields>, <field
list>, <overwrite flag>)
Hardier souls may choose to use the DBCreate function to generate database
files. DBCreate requires four parameters: the name of the database, the
number of fields, a container containing a field list, and "true"or "false" (true if you don't want FileFlex to overwrite a data
file).
Each line of the field list container is used for a field. Each line contains
the following information: name of field, type of field, length, and number
of decimal places (for numeric fields).
Here's an example call:
put DBCreate("FRIENDS",6, cd fld dbFields,false) into dbResult
Runtime Note: You must pre-create all databases you distribute. You
may not use the DBCreate call with your application.
Note: See Creating Databases for a more detailed
description of creating databases with DBCreate.
Managing Database Files
Open a Database File (DBUse)
Syntax: DBUse(<database name> [, <path spec>])
To open a database file, call the FileFlex function DBUse, supplying a database
file name (including full path name if required) as an argument. Put the
result into a variable, because you will need to refer to this file by its
returned value later. Be sure not to use the same variable for any two files
you to have open at one time.
Just type the following line into your script, changing the name of the
database file appropriately.
put DBUse("HD:DB Files:Test.DBF") into databaseID
Alternatively, you can split the path specification into two parameters:
the first the actual file name and the second the path specification. In
the runtime, the path specification will remain unchanged but the database
file name will be decrypted:
put DBUse("Test.DBF","HD:DB Files:") into dbID -- Developer FF
put DBUse("&#R*$*@U","HD:DB Files:") into dbID -- Runtime FF
(Special thanks to Wally Rutherford for pointing out the need for this feature.)
Determining if a Database Exists (DBDatabaseExists)
Syntax: DBDatabaseExists(<database name> [, <path spec>])
New in 2.0: You can easily determine if the database file you're
about to manage is available by calling DBDatabaseExists. You might want
to use this function if you're moving data from CD-ROM to a hard drive,
or if you want to be sure the CD has been inserted and the database is available.
DBDatabase Exists use the same parameters as DBUse (the database name and
an
optional path specification). If the database is not available, DBDatabaseExists
returns a negative result code.
Note: DBDatabaseExists leaves <database name> closed after
executing. If the database is available, you'll need to reopen it with DBUse.
Select Database (DBSelect)
Syntax: DBSelect(<database ID>)
Only one database file can be the current file. You may have numerous database
files open at once, but only one of them will be the current database at
any one time. The DBSelect function will allow you to choose any open database
to act as the current database. To choose a previously opened database as
the current file, use the FileFlex function DBSelect. It requires a single
argument, the variable into which you put the ID of the database when you
opened it with your call to DBUse.
put DBSelect(DatabaseID) into dbResult
Just as there can only be one current database file, so there can only be
one record in that file that is recognized by FileFlex as the "current"record. All operations are performed on or relative to this record. When
FileFlex switches between databases, the current record is saved.
An example will make this clearer. Let's assume that there are two databases
open: a names database (ID 1) and an addresses database (ID 2). You're currently
on the 53rd record of the names database. When you were last in the addresses
database, the current record pointer pointed to record number 2,302. Now,
when you execute a DBSelect(2), which moves you to the addresses database,
the addresses database becomes current and the current record is now 2,302.
If you then reselect the names database by issuing a DBSelect(1), the names
database becomes current and the current record is 53.
Identify Selected Database (DBCurrDBNum)
Syntax: DBCurrDBNum()
There are times when you might want to know the database ID of the currently
selected database. For example, you may want to interrupt processing on
a file to undertake some special processing on another file and then return
to processing the original file. To do so, use the FileFlex function DBCurrDBNum.
It returns the ID of the currently selected database. This ID is the same
as that returned when you called DBUse for this database.
put DBCurrDBNum()
Close Database (DBClose and DBCloseAll)
Syntax: DBClose(<database ID>)
Syntax: DBCloseAll()
When you are finished with a database, you can close the file and reclaim
the memory used for its buffers by calling the FileFlex function DBClose
and passing the database ID returned by the call to DBUse as an argument.
DBClose returns an error code of 0 on successful completion, or an alternative
error code if there is a problem.
put DBClose(DatabaseID) into dbResult
If you have more than one file open and want to close a specific open file,
you must first insure that the file you want to close is the current file.
Use DBSelect for this purpose. If you have more than one database file open
and you are ready to end your work with FileFlex (or with that set of files),
you can close them all in one step with the DBCloseAll function. It takes
no argument.
put DBCloseAll() into dbResult
Here's an interesting tip: DBCloseSession executes a DBCloseAll prior to
evacuating FileFlex from memory. So, while you should as good practice close
your database files, FileFlex will clean up after itself--as long as you
remember to close the session.
Getting a List of Fields (DBListFields)
Syntax: DBListFields()
You may occasionally wish to examine the fields in a database--their names,
the types of data they contain, and their sizes, etc. FileFlex provides
a function that allows you to retrieve such information about the currently
active database file, if you wish.
You can obtain a list of all of the fields in the current database file
with the FileFlex DBListFields function. Since it operates on the current
database, you must first call the DBSelect function to make the desired
database current if it is not already. The field list is returned in the
variable or container you specify. The format for the field list devotes
one line to each field, except for the first line, which contains a number
indicating the number of fields in the file. Each line of the variable,
or container, describes a field as follows:
field name, field type, field width, decimal places
The field type can be any of the values shown below:
ABBREVIATION FIELD TYPE
N numeric
C character
M memo
L logical
D date
The DBListFields function's result should be placed into a variable or field
so that you can later use it to relate field names and content types to
field numbers.
In the following example, the first line makes the database whose name you
supply as a parameter the current database, and the next one puts a list
of its fields into a card field called "DBFields".
put DBSelect(databaseID) into dbResult
put DBListFields() into field "DBFields"
The first line of "DBFields" will contain the number of fields
in the file. All subsequent lines will contain each field's name, type,
width, decimal places as a comma-delimited list.
From the above example, the field named "DBFields" would contain
the following information, assuming this is the structure of the file being
examined:
Name,C,20,0
Salary,N,10,2
Single,L,1,0
Note,M,10,0
Note: Even non-numeric fields contain exactly four items in their
field descriptions, including the number of decimal places (which will always
be zero for non-numeric fields). Be forewarned that this is NOT the same
structure as required by DBCreate.
New in 2.0: FileFlex 2.0 provides the DBListIndexFields
function, which provides a similar service for fields placed into an index.
Navigating in the Database
Count Records (DBCount)
Syntax: DBCount()
To count the number of records in the current database file, you can use
the FileFlex DBCount function. Since it operates only on the current database,
you must first call the DBSelect function to make the desired database current
if it is not already. The first line below makes the selected database current.
The second line will then return the number of records in the database file.
put DBSelect(databaseID) into dbResult
put DBCount() into numRecs
Current Record Number (DBCurrRecNum)
Syntax: DBCurrRecNum()
To find the record number of the current record in the current database
file, you can use the FileFlex DBCurrRecNum function. This routine will
return the current record number. This routine returns the physical record
number. If the logical record order has been changed with DBQuery or indexing,
moving one record forward or backward in the database won't correspond directly
to adding one or subtracting one from the physical record number.
put DBCurrRecNum() into CurrentRecordNumber
Go to a Record (DBGo)
Syntax: DBGo(<record #>)
You can position the current record pointer at any physical record, even
with a currently active index, by using the FileFlex DBGo function. It takes
one argument, the record number to which you wish to be positioned. It does
not retrieve any data.
put DBGo(39) into dbResult
The above line will move the current record pointer to physical record 39.
A "physical" record is usually different from a "logical"record, which refers to the record's number in indexed, or sorted, sequence.
Move to Top or Bottom (DBTop and DBBottom)
Syntax: DBTop()
Syntax: DBBottom()
You may sometimes want to get to the beginning (top) or to the end (bottom)
of a database file. FileFlex supplies two functions for these purposes.
DBTop and DBBottom are both subject to the logical record order. If you've
got a current index or DBQuery operating, DBTop will take you to the logical
first record and DBBottom to the logical last record.
To get to the top of the current database file and retrieve the value of
the first record in the file, use the DBTop function. This will position
the current record pointer to the top record in the database (physical record
or, if an index is open, the first record in index order). You can then
use DBGetCurrRecVal to retrieve the data in this record into fields or a
container as desired.
Type the following lines, changing the name of the container as appropriate,
to have FileFlex move to the first record in the current database and put
that record's field values into a named container:
put DBTop() into dbResult
put DBGetCurrRecVal("A") into contents
Use DBBottom() to get to the last record in the database.
Skipping Records (DBSkip)
Syntax: DBSkip([-]<# records>)
If you don't know the number of the record you want to access, but you know
its position relative to the current one, use the DBSkip function. DBSkip
is also subject to logical record order. If an index or DBQuery is active,
DBSkip will move the offset number of records according to the logical order
of the database.
You can move the current database record pointer forward or backward a specified
number of records with the FileFlex DBSkip function. You must supply an
integer (positive or negative) describing the number of records to skip.
A negative number tells FileFlex to move backward in the file. When it reaches
the indicated record, FileFlex leaves the record pointer positioned at the
record. You can then use the DBGetCurrRecVal function to retrieve the fields
in that record as desired.
put DBSkip(23) into skipResult
put DBGetCurrRecVal("A") into dbResult
Here is how the function would normally be called if you want to skip 10
records from where you are currently positioned in the file:
put DBSkip(10) into dbResult
Or, for example, you may enter:
DBSkip(15) -- to skip forward fifteen records.
DBSkip(-2) -- to skip backward two records.
The behavior of DBSkip depends on whether there is an index file open or
whether a DBQuery operation is currently active. If an index file is currently
in use, DBSkip will skip the number of records in the sequence in which
the index sorts the file. If a DBQuery function has been called with an
argument other than an empty string, then DBSkip will skip records matching
the search criteria defined in the DBQuery function.
Retrieving Information
Get Current Record (DBGetCurrRecVal)
Syntax: DBGetCurrRecVal("A" | "B" | "C"| "G")
Syntax: DBGetCurrRecVal("AD" | "BD" | "CD"| "GD", <decryption key>)
Once you have arrived in your database at the record whose value you wish
to retrieve, you can retrieve its contents with the FileFlex DBGetCurrRecVal
function.
You can return the contents of all the fields in the current record. After
executing the following command, line 1 of the container will contain the
record number, line 2 the delete flag, and all subsequent lines the data
in the file, with the name, type, and value of each field on a separate
line.
put DBGetCurrRecVal("A") into field Contents
DBGetCurrRecVal does NOT return the values of memo fields in the file if
you use this method. You must retrieve each memo field separately using
the GetMemo function in that case.
You can retrieve fields from the database file directly into global variables.
In most development tools, FileFlex retrieves those database fields which
have a global variable of the same name--if a global doesn't exist, it is
created. If you use the "G" parameters to place retrieved data
into global variables, FileFlex will retrieve any memo field that has a
corresponding global variable.
put DBGetCurrRecVal("G") into foo
Be sure to check how your development environment handles globals. Some
environments require globals to be pre-defined, others make globals static,
storing data from one session to another. You'll need to know the characteristics
of your development system to get the most joy and least surprises from
this feature.
You can retrieve the contents of any database record whose number you know
by combining the FileFlex function DBGo followed by the use of the DBGetCurrRecVal
function. For example, to get the contents of record 23 and put them into
their global variables, you would carry out these two lines of code:
put DBGo(23) into dbResult
put GetCurrRecVal("G") into foo
If you don't know the number of the record you wish to retrieve, but you
do know something about its contents, you can locate the record with either
DBQuery or DBSeek.
Encryption: This function has encryption/decryption options. See
Office Quality Encryption for details.
HyperCard and SuperCard only: If you use the XFCN interface in either
HyperCard or SuperCard, you can retrieve only selected fields from the database
file into the card fields on the current card. In this case, FileFlex retrieves
only those database fields which have a card field of the same name. If
you use "C" or "B" parameters (See below) to place retrieved
data into the card or background fields of the current card, FileFlex will
retrieve any memo field that has a corresponding HyperCard field.
put DBGetCurrRecVal("C") into foo
You can also retrieve only selected fields from the database into background
fields on the current card. In this case, FileFlex retrieves only those
database fields which have a bg field of the same name. Note that the variable
name "foo" is arbitrary and unused but must be supplied.
put DBGetCurrRecVal("B") into foo
Finding Information In A Record (DBQuery)
Syntax: DBQuery(<query expression>)
To query a database based on certain criteria and then find the appropriate
record, use DBQuery (formerly 'DBLocate'). DBQuery will locate a record
based on the contents of one or more database fields for which you do not
have an index or for which you do not wish to use the index file for some
reason (e.g., you want to perform a complex query), you can use the DBQuery
function.
The DBQuery function takes a single argument, which must be either a string
or a container that holds a string. The string must evaluate to a logical
expression (i.e., one that can be True or False). Generally, these expressions
are strings that contain the name of a database field, a comparison operator
(=,>,<,<>, etc.) and a value to be compared.
For example, this line:
put DBQuery("sales > 50000") into dbResult
will set up a search condition which tells FileFlex that you only want to
see records where the value in the database field called "sales"exceeds $50,000.
You can combine search terms using logical connections (.AND., .OR., and
.NOT.). For example, to find records where sales are more than $50,000 and
the customer is located in Michigan, you could use a line like this:
put DBQuery("sales > 50000 .AND. state='CA'") into dbResult
New in 2.0: This function used to be called 'DBLocate' but since
it's designed to perform queries, we changed the name to be more clear.
For backwards compatibility, we're still including the DBLocate wrapper
script, but it will be eliminated by in the next release (FileFlex 2.1).
See Searching by Example for more details on using
DBQuery and Intrinsic Function Reference for details
on using FileFlex built-in functions in your query expressions.
Get Field Contents (DBFieldByName and DBGetFieldByNum)
Syntax: DBGetFieldByName(<field name>)
Syntax: DBGetFieldByNumber(<field number>)
You can obtain the value of a single field in the current record if you
know the field's name or number using the FileFlex functions DBGetFieldByName
and DBGetFieldByNumber. Both functions return the value of the indicated
database field into the result variable.
Type the following line, providing your own field name, to retrieve a single
named field from a database:
put DBGetFieldByName("EMPNAME") into field "Name"
Type the following line, providing your own field number, to retrieve a
single field from a database:
put DBGetFieldByNumber(23) into theName
Either of these commands can use a container name as an argument. In that
case, FileFlex assumes the container holds the name or number of the field
desired.
Encryption: This function has encryption/decryption options. See
Office Quality Encryption for details.
Get Memo Field (DBGetMemo)
Syntax: DBGetMemo(<field name>)
Whenever you use the FileFlex record-retrieval function DBGetCurrRecVal
to place the values in a database record into a single container rather
than global variables or card or background fields, you must separately
retrieve any memo fields in that record.
The FileFlex DBGetMemo function will retrieve a named memo field's contents
and put that data into the named container. Type the following line, changing
the name field as needed, to retrieve a memo field:
put DBGetMemo("NOTES") into myMemo
If you use DBGetCurrRecVal to retrieve a record list, you can tell that
GetCurrRecVal has found a memo field even if you don't know the database
file's structure, because any line of the container into which you place
the record values that contains a memo field has an "M" as its
second item and an empty third item.
If you use DBGetCurrRecVal with the global variable "G" option
or card ("C") or background ("B") fields option (HyperCard/SuperCard
only) to retrieve database information, you need not retrieve memo fields
individually. FileFlex will do this for you automatically.
Encryption: This function has encryption/decryption options. See
Office Quality Encryption for details.
Full Text Search in Memo Fields (DBFindMemo)
Syntax: DBFindMemo(<field name>, <search string>)
In earlier versions of FileFlex (and most dBASE/xBASE database implementations),
information that goes into a memo field is not searchable. FileFlex now
supports a DBFindMemo command that does full text searches of memo fields
for specified strings.
DBFindMemo is very fast and reasonably powerful. It does searches in physical
record order (meaning that any existing DBLocate, DBSeek, and DBSkip options
are ignored). DBFindMemo starts at the current physical record and searches
iteratively, record-by-record until it either finds a matching string or
the end of the file. Because it starts at the physical record, you may want
to turn off any pre-existing searches with DBLocate("") to make
sure a DBTop() takes you to the first physical record.
If DBFindMemo succeeds, it returns a 0; if it fails, it returns a 3 (and
positions the physical record pointer at the end of the file).
If you use DBFindMemo, you should be aware that it searches until it finds...so
that if you've got a huge database and unleash DBFindMemo, it could take
some time until the routine returns a result. DBFindMemo does not offer
any conditional expressions; if you look for "TITLE=foo", it will
search for the string "TITLE=foo". DBFindMemo is extremely literal;
it knows not of spaces, words, items, separators or much else. DBFindMemo
is not case specific. Searching for "foo" will return successful
if "FOO", "foo", "fOO" or even "foodstuffs"is found.
put DBFindMemo("NOTES","Component Software") into successFlag
The first parameter is the name of your memo field, the second is the string
you are searching.
Converting Platform Specific End-of-Lines
(DBConvertCRLF)
Syntax: DBConvertCRLF(<data> [, "A" | "M"| "W"])
The Macintosh terminates each line with a newline, and under Windows the
convention is that each line is terminated with a carriage return and line
feed. If you use memo fields, you may need to be aware of this.
Note: Director on both Macintosh and Windows uses the Macintosh format
newline end of line character on both environments. As a result, if you
write something that you store into a memo field in FileFlex on the Macintosh
and you move it over to Windows and you run it in Director under Windows
with FileFlex for Windows you will be able to read that memo field with
no discernible difference.
The format of DBConvertCRLF is:
put DBConvertCRLF(<data>[, <option>]) into newData
DBConvertCRLF is important when you're reading DBF files created outside
of this protected environment. DBConvertCRLF takes a container (a variable
or a field) and converts the end-of-line characters according to the option
parameter. If there is no option parameter (or the option parameter is set
to "A" for "Automatic"), DBConvertCRLF converts the
data into a form appropriate for the currently executing platform. If the
option parameter is set to "M" (for Macintosh), DBConvertCRLF
converts the data into newline-terminated lines for use on the Macintosh.
If the option parameter is set to "W" (for Windows), DBConvertCRLF
converts the data into carriage-return-line-feed-terminated lines for use
in Windows.
Is Record Deleted? (DBRecordDeleted)
Syntax: DBRecordDeleted()
Like the original dBASE/xBASE standard, FileFlex allocates a single byte
in each record to serve as a deletion flag. When you tell FileFlex to delete
a record using DBDeleteRecs, FileFlex doesn't actually remove the data.
Rather it sets a flag in the data file that tells you whether the record
is marked for deletion. This makes operations very, very fast and also allows
you to recover the data later. However, when navigating over records, it's
important that you check to see if a record has been marked for deletion
to determine whether you want to present the information to the user. To
permanently delete a record, see the functions DBPack and DBZapRecs.
You can determine if any specific record has been deleted by using the FileFlex
function DBRecordDeleted, supplying the record number as a parameter. FileFlex
returns a "Y" if the record's delete flag has been set, "N"otherwise.
Type the following lines, with your own record number, to use this function:
put DBGo(29) into dbResult
put DBRecordDeleted() into recDeleted
Of course, if you are already positioned at the record you want to identify
as deleted or not, you do not need to use the DBGo command.
Updating Information
Update Record or Field (DBWriteRec)
Syntax: DBWriteRec("G" | "GE", <record number>)
Syntax: DBWriteRec("C" | "CE", <record number>)
Syntax: DBWriteRec("B" | "GE", <record number>)
Syntax: DBWriteRec("X" | "L" | "A",
<record data>, <record number>)
To update all or part of a database record, you can use the FileFlex function
called DBWriteRec. This function takes three arguments.
Encryption: This function has encryption/decryption options. See
Office Quality Encryption for details.
Updating a Record Directly From Matching Global
Variables
If you pass a "G" as the first argument, you indicate to DBWriteRec
that you want to take values from global variables and pass them to the
database. The names of the global variables need to correspond to the names
of the FileFlex fields.
Note: It is important that you pre-define and fill a complete set
of global variables with names corresponding to those in the database. Since
globals behave differently between development environments, passing a full
set of globals is the best way to make sure everything works the way you
want.
Updating a Record Directly From Card or Background
Fields
HyperCard/SuperCard only: If you are using the XFCN interface in
HyperCard or SuperCard, you can drop data directly into fields. The first
argument to DBWriteRec is the container type. "C" indicates that
the new values to be inserted into the selected record can be found in card
fields on the current card whose names correspond to the names of fields
in the database file. "B" indicates that the new values are stored
in background fields of the same names as database fields.
Updating a Record From a Single Container
Instead of supplying a "B", "C", or "G" as
the first argument, you can pass any other letter code. For example, an
"A" means that the values are contained in a separate container
(i.e., a variable) whose name is supplied as the third argument. Values
are formatted with the field name and contents, separated by a comma. For
example, assume that the variable myData contained the following:
NAME,David Gewirtz
COMPANY, Component Software
PRODUCT, FileFlex
A call to DBWriteRec in the form:
put DBWriteRec("X",myData,33) into dbResult
would place "David Gewirtz" into the field "NAME", "Component
Software" into the field "COMPANY", and "FileFlex"into the field "PRODUCT", all on record 33.
Specifying the Record Number to be Updated
If the "G", "C", or "B" options have been
used, the second argument is the record number of the record to be updated.
It can be a value, a variable, or the DBCurrRecNum() function. If you've
used the single container option, you'll put the record number into the
third argument of DBWriteRec.
Add New Record (more on DBWriteRec)
To add a new record to a database file, you can use any of the various approaches
to updating an individual record in the database but supply a record number
that is at least one number higher than the number of records in the current
database. Before you add a record, then, you should use the DBCount function
to determine how many records are in the database now, then add 1 to that
value. Pass the result to the FileFlex DBWriteRec or DBWriteMemo function
as appropriate.
The following lines will set up a variable called newRecNum to contain a
value one higher than the number of records in the current database.
put DBCount()+1 into numRecs
Now you can use this value (which you might want to declare as a global
variable for use in other functions) to add records to the file. To add
an entire record to a database file in FileFlex, you can choose any of the
three above update methods.
Update Memo Field (DBWriteMemo)
Syntax: DBWriteMemo(<field name>, <field data>)
If you use the DBWriteRec function and a named container to update a database
record and that database contains one or more memo fields, you must update
those fields' contents individually with the DBWriteMemo function. You supply
two arguments: a field name and the name of a container that holds the memo
field's new contents. Notice that this function operates only on the current
record, so you must make the appropriate record current before calling this
function.
Type the following line into your script, making changes in the names of
the fields and containers as appropriate:
put DBWriteMemo("Notes",field "New Notes") into dbResult
Encryption: This function has encryption/decryption options. See
Office Quality Encryption for details.
Deleting Records (DBZapRecs, DBDeleteRecs,
DBPack, DBRecallRecs)
Syntax: DBZapRecs(<start record #>, <end record #>)
Syntax: DBDeleteRecs(<start record #>, <end record #>)
Syntax: DBRecallRecs(<start record #>, <end record #>)
Syntax: DBPack()
You can use either a one-step approach to database record deletion or you
can take a two-step approach.
In the one-step approach, you use the FileFlex DBZapRecs function and supply
two comma-delimited values to indicate the first and last records to be
deleted as arguments. The records are physically deleted and the database
compacted. This makes the records unretrievable by any means.
Type the following line, supplying your own record numbers, to carry out
this one-step record deletion:
put DBZapRecs(9,23) into dbResult
Note that even if you want to delete only one record, you must supply two
arguments. To delete record 23, then, you would use:
put DBZapRecs(23,23) into dbResult
The two-step approach marks records for deletion and only handles their
physical removal from the database on specific demand. This approach has
two primary advantages. First, it can be undone as long as the second step
hasn't yet been taken. Second, it is more efficient than the one-step approach
since the sometimes time-consuming process of compacting the database only
takes place once on demand rather than after each batch of records is deleted.
Type the following line, supplying your own record numbers, to carry out
the first step in this two-step deletion:
put DBDeleteRecs(9,23) into dbResult
If you later change your mind and want to undelete some or all of these
records, type the following line, with your own record numbers substituted:
put DBRecallRecs(9,23) into dbResult
When you are certain that you want to delete all marked records (for example,
at the end of a day or processing session), call the FileFlex function DBPack.
It takes no arguments and physically removes all marked records from the
database, compacting the file when it has done so. This makes recovery of
deleted records impossible. It's also relatively slow.
put DBPack() into dbResult
If the file you are working with has memo fields, it therefore has an associated
file (usually with a name that ends in .DBT for reasons of compatibility
with other versions of dBASE). Deleting records that contain memo fields
will not clear the memo file of those records. The file space must be reclaimed
by a programming loop that reads each record and writes it to a new file.
This was not our design decision but is part of the way xBASE systems behave.
If you want to clear the unused space from .DBT files, you're going to have
to create a second database and then copy the record information from the
first database into the second. This will create an optimized .DBT file.
We don't recommend bothering with this unless space is at a premium and
time is not.
Using Index Files
Creating an Index File (DBCreateIndex)
Syntax: DBCreateIndex(<index file>, <index expr>, "0",
"0" | "1")
Use the DBCreateIndex function to create a brand-new index file. DBCreateIndex
expects four parameters: the name of the index file, the index expression
(usually the name of the field you wish to index), the string "0",
and "0" if you want to overwrite an existing index file and "1"if you don't.
put DBCreateIndex("STARS","UPPER(NAME)","0","0") into dbResult
Runtime Note: You must pre-create all indexes you distribute. You
may not use the DBCreateIndex call or the FileFlex stack itself with your
application.
New in 2.0: You can use the FileFlex Database Designer to construct
your index files. See Ultra-fast Searching with Indexes
to understand more about how indexes work and how to construct index expressions.
Also see Intrinsic Function Reference for more details
on index expressions.
Open an Index File (DBUseIndex)
Syntax: DBUseIndex(<index file> [, <path spec>])
To open an index file, use the FileFlex DBUseIndex function. Supply the
index file's name, including path name if needed, as an argument. Assign
the result of this function to a global variable, since you'll need to refer
to its database ID in other scripts and handlers.
put DBUseIndex("STARS") into starIndex
Alternatively, you can split the path specification into two parameters:
the first the actual file name and the second the path specification. In
the runtime, the path specification will remain unchanged but the database
file name will be decrypted:
put DBUseIndex("STARS","HD:DB Files:") into ndxID -- Developer FF
put DBUseIndex("&#R*$","HD:DB Files:") into ndxID -- Runtime FF
(Special thanks to Wally Rutherford for pointing out the need for this feature.)
Note: Indexes are tied to their original data files (this makes sense
because you can't index data in a file that doesn't have the data). As such,
you must have the appropriate data file selected with a DBSelect function
before calling DBUseIndex. Failure to do so usually results in FileFlex
generating an error code, the user calling technical support, and said user
being told to read this section again.
Use an Index File (DBSelectIndex)
Syntax: DBSelectIndex(<index ID>)
To use a particular index file, you must first open it with the DBUseIndex
function. This function returns a value you should store in a global variable.
This global is then supplied as an argument to the SelectIndex function
to tell FileFlex to use this index as the current index file.
Assuming that you have previously opened the index file STAR.NDX and have
its reference stored in the global variable starIndex, you would make that
index current with a line like the one below.
put DBSelectIndex(starIndex) into dbResult
Note: Indexes are tied to their original data files. As such, you
must have the appropriate data file selected with a DBSelect function before
calling DBSelectIndex. Failure to do so usually results in FileFlex generating
an error code, the user calling technical support, and said user being told
to read this section one more time.
Close an Index File (DBCloseIndex)
Syntax: DBCloseIndex(<index ID>)
To close a particular index file, it must already have been opened previously
with the DBUseIndex function. This function returns an index ID value you
should store in a global variable. This ID value is then supplied as an
argument to the DBCloseIndex function to tell FileFlex you no longer need
the index file to be available.
Assuming that you have previously opened the index file STAR.NDX and have
its reference stored in the global variable starIndex, you would close that
index with a line like the one below. (Copy and paste that line, changing
the variable name as appropriate) into your own script to close a particular
open index.)
put DBCloseIndex(starIndex) into dbResult
Note: Indexes are tied to their original data files. As such, you
must have the appropriate data file selected with a DBSelect function before
calling DBCloseIndex. Failure to do so usually results in FileFlex generating
an error code and, well, you know the rest.
Seek Specific Record (DBSeek)
Syntax: DBSeek(<seek expression>)
To locate a record that matches a specific expression in the indexed field(s)
of your database and retrieve that record's values, you will use the FileFlex
DBSeek function. The function takes a single argument, which is the seek
expression, (i.e., the value to be searched for in the indexed database
file using the current index).
The DBSeek function repositions the current record pointer to the record
that matches the criterion in the seek expression or the one immediately
following where the record would have been found if no matching expression
is located in the currently active index. You can then use the DBGetCurrRecVal
function to return the values in that records fields.
You must, of course, make sure that the index file that arranges the file
in order by the desired field on which you wish to search is the current
index file. Use the DBSelectIndex function for this.
put DBSelectIndex(starID) into dbResult
put DBSeek("Marilyn Monroe") into dbResult
You are now ready to retrieve the desired field(s) from this record.
Once you have located a record matching your index criterion with DBSeek,
you may want to locate other matching records in the file. In that case,
you should use DBSkip(1) to move the current record pointer to the next
matching record in indexed order.
When the indexed field on which you are performing a DBSeek operation is
a character field, DBSeek may locate a record that only partly matches your
search criteria. Furthermore, if it does not find a matching record, it
positions the record pointer at the next record in the file AFTER where
it expected to find the information for which you have instructed it to
search.
You can determine which type of result DBSeek has obtained because
its return value is:
0 to mean an exact match was found
2 to mean a partial match was found
4 to mean no match was found
Where an exact match is not important, you need only check for a return
value of 4. All other times, you should check for a return value other than
0 and respond accordingly.
A trick often used by FileFlex developers is to do a DBSeek on a partial
match (for example, "Appl" as the seek expression to find all
the possible "Apple Computer", "Apple Computer, Inc.",
"Apple Pie", etc entries). Even though the DBSeek return code
is other than zero, by grabbing the search field value using DBGetFieldByName
and comparing, it's possible to tell if a record is a near match.
New in 2.0: DBSeek requires that the seek expression be the same
width (including blank spaces) as the field definition. So if you define
the field CITY to contain ten characters, you should pass "NEW~YORK~~"(the ~'s are used to indicate spaces) to the DBSeek function. FileFlex 2.0
adds a new DBBuildSeekExpr function that can be very helpful in building
perfect seek expressions (see below).
Building a Seek Expression (DBBuildSeekExpr)
Syntax: DBBuildSeekExpr(<indexID>, <index expr> [, <index
expr> ... ])
The DBSeek function works hand-in-hand with the DBCreateIndex function.
DBCreateIndex takes an index expression template (i.e., "UPPER(LAST)+UPPER(FIRST)")
and creates a new index file. DBSeek uses an index expression (i.e., ("GEWIRTZ
DAVID ") and searches the index file. The challenge is that the index
expression must precisely match the index template in structure or DBSeek
will not perform an exact match.
New in 2.0: Until FileFlex 2.0, the user had to very carefully construct
the DBSeek expression by hand, converting character case, padding strings,
offsetting decimal values and combining multi-field strings into a properly
constructed seek expression. The potential for error was considerable and
this caused frustration among new and old users alike. FileFlex 2.0 introduces
DBBuildSeekExpr, a helper function that constructs a proper seek expression
based on the template in the index file itself. DBBuildSeekExpr properly
pads the string width, converts strings to upper case for case-insensitive
searches, offsets decimal values appropriately, and combines multiple fields
into a single search expression.
Using DBBuildSeekExpr is quite simple. The first parameter is the index
ID of an open index file. The second and subsequent parameters correspond
to each individual index expression in the index file. For example, let's
assume you had an index file declared as "UPPER(LAST) + UPPER(FIRST)".
Fields FIRST and LAST are each ten characters wide. Here's what you'd pass
to DBBuildSeekExpr if you wanted to find my name:
put DBBuildSeekExpr("1","Gewirtz","David") into expr
DBBuildSeekExpr would look at the index definition of the index file represented
by ID #1 and would place into expr the following string (the ~'s are used
to indicate spaces to make it easier for you to read):
"GEWIRTZ~~~DAVID~~~~~"
Determining an Index's Expression (DBIndexExpr)
Syntax: DBIndexExpr(<indexID>)
New in 2.0: DBIndexExpr will retrieve the expression used to create
the index file and return it to you as a string. Continuing on the example
above, DBIndexExpr("1") will return "UPPER(LAST) + UPPER(FIRST)".
Determining an Index's Fields (DBListIndexFields)
Syntax: DBListIndexFields(<indexID>, <delim>)
New in 2.0: DBListIndexFields also provides the index expression back to
you, but it strips out the intrinsic function calls and returns only the
field names. Further, DBListIndexFields requires a delimiter character that
lets you specify how you want the fields returned. Here's what DBListIndexExpr("1",",")
would return:
UPPER,LOWER
And here's what DBListIndexExpr("1",RETURN) would return:
UPPER
LOWER
Dynamically Updating Indexes
Index files are maintained automatically by FileFlex. Whenever you execute
a DBWriteRec function call, FileFlex updates all currently open index files
for that database. No special programming is needed.
Checking Index File Integrity (DBCheckIndex)
Syntax: DBCheckIndex(<indexID>)
DBCheckIndex will examine an index file to determine if it is up to date
and reliable. A value of less than zero indicates that the Index file is
suspect.
put DBCheckIndex(indexID) into dbResult
Note: Indexes are tied to their original data files. As such, you
must have the appropriate data file selected with a DBSelect function before
calling DBCheckIndex. Failure to do so usually results in FileFlex generating
an error code and the user calling technical support.
Rebuilding the Index File (DBReindex)
Syntax: DBReindex(<indexID>)
If your index file is not valid, you can force FileFlex to completely reconstruct
the file using DBReindex.
put DBReindex(indexID) into dbResult
Note: Indexes are tied to their original data files. As such, you
must have the appropriate data file selected with a DBSelect function before
calling DBReindex. Failure to do so usually results in FileFlex generating
an error code and the user calling technical support. Are you beginning
to notice a trend here?
Performing Calculations
Adding theTotal Value of a Field (DBSum)
Syntax: DBSum(<field name>)
To calculate the total of all of the numeric contents of a specific database
field, use the FileFlex DBSum function. Supply the name of a numeric field
and put the result into a container. Copy and paste the following line into
your script to handle this task, making appropriate changes in the field
and container names:
put DBSum("SALARY") into myPayroll
Averaging a Field's Value (DBAverage)
Syntax: DBAverage(<field name>)
To calculate the average of all of the numeric contents of a specific database
field, use the DBAverage function. Supply the name of a numeric field and
put the result into a container. Copy and paste the following line into
your script to handle this task, making appropriate changes in the field
and container names:
put DBAverage("SALARY") into avgSalary
Encrypting and Decrypting Data (DBEncrypt,
DBDecrypt)
Syntax: DBEncrypt(<string>, <encryption key>)
Syntax: DBDecrypt(<string>, <decryption key>)
DBEncrypt and DBDecrypt are standalone decryption functions provided in
the FileFlex engine that allow you to get and retrieve encrypted data. These
are ideal for encrypting and decrypting passwords, for example. The first
parameter is the string you want to encrypt or decrypt. The second parameter
is the encryption key, a string that is used as the seed for the encryption
formula. DBEncrypt returns an encrypted string. DBDecrypt, assuming it gets
the right key, returns the decrypted string. Both are case sensitive.
FileFlex Version Information
Determining the Current Version of FileFlex
(DBVersion)
Syntax: DBVersion()
You can ask FileFlex to tell you it's version number using the function
DBVersion. It will return the numerical version number as a string followed
by a status message indicating whether the external is licensed for distribution
or not.
put DBVersion()
New in 2.0: FileFlex now specifies some very useful information as
part of the version string. Version strings are now composed of 4 parts:
the version number, the capability code (i.e., runtime, demo, sdk), the
platform (i.e, 68K, WIN), and the host environment (i.e., Director 5, Authorware,
XFCN, etc). Here's how they break down:
2.0.0L-PPC/DIR4 (Developer Version - Not Licensed for
-----| | | Distribution)
| | | |
| | | |
| | | The host development environment
| | |
| | The host OS environment
| |
| Single character, if available, determines edition
| of FileFlex: 'L' is lite edition, 'R' is runtime
| 'D' is demo, 'P' is professional, the full SDK.
|
Three-tier version number. The first number represents
major version, the second minor version, and the third
bug-fix updates. Bug-fix updates can always be downloaded
for free from the www.component-net.com website.
If you want to get the version number only of the version string, you'll
need the first five characters:
put char 1 to 5 of DBVersion()
Determining the Host Platform (DBPlatform)
Syntax: DBPlatform()
You can ask FileFlex to the current platform (i.e., Windows or Mac) by calling
DBPlatform:
put DBPlatform()
FileFlex will return the following strings:
Platform String Returned
---------------- -----------------
Macintosh 68K FF68K
Macintosh PPC FFPPC
Windows FFWIN
Getting the FileFlex Copyright Message (DBCopyright)
Syntax: DBCopyright()
You can ask FileFlex to report it's copyright message by using the DBCopyright
function like this:
put DBCopyright()
Determining the Maximum Records Accessible
(DBMaxRecs)
Syntax: DBMaxRecs()
New in 2.0: FileFlex now ships in a variety of "editions".
Our online demo is available free to anyone who wants to see what FileFlex
can do. However, it is limited to accessing only the first 100 records of
a database file. Similarly, our Lite edition, bundled with Macromedia's
Director, enables full database access on databases of 1000 records or less.
The DBMaxRecs function allows returns the maximum number of records the
engine will allow you to address. The function will return either a numerical
value in string form (i.e., "100", "1000") or the string
"unlimited". Remember, the professional edition of FileFlex is
designed for very large databases; you can access upwards of a billion records.
Internal Test Functions (DBGetGlobal, DBSetGlobal)
Syntax: DBGetGlobal(<globalName>)
Syntax: DBSetGlobal(<globalName>,<value>)
New in 2.0: FileFlex uses the global variables of the host development
environment in a number of ways, including setting and retrieving the values
of host globals using DBWriteRec and DBGetCurrRecVal. As a result, the internal
FileFlex engine must be able to successfully interact with the global variables
of the host language. The functions DBGetGlobal and DBSetGlobal are not
designed for your use. Rather, they are functions that allow us to test
(in the validation suite) whether the global interface between FileFlex
and the host globals work successfully. There's really no good reason for
you to call DBGetGlobal yourself, for example, because it'll be much easier
for you to just put the global value into a container directly. But, if
you're curious how these functions are used, look at the movie scripts in
the validation suite.
[Previous Chapter] [Table of Contents] [Next Chapter]
Copyright (c) 1996 David Gewirtz under license to Component Software Corp.
All rights reserved worldwide.