User manual CRESTRON SW-DBM

[. . . ] CRESTRON e-controlTM Database Manager (SW-DBM) version 1. 4 © 1999 Crestron Electronics, Inc. Crestron e-control Database Manager SW-DBM Contents How to Use This Manual 3 A Note on Printing This Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Quick Start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Section Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Quick Start Guide: Address Book (demo1) Introduction 5 6 What is Crestron e-controlTM Database Manager?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [. . . ] The columns of the Tracks table include the track number, track name, and playing time of the track, and the Album ID of the album in the Albums table where the track is found. · The objective is to make all the album information available when querying for a track. Because each album can have a different number of tracks, you would not try to put all the track information in the album record. An alternative is to create separate records in the Albums table for each track. However, doing so requires duplicating all the album information in the records for each and every track on the album. The solution is to keep the tables as they are, and separate, but let the database engine appear to "join" them when you access the track table. We do this by indicating the tables to join, and the "pivot field, " the field they have in common that ties the information from the superior table to each and every record in the subordinate table. In this case, the pivot field is the Album ID. This join creates a recordset with a record for each track that contains additional columns for selected fields from the Albums table. In the following figure, the album title appears in each track record. A portion of the results of a join query viewed from Microsoft Access Following is a Queries table record that might produce the above query: Hypothetical Queries table record, viewable from either the server or from Microsoft Access. NOTE: The list fields, also included in the generated query, are not shown in the results above. The value in the table column shown above specifies that matching rows for the Albums and Tracks table are those rows that have the same values in the CDJID column (A. CDJID = T. CDJID). This is an INNER JOIN, meaning that only rows with a match in both tables are retrieved and displayed. If an album in the Albums table has no matching track rows in the Tracks table, that album is not included in the records retrieved and sent to the touchscreen. Similarly, if tracks exist with no matching rows in the Albums table, they are also ignored. The SQL statement constructed from this row of the Queries table is: SELECT T. Track, T. Title, A. Artist AS ARTIST, A. Title AS ALBUM, T. Track AS CUT, T. Title AS TUNE, 34 · The Database File Installation & Reference Guide -- DOC. 5823 Crestron e-control Database Manager T. Minutes AS ["], T. Seconds AS ['] FROM Albums A INNER JOIN Tracks T ON A. CDJID = T. CDJID SW-DBM Note the following syntax features of SQL, all illustrated in the above example: · Dot syntax. When the same field name is used in more than one table, you must use the syntax table. field to eliminate ambiguity. In the above example, this syntax was used throughout, although in fact the fields Artist and Track did not actually appear in both tables. The FROM clause assigns the alias A to the Albums table and T to the Tracks table. Once these aliases are established, you can use them instead of the full table names in the listFields and dataFields columns. Use the word AS after a field name in the SELECT clause to introduce a caption. Unlike a table name alias, you cannot refer to the field using its caption. Captions, when defined, are displayed in place of the field name in reports generated with Microsoft Access. The server also uses captions during successive queries. [. . . ] The current scroller height is set to 8 rows. On the first page, 1/3 of the gauge is filled [value = 1/3 x 65535 = 21845]; on the second page, 2/3 of the gauge is filled [value = 2/3 x 65535 = 43690]; and on the third page, the entire gauge is filled [value = 3/3 x 65535 = 65535]. Line Mode shows progressed based on the first row on the first page through the last row on the last page. The formula the server uses to calculate the value is (p1) / (pps-1) x 65535. Example 2: Again, there are three pages. On the first page, the gauge is empty [value = (1-1)/(3-1) x 65535 = 0]; on the second page, 1/2 of the gauge is filled [value = (2-1)/(3-1) x 65535 = 32767]; and on the third page, the entire gauge is filled [value = (3-1)/(3-1) x 65535 = 65535]. If you find this confusing, note that it is simply a manifestation of the classic "fence post problem. " The easiest way to think about this is to consider a certain length of fencing to represent the length of the recordset. [. . . ]