# `dBase.opx` — Database handling

## Contents

- Overview

- Compatibility

- `DBADDFIELD:`
- `DBADDFIELDTRUNC:`
- `DBCREATEINDEX:`
- `DBDELETEKEY:`
- `DBDROPINDEX:`
- `DBGETFIELDCOUNT&:`
- `DBGETFIELDNAME$:`
- `DBGETFIELDTYPE&:`
- `DBISDAMAGED&:`
- `DBISUNIQUE&:`
- `DBMAKEUNIQUE:`
- `DBNEWKEY&:`
- `DBRECOVER:`
- `DBSETCOMPARISON:`

## Overview

To use this OPX, you must include the header file `dBase.oxh`, which contains the following declaration:

        DECLARE OPX DBASE,KUidOpxDBase&,KOpxDBaseVersion%
              DbAddField:(keyPtr&,fieldName$,order&) : 1
              DbAddFieldTrunc:(keyPtr&,fieldName$,order&,trunc&) : 2
              DbCreateIndex:(index$,keyPtr&,dbase$,table$) : 3
              DbDeleteKey:(keyPtr&) : 4
              DbDropIndex:(index$,dbase$,table$) : 5
              DbGetFieldCount&:(dbase$,table$) : 6
              DbGetFieldName$:(dbase$,table$,fieldNum&) : 7
              DbGetFieldType&:(dbase$,table$,fieldNum&) : 8
              DbIsDamaged&:(dbase$) : 9
              DbIsUnique&:(keyPtr&) : 10
              DbMakeUnique:(keyPtr&) : 11
              DbNewKey&: : 12
              DbRecover:(dbase$) : 13
              DbSetComparison:(KeyPtr&,comp&) : 14
        END DECLARE

### Compatibility

ER5 WINS OPXs are not useable on the ER3 EPOC Emulator, and ER3 WINS OPXs will not work on the ER5 Emulator. However, ER5 MARM OPXs will work on ER3 EPOC devices, and ER3 MARM OPXs will work under ER5.

## `DBADDFIELD:`

Usage: `DBADDFIELD:(key&,field$,order&)`

Adds the field, `field$`, to the key, `key&` (see `DBNEWKEY&:` for creating a new key). This new key can then be used to create an index on a table. `order&` specifies how this field should be ordered:

value | meaning | constant declaration in dBase.oxh
1 | ascending | `CONST KDbAscending&=1`
0 | descending | `CONST KDbDescending&=1`

You can use this procedure repeatedly to add more than one field to the same key. The order in which the fields are added dictates the significance of these fields in building up the index. The first field added to a key is the primary field; if there were any identical values in this field then the secondary field would be used and so on. For example, if the primary field in an ‘Address’ table was ‘Surname’ and there were two people with the surname Brown, then the secondary field or tertiary fields like ‘Forename’ and ‘Age’ might be used to define how the index will be ordered.

When using string fields in an index they cannot be longer than 240 bytes. To limit the size of a string field see `CREATE` under Keyword Reference. If a string field is the last field in a key (or first and only) then it may be truncated to a specified length - see `DBADDFIELDTRUNC:`.

Note that the size of an index can become huge if the key is long. The key length k is the length in bytes of the sum of all the fields in the key. An integer or a long integer field is 4 bytes, a floating-point field 8 bytes and a text field depends on the length assigned to it (see also the previous paragraph). The size of the index depends at least linearly on k and hence may be large if the key is long.

When the key has been built as required, it can be used to create an index.

See `DBCREATEINDEX:`.

## `DBADDFIELDTRUNC:`

Usage: `DBADDFIELDTRUNC:(key&,field$,order&,trunc&)`

Adds a truncated string field `field$` to the key `key&`. Only the last field added to a key (or first and only) can be truncated. `order&` specifies how this field should be ordered and may take the values:

value | meaning | constant declaration in dBase.oxh
1 | ascending | `CONST KDbAscending&=1`
0 | descending | `CONST KDbDescending&=1`

`trunc&` is the truncation length and determines how many bytes of the string field will be used in building up the index (up to 240).

This procedure is useful for building up indexes on OPL16 databases whose string fields have a set length of 255 bytes. When the key has been built up as required it can be used to create an index.

See `DBADDFIELD:`, `DBCREATEINDEX:`.

## `DBCREATEINDEX:`

Usage: `DBCREATEINDEX:(index$,key&,file$,table$)`

Creates an index with the name `index$` on the table `table$` in the database `file$`. The index is used for sorting and vastly increases the speed in table lookup. The index is based on `key&`, the supplied key. The database must be closed when this procedure is used.

