Database

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.

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.

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.

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:
DeleteDatabase()

0

No category

1

Name

2

Date

3

Telephone

4

Street Address

5

E-mail Address

DeleteDatabase( name AS string ) AS integer

Deletes a database. This action returns a non-zero value 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.

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.

8 Search Backwards.

0 Case insensitive

4 Case sensitive

0 Partial start: * at start

2 No partial start: field string must start with matchString.

0 Partial end: * at end

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.