Data Access Actions

Description

select
search
get
read
retrieve

Read from the database.

The system will first check whether dbobj%selectform is specified and the select range is restricted by dbobj%where (unless dbobj%wild is true).  If so, they will be used to generate the SQL query statement.

Otherwise, the generated SQL statement will be a select on the table defined in dbobj%table on the fields in dbobj%fields.  If dbobj%table is not specified, the name of dbobj will be used as the table name.  If dbobj%fields is not specified, all fields ("*") will be retrieved.

Whether "selectfrom" is used or not, the key values in dbobj%search will be used to generate the where-clause (and "and"ed after dbobj%where if one is specified,) and dbobj%orderby will be used in the "order by" clause of the generated SQL statement.

The key values contained in dbobj%search will be properly encoded with the BEE Conversions in dbobj%encode before being used to generate the where-clause.

The SQL statement will be submitted to the database engine and the retrieved record set can be fetched via "var" commands or a "foreach" loop, after the BEE Conversions in dbobj%decode are applied to their corresponding fields.

update
change
modify

Update the database.

The update will be on the table defined in dbobj%table.  If dbobj%table is not specified, the name of dbobj will be used as the table name.

The system will get the fields to be updated from dbobj%fields, appended with field names in dbobj%mustfields (if specified) for those not in dbobj%fields already.  Then dbobj%keys will be looped through and an SQL update statement will be generated for each element in dbobj%keys, based on the values in the matrix.  (Please see the "matrix" parameter under the "database" command.)

All the field values used in the SQL update statement will be properly encoded using the corresponding BEE Conversions in dbobj%encode.

If dbobj%keyfield is specified, the corresponding key field will NOT be updated, and the generated where-clause will be ignored.  Instead, the value of the key field found in the matrix will be used to find the record to update.

If dbobj%keyfield is not specified, the search criteria in dbobj%search will be used to generate the where-clause used in the SQL update statement.

Please note that when updating multiple records from a matrix, it is important to always specify dbobj%keyfield.  This way, the system will generate multiple SQL update statements, each update only one record with "where keyfield = ...", which make sure the corresponding row on the matrix is being updated.

To avoid updating the entire table by mistake, if the SQL update statement is found to have no where-clause, the system will return an error (unless dbobj%wild is true).

insert
put
add

Add a new record into the database.

The new record will be inserted into the table defined in dbobj%table.  If dbobj%table is not specified, the name of dbobj will be used as the table name.

The system will get the fields to be included in the new record from dbobj%fields, appended with field names in dbobj%mustfields (if specified) for those not in dbobj%fields already.  Then dbobj%keys will be looped through and an SQL insert statement will be generated for each element in dbobj%keys, based on the values in the matrix.  (Please see the "matrix" parameter under the "database" command.)

All the field values used in the SQL insert statement will be properly encoded using the corresponding BEE Conversions in dbobj%encode.

The last sequence number used with the database object will be checked (assuming that the database platform will not change its value by mere checking.)

If dbobj%keyfield is specified and the last sequence number can be obtained from the database platform, the system will assume that the database platform is capable of generating the key value internally and therefore would not bother to include the key field in the inserted record.

However, if the last sequence number is not available, the system will try to obtain the next sequence number from the database platform for the key value.  If that cannot be obtained, it will be an error.  In another word, if the database platform is not capable of generating either last sequence or next sequence, dbobj%keyfield should not be specified at all.

Note: If dbobj%keyfield is specified in an "insert" operation, the key field value should be available in the matrix (even as a dummy value) in order to trigger the key generation process.

delete
erase
remove

Delete a record or records from the database.

The record will be deleted from the table defined in dbobj%table.  If dbobj%table is not specified, the name of dbobj will be used as the table name.

If dbobj%keyfield is specified, the corresponding key field of the first row of the matrix will be used to identify the record to be deleted.  (The second row and onwards in the matrix are ignored in the "delete" data access action.)

If dbobj%keyfield is not specified, the search criteria in dbobj%search will be used to generate the where-clause used in the SQL delete statement.

To avoid deleting the entire table by mistake, if the SQL delete statement is found to have no where-clause, the system will return an error (unless dbobj%wild is true).

Please note that only one "delete" SQL will be executed.  For deletion via the matrix, only one record will be deleted.  For deletion using the search criteria in dbobj%search (when dbobj%keyfield is not specified), multiple record can be deleted.  Deleting without a where-clause (deleting the entire table) is possible only if dbobj%wild is true.