A database component allows you to create and organize several pieces of
data. It can store this information permanently so you can call upon it to
use in future programs.
Database Introduction
Follow these steps to access data in a database:
-
First, you must open or create a database. A database component provides
a way for your program to access information in a database file. In general,
we will use the term "database" to describe both the database component and
the data it manipulates. To open or create a database, use the OpenDatabase(
) or CreateDatabase( ) action.
-
A database is organized into records. You may access a record by its index
within the database, or by searching for an index which matches some criterion.
To get a record by index, use the GetRecord( ) action; to search
for a record, use the SearchString( ) or SearchNumber( )
action. When you access a record, it becomes the database's current record.
There are a number of actions which allow you to access and change the current
record.
-
Each record of the database consists of a number of fields.
Each field has a name. An address list database might have "Name," "Address,"
and "Telephone" fields. You may get or set the record's value for any of
these fields by means of the GetField( ) or SetField( )
actions. To commit changes you make, be sure to call the
PutRecord( ) action before making another record the current
record.
-
When you're done accessing records, call the CloseDatabase( )
action to close the database.
Dates and Times in Databases
The Database component makes it easy to store simple values like integers
and strings, and complex values like graphics. However, it does not have
a good way to store STRUCTs.
-
Most applications that work with dates and times will use the Date and TimeOfDay
STRUCTs, and benefit from storing such values in the database, and sorting
based upon those values.
-
Also, it is possible to set up a Database to use a "timestamp" function so
that it notes the date and time each record was last changed.
For these reasons, some special routines have been set up which convert dates
and times to and from numerical values. This simplifies sorting and comparison
of dates and times, which is useful if you're searching the database.
The routines, which are in the system module, are called
DateTimeToLong( ) , LongToDateTime( ), DateToInteger( ),
IntegerToDate( ), TimeToLong( ), and
LongToTime( ). Especially important among these is the
LongToDateTime( ) subroutine, which can decode a timestamp value
into a date and time.
Database Properties
Standard Properties: class, parent, proto, version.
-
databaseName string
-
This read-only property is the name of the database. The name must be a valid
filename.
-
fieldCategories[numFields] integer (0-5)
-
This property is a list containing a category for each field of the database.
The following categories are available:
-
0 No category
-
1 Name
-
2 Date
-
3 Telephone
-
4 Street Address
-
5 E-mail Address
Assigning an out-of-range value to an element of this array results in a
run-time error.
-
fieldNames[numFields] string
-
This read-only property contains the list of fields defined in the database.
-
fieldTypes[numFields] string
-
The type for each field defined in the database. The following types are
available:
-
"integer" integer
-
"long" long
-
"float" float
-
"string" string
-
"ID" Record ID special type. There may be at most one field of this type;
it may only be created by setting the proper flags when invoking the
CreateDatabase( ) action.
-
"TIMESTAMP" Timestamp special type. There may be at most one field of this
type; it may only be created by setting the proper flags when invoking the
CreateDatabase( ) action.
-
"anything else" complex
If you use a field type string not assigned to some type, the Database component
will assume the data type is complex. Thus, you might use "array" for array
data and "beephonk" for sound data.
-
nextRecordID long (0-...)
-
This read-only property is the record ID which will be assigned to the next
new record (assuming that the current database uses record ID numbers.)
-
numFields integer (0 -255)
-
This read-only property is the number of fields defined in the database.
-
numRecords integer (0 - ...)
-
This read-only property is the number of records in the database.
-
record integer (-2 - numRecords-1)
-
This read-only property is the number of the record currently being accessed.
-
If this number is -1, then a new and/or invalid record is being processed,
a record that is not yet part of the database's committed collection.
-
If this number is -2, then there is no current record.
-
recordID long (-1 - ...)
-
This is the permanent ID number for the database record; while a record's
record number may change, its recordID never will. If the recordID
is -1, then the current record is new or invalid.
Database Events
None.
Database Actions
AddField( )
AddField( fieldName AS string, type AS string, category AS integer
) AS integer
Adds a new field to the database. If the operation fails, an error number
will be returned.
Passed:
-
fieldName string
-
The name for the new field.
-
type string
-
The type of the new field. See the documentation for the fieldTypes
properties for allowed strings. You may not add a field of type "ID"
or "TIMESTAMP" after you've added records to the database.
-
category integer (0-5)
-
The field category of the new field. This value corresponds to the
fieldCategories value for this field:
-
0 No category
-
1 Name
-
2 Date
-
3 Telephone
-
4 Street Address
-
5 E-mail Address
CloseDatabase( )
CloseDatabase( ) AS integer
Closes the database. If the operation fails, an error number will be returned.
CreateDatabase( )
CreateDatabase( name AS string, flags AS integer, numKeyFields AS
integer, keyfieldNames[] AS string, keyFieldTypes[] AS string,
keyFieldSortOrder[] AS integer, keyFieldCategories[] AS integer ) AS
integer
Creates and opens a database.
Passed:
-
name string
-
The name of the database to create.
-
flags integer
-
This variable allows you to specify details about the new database. Add together
the numbers corresponding to the desired features:
-
8 timestamp: Indicates the database should have a single special field
of type "TIMESTAMP" called "Timestamp." This field is automatically created
and updated for new and changed records. If your BASIC code explicitly alters
this field, then it will not be updated when the record is updated.
-
4 record ID: Indicates that the databases should have a single special
field of type "ID" called "RecordID." This field is automatically created
and updated for new records. If the field is manually modified (such as for
synchronizing two databases), it will not be automatically set. This field
cannot be deleted.
-
1: This flag is not supported.
-
numKeyFields integer(0-1)
-
Number of key fields.
-
keyfieldNames[] string
-
An array of names of the fields that make up the key. You may not use the
Timestamp or RecordID fields as the key. You may add non-key fields to the
database after creation by means of the AddField( ) action.
-
keyfieldTypes[] string
-
An array of type strings of the fields that make up the key. See the
documentation for the fieldType property for a list of supported field
types. The following values are illegal: "variant," "complex," "ID," "TIMESTAMP."
-
keySortOrder[] integer
-
This number is ignored; only ascending sorts are supported.
-
keyFieldCategories[] integer
-
The category for each key field. This corresponds to the fieldCategories
property for these fields. The following field category numbers are valid:
-
0 No category
-
1 Name
-
2 Date
-
3 Telephone
-
4 Street Address
-
5 E-mail Address
DeleteDatabase( )
DeleteDatabase( name AS string ) AS integer
Deletes a database. This action returns a non-zero value if the operation
fails.
You may not delete an open database; if you have the database open, close
it (using the CloseDatabase( ) action) before attempting to delete
it.
Passed:
-
name string
-
The name of the database to delete.
DeleteField( )
DeleteField( fieldName AS string ) AS integer
Deletes the specified field from the database. The field will be removed
from all records in the database. You may not delete a RecordID or TIMESTAMP
field.
The numFields property does not change, and the remaining fields will
not move in the field...[] properties. If you try to get the name,
category, etc. of the deleted field, there will be an error.
Passed:
-
fieldName string
-
The name of the field to delete.
DeleteRecord( )
DeleteRecord( ) AS integer
Deletes the current record from the database. Specifically:
-
The current record is deleted.
-
The record property becomes -1 to show that the current record data belongs
to a "new" record.
GetField( )
GetField( fieldName AS string ) AS ...
Retrieves the value of the specified field from the current record.
Passed:
-
fieldName string
-
Which field to retrieve.
GetRecord( )
GetRecord( recordNum AS long, isRecordID AS integer) AS integer
Makes the specified record the current record. The action returns a non-zero
number if the operation fails.
This will lose all unsaved changes to the current record--to save those changes,
use the PutRecord( ) action.
To get a new record, use the NewRecord( ) action.
Passed:
-
recordNum long
-
The number of the record to retrieve; or the RecordID of the record to retrieve
if isRecordID is non-zero.
-
isRecordID integer
-
If this flag is zero, then recordNum will be treated as a record number;
if this flag is non-zero, the recordNum will be treated as a RecordID.
NewRecord( )
NewRecord( ) AS integer
Creates a new record.
The new record will have record number -1; if you save this new record
via the PutRecord( ) action, it will be given a proper number.
This action loses all unsaved changes to the current record--you can use
the PutRecord( ) action to save those changes.
OpenDatabase( )
OpenDatabase( name AS string, flags AS integer) AS integer
Opens a database. This action will fail if the component already has a database
open; close it first with the CloseDatabase( ) action.
Passed:
-
name string
-
The name of the database to open.
-
flags integer
-
This number indicates whether we will get write access to the database and
whether we will share it. Add together the numbers that apply:
-
2 Write access: Set this flag if you will modify the database's data.
-
1: This option is not available.
PutField( )
PutField( fieldName AS string, value AS ... ) AS integer
Changes the value of a field of the current record.
To save the changed record, use the PutRecord( ) action.
Passed:
-
fieldName string
-
The field to change.
-
value
-
The new value for the field.
PutRecord( )
PutRecord( ) AS integer
Commits the current record to the database. The action returns a non-zero
number if the operation fails.
If this is a new record, it will be assigned a record number. If the record
has a TIMESTAMP field, that field will be updated with the current time.
PutRecordNoUpdate( )
PutRecord( ) AS integer
Commits the current record to the database. Unlike the PutRecord( )
action, this action will not affect the record's TIMESTAMP field, if
any. The action returns a non-zero number if the operation fails.
RenameDatabase( )
RenameDatabase( name AS string ) AS integer
Renames the current database. If this operation fails (perhaps there is already
another database with this name), an error number will be returned.
Passed:
-
name string
-
The new name for the database.
RenameField( )
RenameField( oldName AS string, newName AS string ) AS
integer
Renames one of the database's fields. You cannot rename a RecordID or TIMESTAMP
field.
Passed:
-
oldName string
-
The old name of the field.
-
newName string
-
The new name for the field.
SearchNumber( )
SearchNumber( fieldName AS string, value AS ..., flags AS integer,
maxRecords AS integer ) AS integer
This will search through the database for a record whose number in the specified
field matches the specified number. The action returns a non-zero number
if the operation fails, or if the search was terminated early because of
a non-zero maxRecords argument. (If the maximum records to search
limit was reached, the search will cease.)
The search starts from the current record.
-
If the search was successful, the matching record will become the current
record; any unsaved changes to the old current record will be lost.
-
If the search was not successful, then the current record will be whatever
it was before the search--if you're just searching a few records at a time
(passing a non-zero maxRecords), you'll want to go forward a few records
before searching the next batch.
Passed:
-
fieldName string
-
The field that should match.
-
value integer, long, or float
-
The number to match.
-
flags integer (0-1)
-
This flag determines the direction of the search: zero means forward, one
means backwards.
-
maxRecords integer
-
The maximum number of records to search. Pass zero to search all the records.
-
Limiting the number of records allows the search to be interrupted for larger
databases; allowing the program to give the user update information and not
hog access to the database.
If the search searches maxRecords records without finding a match,
it will update the current record to be the last record searched, so that
you may continue the search later.
SearchString( )
SearchString( fieldName AS string, matchString AS string, flags AS
integer, maxRecords AS integer ) AS integer
This searches for a record whose field fieldName contains the string
matchString. The search starts from the current record.
-
If a matching record is found, it will be made the current record (and all
unsaved changes to the current record will be lost). The action returns a
non-zero number if the operation fails or if the search terminates early
due to a non-zero maxRecords argument.
The search starts from the current record.
-
If the search was successful, the matching record will become the current
record; any unsaved changes to the old current record will be lost.
-
If the search was not successful, then the current record will remain what
it was before the search--if you're just searching a few records at a time
(passing a non-zero maxRecords), you'll want to go forward a few records
before searching the next batch.
Passed:
-
fieldName string
-
The field to search; pass "" to search all fields of type "string."
-
matchString string
-
The string to search for.
-
flags integer
-
Flags allowing you to fine-tune your search. To compute your flags number,
add together the appropriate numbers below. For example, for a forward,
case-sensitive search, where the field string had to match the matchString
(not just contain it), you would pass 7 = 0 (Forward) + 4 (case-sensitive)
+ 2 (No * at start) + 1 (No * at end)
-
0 Search Forward, Case insensitive, Partial start: * at start, Partial end:
* at end
-
8 Search Backwards
-
2 No partial start: field string must start with matchString.
-
1 No partial end: field string must end with matchString.
-
maxRecords integer
-
The maximum number of records to search. Pass zero to search all the records.
Limiting the number of records allows the search to be interrupted for larger
databases; allowing the program to give the user update information and not
hog access to the database.
If the search searches maxRecords records without finding a match, it will
update the current record to be the last record searched, so that you may
continue the search later.