Theme:
v
APL+Win Training Pack 10

Developing a Relational Database in APL+Win (part 2)

Download APL+Win Training Pack 10 (565 K)

This APL+Win Training Pack delivers many more APL+Win Relational Database tools.

The chore of the Relational Database Engine which we started to develop in the previous lesson is now complete.

With the August Training, you will get the following new tools:

  • enhanced data dictionary commands (List Tables, List Columns)
  • a cascading Delete command to logically remove records from tables (Delete)
  • a command to restore logically removed records (Undelete)
  • a command to physically remove logically deleted records (Pack)
  • a search tool to locate records in any table (Iota)
  • 2 low level data extracting utilities (Compress and Index)
  • a general query command allowing you to easily extract data from multiple tables in one run  (Join)
  • a command to batch update records in any table (Compress ... Assign)
  • a command to update individual table cells (Index ... Assign)
  • commands to move tables and columns in the data dictionary (Move Table ..., Move Column ...)
  • commands to rename tables and/or columns in your database (Rename Table ..., Rename Column ...)
  • a reverse engineering command to build a Make function to recreate your database (MakeDatabase)
  • a command to copy a table structure into a new table (Copy Table ...)
  • a Load command to create dynamic cascading input forms (Load)

As an example, here is a complex relational database query:

      Frame Join ordDate cliName supName proName 'orlQty×orlUnipr' From orl SortedBy proName Where stoNum Eq'11'
---- ---------- -------- -------------- -------------------- ---------------
          Order Supplier Product  
Inds       Date Name     Name           Name                           expr1
---- ---------- -------- -------------- -------------------- ---------------
  20 1994/06/08 SWAIN    JUMANTEC CORP. BLACKLEAD PENCIL               15.00
  24 1994/06/08 DRESSLER JUMANTEC CORP. BLACKLEAD PENCIL               40.00
  23 1994/06/08 SWAIN    JUMANTEC CORP. FOUNTAIN-PEN                   15.00
  16 1994/06/06 GARNER   EQUIP2000 INC. LETTER-WEIGHT 1-500           200.00
  21 1994/06/08 SWAIN    JUMANTEC CORP. PEN                            15.00
  22 1994/06/08 SWAIN    JUMANTEC CORP. PENCIL                         15.00
  25 1994/06/08 DRESSLER JUMANTEC CORP. PENCIL                         40.00
  18 1994/06/07 DUXLER   ELECTROLAMP    RULE                          149.00
 15 1994/06/06 GARNER    ELECTROLAMP    SCISSORS                      149.00
 28 1994/06/10 DRESSLER  ROULAND CORP.  STAMP                          30.00
---- ---------- -------- -------------- ------------------- ----------------
        ordDate cliName  supName        proName              orlqty×orlunipr
---- ---------- -------- -------------- ------------------- ----------------

The above command executes almost instantaneously: it performs a Join between 5 different tables (orders [ord], clients [cli], suppliers [sup], products [pro] and order details [orl]) while extracting data based on a selection in a sixth table (stores [sto]). It also reports a calculated field (orlQty x orlUnipr) i.e. the quantity ordered times unit price) and sorts extracted data by product names.

With the Join command you can extract any combination of fields from any set of tables in your database, based on any selection criteria from any table in a natural manner close to natural language. The delivered database engine finds the table connections to use by itself.

August 1997 also delivers a powerful Load command for performing cascading relational input with dynamically created forms.

With Load you get an immediate sophisticated interface to enter data in your tables for any database you create.

Example

:
      Load orl

This form is dynamically created by the the Load command. Fields which are in Bold correspond to Not Null columns. Fields which are noted within brackets are linked to other tables.

When in the [ Ordered Product ] field for example, press Alt+Enter and a menu showing the Primary Keys of the products table is displayed to let you select a product:

When you type letters in the toolbar edit field the menu list gets automatically restricted to those items containing the edit field sub-string.

If for some reason, the ordered product is not yet in the products table, simply click on the top left button and the products input form is created and displayed on the fly:

So you can create a new record in the products table an return later to your initial input form. Note that from the products input form you could hit Alt+Enter in the [ Product Supplier ] field to select a supplier and maybe add a new supplier to the suppliers table.

This is cascading relational input.

We hope you will enjoy this APL+Win Training Pack and that it will help you in your application developments.


Pack 10 Content

File Description
BUTTONS.BMP More than 100 images for your toolbar buttons.
DISK10.DOC An 73 pages Winword document containing Chapter 19 of the APL+Win Training.
TOOLKIT.W3 The second part of an APL+Win Relational Database Engine.
TOOLKIT.INI The Relational Database .INI file.