The opening of an ordered view on a table must be requested in the SQL query in the OPEN command. If a suitable index has been created then it will be used.

See `DBNEWKEY:`, `DBADDFIELD:`, `DBADDFIELDTRUNC:`, `DBMAKEUNIQUE:`, `DBSETCOMPARISON:`, `DBDROPINDEX:`, `OPEN`, SQL specification.

## `DBDELETEKEY:`

Usage: `DBDELETEKEY:(key&)`

Deletes the key `key&`. This should be called as soon as the key is no longer required. Any keys left undeleted when all modules that declare or include a declaration of the Dbase OPX have been unloaded will be automatically deleted

See `DBNEWKEY&`.

## `DBDROPINDEX:`

Usage: `DBDROPINDEX:(index$,file$,table$)`

Drops the index `index$` of the table `table$` of the database `file$`. The database must be closed to use this procedure.

See `DBCREATEINDEX:`.

## `DBGETFIELDCOUNT&:`

Usage: `n&=DBGETFIELDCOUNT&:(dbase$,table$)`

Returns the number of fields in one of the records in the table `table$` of the database `dbase$`. This number can then be used to analyse the contents of the record.

See `DBGETFIELDNAME$:`, `DBGETFIELDTYPE&:`.

## `DBGETFIELDNAME$:`

Usage: `name$=DBGETFIELDNAME$:(dbase$,table$,fieldNum&)`

Returns the name of the field whose number is `fieldNum&` in the table `table$` of the database `dbase$`. This is useful for analysing the records of a table.

See `DBGETFIELDCOUNT&:`, `DBGETFIELDTYPE&:`.

## `DBGETFIELDTYPE&:`

Usage: `type&=DBGETFIELDTYPE&:(dbase$,table$,fieldNum&)`

Returns the type of the field whose number is `fieldNum&` in the table `table$` of the database `dbase$`. This is useful for analysing the records of a table.

The values that me be returned (many of which aren’t supported by OPL databases), are as follows:

value | type
0 | bit
1 | signed byte (8 bits)
1 | unsigned byte (8 bits)
2 | integer (16 bits)
3 | unsigned integer (16 bits)
4 | long integer (32 bits)
5 | unsigned long integer (32 bits)
6 | 64-bit integer
7 | single precision floating-point number (32 bits)
8 | double precision floating-point number (64 bits)
9 | date/time object
10 | ASCII text
11 | Unicode text
12 | Binary
13 | LongText8
14 | LongText16
15 | LongBinary

Types 2,4,8,10 correspond to OPL’s `%`,`&`, floating point and `$` types respectively.

See DBGETFIELDCOUNT&, DBGETFIELDNAME&

## `DBISDAMAGED&:`

Usage: `i&=DbIsDamaged&:(dbase$)`

Returns 1 if the database `dbase$` considers that it may have damaged indexes and 0 if not. If the database is damaged, then this does not make it unusable, but attempting to use any damaged index will result in an error. Recovering the database will restore any damaged indexes.

See `DBRECOVER:`.

## `DBISUNIQUE&:`

Usage: `i&=DBISUNIQUE&:(key&)`

Returns -1 if the key `key&` is unique or 0 if it is not.

See `DBMAKEUNIQUE:`.

## `DBMAKEUNIQUE:`

Usage: `DBMAKEUNIQUE:(key&)`

Sets the key `key&` to be unique. The index created will then not allow any records to be added if they exactly match the indexed fields of another record.

See `DBNEWKEY:`, `DBADDFIELD:`, `DBCREATEINDEX:`.

## `DBNEWKEY&:`

Usage: `k&=NEWKEY&:`

Returns a handle to a key which can be used for creating indexes.

See `DBDELETEKEY:`, `DBCREATEINDEX:`, `DBADDFIELD:`, `DBADDFIELDTRUNC:`.

## `DBRECOVER:`

Usage: `DBRECOVER:(dbase$)`

Recovers a damaged database `dbase$`, restoring any damaged indices.

See `DBISDAMAGED&:`.

## `DBSETCOMPARISON:`

Usage: `DBSETCOMPARISON:(key&,comp&)`

The text comparison is used to determine the order of an index. The argument `comp&` sets the text comparison mode of a key `key&`, and takes one of the constant values supplied in the header file `dBase.oxh`:

        CONST KDbCompareNormal&=0
        CONST KDbCompareFolded&=1
        CONST KDbCompareCollated&=2