scl database extensions
The SCL database extensions permit authors to write scripts that interact with the contents of a database by querying or modifying the contents of the database tables. It is also possible to determine the structure (the tables and fields) of a database. It is not possible though to modify the structure of the database using these SCL commands. If you need to modify the database structure then you should use the existing functions on the database menu to add or delete tables and columns.
These SCL commands require that a database is currently opened. This can be achieved by using the SclOpenDatabase command which returns a handle to the opened database or by obtaining the handle to a database that has previously been opened by using menu functions to open and work with the database.
Features of data that have been organised into a database include:
- A database will contain 1 or more tables.
- Each table will contain 1 or more columns (sometimes called fields).
- Each column has an associated data type, character, integer or real (a floating point number). The datatype of a field determines the type of data that can stored in that column.
- Each table contains zero or more rows of data. All rows in a table contain the same columns of data although it is permissible for columns to record no data, provided that column has been defined as permitting a NULL value to be recorded when the database was defined.
- A NULL value signifies the absence of any value at all. For character fields a NULL value is essentially indistinguishable from a character string that is zero characters in length. For integer and real fields a NULL value has no equivalent numeric representation. For convenience though, if a NULL value is retrieved from an integer or real column for a row in a table, the numeric value returned is always -1.
- These SCL extensions permit you to perform query, insert, update and delete operations on a database. These operations provide all the flexibility required to work with the data recorded in a database.
- An important concept when working with database tables is that of the order of the rows in the table. While there is a physical order in which
the rows are stored, the order in which the rows are retrieved when using a query operation is dependent on the ordering applied to the query. For
most database types this is in turn dependent on the indexes that have previously defined for the table. The database implementation in Surpac
requires that if rows are to be retrieved in a particular order then an index must exist on the row/s that define that order. When defining the
query the order in which the rows are to be retrieved must be defined explicitly to achieve the required outcome.
Ordering is important because it guarantees rows will be retrieved in a consistent order which is sometimes essential for processing algorithms to work correctly. For example, most drill hole compositing algorithms require that the rows for a drillhole be retrieved in asceding order of hole depth.
- Query, update and delete operations on database tables may by restricted to a subset of the rows in a table by applying constrains to a query.
- Database objects,i.e. databases, tables, fields, rows, queries, inserts, updates and deletes are all referenced by using variable to which has been assigned a handle to the object or operation.