Direct SQL Database Retrieval

Direct SQL Retrieval

The Direct SQL Retrieval component retrieves data based on the specified SQL query, and makes the data available to the Procedure. This is an alternative to the compartmentalized (structured) Retrieval component that reconstructs the SQL query from the settings of the graphical controls in the Database Retrieval Inspector. The Direct SQL Retrieval is based on the opposite process of taking the user-supplied SQL query and deriving the names and data types of fields returned by it.

To designate the Retrieval as Direct SQL, the Retrieval component provides a right-mouse driven pop-up menu shown in the following example:


1. Right mouse pop-up menu	2. After setting menu to Direct

In the Direct SQL form, the Retrieval component has its header set to a permanent "Direct SQL" title, and does not accept dragging of the table names. Instead, the name (or names) of the tables that the Retrieval will operate on, is derived from the SQL query text entered in the Database Retrieval Inspector.

Unlike its Structured counterpart, the Direct SQL retrieval component is not resizable, and the user cannot add the table fields to it. Instead, the fields are added by Scribe after evaluation the SQL query command entered in the SQL command tab view of the Inspector.

The Direct SQL Inspector provides the following controls for configuring the Direct SQL Retrieval component:

Row processing

Multiple rows, Single row and Distinct only options are disabled for the Direct SQL retrieval. The Multiple rows setting is the default (and only) setting available for this type of retrieval. The effect of the Distinct only modifier can be achieved by placing the "distinct" keyword into the SQL query command if desirable.

The Row Processing view also displays the:
Stop & Show

returns all rows of data as defined by the Retrieval component, 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

lets the user select one or more displayed rows by clicking on them (to select multiple rows, hold Ctrl key down), effectively manually reducing the size of the stack. Selection of multiple rows is only possible if "Select many" radio button is checked:

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 component and an example as to its usage. The use of the Data retrieval loop is mandatory in Direct SQL mode. To use this component: drag it into the procedure workspace and connect the Retrieval component to it. Connect the YES outlet to the chain of components that process the returned rows - then connect the tail end of the chain back to the Retrieval component. The NO outlet is connected to the component or chain of components that should be executed when no more rows are retrieved.

Data source

This tab view sets the database connection and the default schema that will be used for executing the SQL query. If the database table(s) used in the query are qualified with the schema name, this overrides the default schema specification.

SQL command

The SQL command view is a text editor where a free-formatted SQL query is entered. The query must be a SELECT statement that returns one or more rows with the table fields. The fields can be the actual table columns, or calculated or aggregated columns (like COUNT(*) function), and the query can include subqueries, joins, unions and groupings.

After the SQL query is entered into the editor, it must be evaluated. The evaluation of query is done by sending it to the database server, and inspecting the names and data types of the returned fields. The returned field names are then loaded into the Retrieval component which is automatically resized to accomodate them, and are also added to the Local column of the Palette's Main Resources tab view.

Like its structured counterpart, the Direct SQL Retrieval component searches the target database table(s) for matching records, according to the SQL query, and loads all found data into internal memory. These accumulated records are then loaded into the procedure variables listed in the Retrieval component incrementally, one record at a time in a looping sequence, controlled by the flowchart.