The Retrieval object retrieves data from the specified database resource, according to the user-supplied selection criteria, and makes the data available to the Procedure.

In relational databases, accessing data in the database resources (tables or views) is done through the agency of the Structured Query Language (SQL); this language provides instructions for searching, updating, adding and deleting records (rows) in the database tables, following selection criteria encoded in SQL statements.

The Retrieval object encapsulates the SQL query into a graphical shell that hides the intricacies of SQL programming from the end user; in its simplest form it takes the names of the database resource (table or view) and fields (table/view columns) to be retrieved, optionally adds selection criteria to narrow down the search, and executes the query.

When you introduce the Retrieval object into the Workspace from the Palette, it will be a simple object with two fields: the first field will accept the name of the target database table by selecting or drag-dropping it into the object; the second field (in the Get Field section) will receive the field name to be retrieved from the stated database table.

When the instructions associated with the Retrieval object are executed, it searches the target database table for matching records, according to the selection criteria and loads all found data for the specified fields into internal memory. These accumulated records are then loaded into the procedure variables (of Database field kind) incrementally, one record at a time in a looping sequence, controlled by the flowchart.

To utilize a Retrieval object, follow these simple steps:

  1. drag a Retrieval object into the Workspace;
  2. decide on the number of fields to be retrieved. If the number exceeds one field (the default), grab the bottom center control point and pull down until you have the number of desired slots:

  1. select or drag-drop the desired Database Source from the Palette into the Retrieval object (this will display the available fields in the Database Fields column of the Palette's Main resources view;
  2. select or drag-drop the desired Database Source Fields (this will insert them into the Get Field area of the Retrieval object);
  3. with the Retrieval object still selected, make your specifications in the Inspector. This involves four views:

Row processing


Search

The Search view of the Inspector acts as an additional filter, if desirable, for the Retrieval object (in the Workspace) which specifies the target database table and its specific field(s) for retrieval.

The Search view consists of a single scrollable view which shows all available fields, both user-defined and system fields, for the database table specified in the Retrieval object. To include a field in the Search, select the desired field and drag the field tile above the text header Drag fields above this line. Once dragging is complete, the field tile will display its four component parts:

See a more detailed explanation of some of the components of the Inspector's Search view (as well as Sort and Expert)

Sort

Drag the desired field(s) above Drag fields above this line and set each line to ascending (arrow button up) or descending (arrow button down) order for simple or hierarchical sorts.

Expert

The Expert view displays the WHERE clause of the SQL SELECT statement generated by the graphical settings established by the Search and/or the Sort. The scrollable view is editable and the WHERE clause can be:

In addition, the Lock table feature can be invoked

  1. connect the object(s) (i.e. the Retrieval object and possibly the Next row? Conditional object).

You are now done with the Retrieval object. When executed, it searches the target database table for matching records, according to the selection criteria and loads all found data for the specified fields into internal memory. These accumulated records are then loaded into the procedure variables (of Database field kind) incrementally, one record at a time in a looping sequence, controlled by the flowchart.

Go to:
Index
Table of Contents