Variable

Description

dbobj%table

If specified, the table name used in an "action".  Otherwise, the name of dbobj will be used as the table name.

dbobj%keyfield

The name of the key field name.

The value of dbobj%keyfield is used by different actions in different ways:

select:         dbobj%keyfield is not used

update:        dbobj%keyfield, if specified, overwrites the record filter (the generated where-clause) with keyfield = 'keyValue' (the keyValue is from the matrix.)  Also, the key field and its corresponding value will be removed from the set-list to avoid the key field being updated.

insert:         dbobj%keyfield, if specified, its value will be disgarded and replaced with one generated by the underlying database platform.  If the database does not generate keys, it will be an error.

delete:         dbobj%keyfield, if specified, overwrites the record filter (the generated where-clause) with keyfield = 'keyValue' (the keyValue is from the first row of the matrix).  In this case, only one record can be deleted.

dbobj%mustfields

If specified, contains a list of compulsory fields.  A compulsory field is one that must appear in dbobj%fields.  The system will examine dbobj%fields and if there are compulsory fields missing, their names will be appended to dbobj%fields.

This feature is useful when some fields in the matrix might be missing (e.g. if the matrix receives its values from a form, and so happens that none of the checkboxes or radio entry of a particular column are checked by the user).  When such a "maxtrix" is processed by the "group" command, the missing fields will not be included into dbobj%fields at all, and the whole column will be ignored (instead of setting them all to the unchecked status.)

Therefore, it is recommended to include every checkbox and radio entry into dbobj%mustfields.

dbobj%wild

To avoid accidental operations on the entire table (e.g. due to programming or operational error), "update" and "delete" will return an error if there is neither a key field nor a where-clause implicit (record filter) or explicit (dbobj%where).

By specifying dbobj%wild to be true (non-null and non-zero), you overwrite this protection and allow operation to be carried out for the entire table in one go without a where-clause and without a key field being specified.

For "select", selecting multiple tables without a where-clause will result in an error (to protect the platform from being overloaded with a huge record set) unless dbobj%wild is true.  Selecting on a single table without a where-clause is valid, regardless of the value of dbobj%wild.

dbobj%wild has no effect if the "query" parameter is specified in the "database" command.  (You can do anything with a "query".)

dbobj%orderby

The "order by" clause without the word "order by".  It is a comma-delimited list of field names, each is optionally followed by "desc" to indicate a reverse sorting order.

This variable is used only for data retrieval operation.

(If multiple tables are involved, please make sure the table name qualifiers and a dot is included in each field to be sorted.)

dbobj%match:matchName

If specified, contains a search criterion named matchName.  The criterion is in the format of matchType:matchField, where matchField is the field in the table to match (default is matchName), and matchType is one of these:

equal     Field content is equal to the key

substr   Field content contains the key as a substring

regexp   Field content matches the regular expression represented by the key

min       Field content is equal to or higher than the key

max      Field content is equal to or lower than the key

minx     Field content is higher than the key

maxx    Field content is lower than the key

dbobj%search:matchName

If specified, contains the key for match criterion matchName.  The value of the key is to be used to build the record filter (the generated where-clause).  e.g. If "dbobj%match:CanAfford" contains "max:Price" and "dbobj%search:CanAfford" contains "1000", the where-clause will contain the condition "Price <= 1000".  All these conditions are "and"ed together to form the where-clause.

If dbobj%match:matchName does not exist, it is assumed to be "equal".  This simplify the process: setting dbobj%search:field to value generate a criterion of field = 'value'.

Usually, the dbobj%search variable copies the values from the search form via sys%form as in:
var dbobj%search = "(var)sys%form";

dbobj%where

If specified, will be taken as the first condition in the record filter (the generated where-clause).  i.e. the condition contained in dbobj%where will be "and"ed to the generated where-clause from the left.

dbobj%selectfrom

If specified, overwrites the field list (dbobj%fields) and table (dbobj%table).

The variable dbobj%selectfrom is useful to join multiple tables in a "select" operation.  In that case, the dbobj%where variable needs to contain the conditions that link the tables together.  That is why the variable dbobj%selectfrom is effective only if dbobj%where is specified (unless dbobj%wild is true, which is a dangerous and useless things to do as the joining will become an unrestricted crossing of all the tables involved, and is likely to end up timing out.)

dbobj%encode:field

If specified, contains a BEE Conversion to apply to the value to be written to Field field, or the value to be used in the record filter (the where-clause).  e.g.
var friend%encode:Birthday = "strtotime";

dbobj%decode:field

If specified, contains a BEE Conversion to apply to the value read from Field field.  e.g.
var friend%decode:Birthday = "strftime:%Y-%m-%d";

dbobj%quote:field

If specified, contains a quotation mark to use when generating the query for Field field.  The default is single quotation mark (').  Set it to blank if no quotation mark is required.  e.g. with mSQL
var friend%quote:ID = "";

dbobj%fieldnames

If specified, the elements will be used as keys of the record fetched from the record set. This is useful if you want to overwrite the field names defined in the database.

The list is position sensitive as the first element will be used as the key for the first fetched field, the second element as the key for the second field and so on.

In multiple table joining, the same field names may exist in different tables.  In such case, dbobj%fieldnames is the only way to distinguish them by giving each field a distinct name.

dbobj%fieldnames are used at the time of reading the database result (via the (db)dbobj cast), not at the time of "query".  That means you can modify dbobj%fieldnames even after reading has started.  This will cause subsequent readings to bear different fieldnames.