dbpSort

Purpose:

Sort a table based on a specific field or group of fields. By default table records are usually displayed in the order that they were created. This action allows you to temporarily rearrange the records and display them in a different order. Fields can be sorted in either ascending or descending order. If an index for a sort field exists, it will be used automatically to speed the sort process.


Navigation actions such as dbpFirst, dbpPrev, dbpNext and dbpLast actions will display records according the sort order. To restore the original record order and remove the sort, use dbpUnSort.

Category:

Sort/Index

Syntax:

dbpSort "database id" "table" "sort info"

database id

The name assigned to the database.

table

The name of the table to sort.

sort info

The name of the field or group of fields to use for the sort. Each field name must be followed by an equal sign and a direction keyword (ASC for ascending order or DESC for descending order). If multiple fields are used they should be separated by semicolons (;). For example, the following will sort by State in ascending order (A-Z), then sort by City in descending order (Z-A):


"State=ASC;City=DESC"

Example:

dbpSort "AddrBook" "Contacts" "LastName=ASC"

SQL Equivalent:


SELECT * FROM table ORDER BY sort info


dbpUnSort

Purpose:

Remove the current sort criteria specified with dbpSort and display the table records in their original ordinal order.

Category:

Sort/Index

Syntax:

dbpUnSort "database id" "table"

database id

The name assigned to the database.

table

The name of the table to unsort.

Example:

dbpUnSort "AddrBook" "Contacts"

SQL Equivalent:

Usually a sort can be removed by simply performing another query. For example:


SELECT * FROM table


dbpGetIndexNames

Purpose:

Obtain a list of indexes associated with a table and store the results in a variable. Index names will separated by the specified delimiter.

Category:

Sort/Index

Syntax:

dbpGetIndexNames "database id" "table" "delimiter" "variable"

database id

The name assigned to the database.

table

The name of the table.

delimiter

The character or characters used to separate the index names.

variable

The name of the variable where the index names will be stored. Each index name will be separated by the specified delimiter.

Example:

dbpGetIndexNames "AddrBook" "Contacts" ";" "[Indexes]"

SQL Equivalent:


SHOW INDEX FROM table


dbpCreateIndex

Purpose:

Create an index to speed up sort and query operations.

Category:

Sort/Index

Syntax:

dbpCreateIndex "database id" "table" "fields" "index name" "options"

database id

The name assigned to the database.

table

The name of the table.

fields

A single field or list of fields to be used for the index. Separate multiple fields with semicolons (;).

index name

The name to be used for this index.

options

This is a compound parameter and can contain any combination of the following items:


PrimaryIndex=Yes/No

Yes = indexed field will become the primary key field for the table. A primary key field cannot be empty (NULL). Each table can have only one primary Index.

Unique=Yes/No

Yes = create a unique index. No = do not create a unique index. A unique index requires all values in the index must be different. An error occurs if you try to add a new record with a data that matches an existing record.


Separate multiple items in a compound variable with semicolons (;). This parameter may be left blank if you do not require either of these options.

Example:

dbpCreateIndex "AddrBook" "Contacts" "LastName;FirstName" "NameIdx" "PrimaryIndex=No;Unique=No"

SQL Equivalent:

Normally, indexes are created at the time the table itself is created, however the following command can be used to add indexes to existing tables:


CREATE INDEX index name ON table ( fields )


dbpDropIndex

Purpose:

Delete an existing table index.

Category:

Sort/Index

Syntax:

dbpDropIndex "database id" "table" "index name"

database id

The name assigned to the database.

table

The name of the table containing the index.

index name

The name of the index to be deleted.

Example:

dbpDropIndex "AddrBook" "Contacts" "NameIdx"

SQL Equivalent:


DROP INDEX index name