Crestron
e-control Database Manager SW-DBM
33
••
The Database File
Installation & Reference Guide — DOC. 5823
The control system accesses a database table using a scroller signal block. More than
one touchscreen can browse a table at the same time; one user’s view does not affect
any other user’s view of the table rows displayed in the scroller. However, if the
application allows editing of the database from the touchscreen (as shown in demo1),
one user can conceivably change a row while another user is viewing the old version
of the row. If the second user also attempts to edit the row, a message indicating that
the row has been changed is displayed and the user has a choice of overwriting the
previous changes, discarding the new changes, or copying the new changes to the
clipboard.
Once you have one or more database tables, each database scroller needs to select at
least one table and identify the columns to display on the touchscreen. The set of
instructions for selecting tables and columns is called a query.
Queries
In Microsoft Access, the language for constructing queries is called SQL (Structured
Query Language).
An SQL query contains all of the information needed to select rows for display: the
name of the table or tables to read, the names of the columns to retrieve, and , if you
do not want to retrieve every row of a table, it specifies a logical test for determining
which rows to retrieve.
For example, consider the Addresses table shown above, which contains names,
addresses, and phone numbers for Crestron offices.
Assume that you want to display the names and address columns for all offices in the
USA. In SQL, the query would be:
SELECT Company, Address, City, State, PostalCode
FROM Addresses
WHERE (Country="USA");
This query retrieves the following answer set:
The SELECT clause of the query lists the columns to be retrieved. While this query
identifies the table name for each column prefixed to the column name, in this case
only one table is being accessed, so the column names alone would have been
sufficient. If two tables were being accessed (demonstrated in demo2), you would
need to identify the table name with the column name. If you do this, you can assign
a shorter alias name to each table. This technique is also demonstrated in Demo2.
The FROM clause of the query identifies the table or tables to be accessed.
The WHERE clause of the query indicated any conditions you want each row to pass
in order to be retrieved.
Joins: Accessing Multiple Tables
One table may not hold all of the data you need for your application. Consider the
query used by Demo2. The “recordset” (logical table) which results from this query
contains fields (columns) from two actual tables:
