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:

3. 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;
4. select or drag-drop the desired Database Source Fields (this will insert them into the Get Field area of the Retrieval object);
5. with the Retrieval object still selected, make your specifications in the Inspector. This involves four views:

Row processing

• Multiple rows (all)
  requires use of Next row? Conditional object* (drag from Inspector);
  • returns all rows of data as defined by the Retrieval object and based upon the Search criteria view;
  • stores them in memory as a stack of data in the specified Sort order, if any; and
  • steps through this stack of data, one at a time, from the top down.
• Multiple rows (distinct only)
  requires use of Next row? Conditional object* (drag from Inspector);
  • returns all rows of data as defined by the Retrieval object and based upon the Search criteria view (but does not include any duplicate data as based upon the fields being retrieved);
  • stores them in memory as a stack of data in the specified Sort order, if any; and
  • steps through this stack of data, one at a time, from the top down.
• Single row**
  does not make use of the Next row? Conditional object. Attempts to return a single row of data based upon the Retrieval object and Inspector specifications. To test for success of retrieval, you can connect a Condition object and ask: X==EMPTY (where X is one of the fields being called for and EMPTY is a Constant in the Local Resources area of the Palette)

  The Row Processing view also displays the:

  Stop & Show
  (a Retrieval feature available for use with Multiple Rows (all) and Multiple Rows (distinct only))

  returns all rows of data as defined by the Retrieval object, requested by the Multiple Rows (all) and Multiple Rows (distinct only), and based upon the Search criteria view;

  stores them in memory as a stack of data in the specified Sort order, if any;

  displays the stack of data in a window that provides the option to Select all or Select the item(s) that you wish to have considered (effectively manually reducing the size of the stack); and

  steps through this reduced stack of data, one at a time, from the top down.

• Data retrieval loop
  which consist of the Next row? Conditional draggable object and an example as to its usage. The use of the Data retrieval loop is mandatory when the Row processing option is not a Single row. To use this object: drag it into the procedure workspace and connect the Retrieval object to it. Connect the YES outlet to the chain of objects that process the returned rows - then connect the tail end of the chain back to the Retrieval object. The NO outlet is connected to the object or chain of objects that should be executed when no more rows are retrieved.
• Aggregate Functions
  when retrieving data as a Single row,  the Data retrieval loop area is replaced with a scrollable selection view. This view, with its separator cell Drag fields above this line , lists all fields included in the Retrieval object, which lend themselves to aggregate calculations (numeric and date fields). If one or more displayed fields are to be subjected to an aggregate calculation, drag those field names above the header text. Each field name row will be equipped with a pop-up list containing four aggregate options. These options are: SUM (total), MAX (maximum), MIN (minimum), and AVG (average). Make the appropriate selection.

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:

• Field name;
• pop-up list of available Comparison Operators;
• run-time button;
• comparison value field.

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:

• modified, if generated; or,
• set up from scratch, effectively by-passing the graphical Search Criteria and/or the Sort Order views.

In addition, the Lock table feature can be invoked

6. 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