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:

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:

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:

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