Clarion Community Help

adoprocessdatacolumnsadoprocessdataadoprocessgeneraladoprocessdefaultbehavioradoprocessdefaultsqladoprocessclassesadoprocessconditionalbehavior

The ADO Process Extension Template is the core template for all Process and Report procedures that use ADO as a connection layer.

The following prompts are available:

General Tab

adoprocessgenpreviewadoprocessgendetailfiltersadoprocessgenprocess

Process Prompts

Processed Text

Enter a string of up to 100 characters that will be displayed on the process progress window. If left blank, the default is “Processed”

Timer Value

Enter a numeric value to specify the time slicing interval between the time that rows are being processed, and the update frequency of the progress window. Values are in hundredths of a second. If left blank, the default value is 50 (half second).

Rows to fetch per timer

Enter a number to designate how many rows can be processed in each timer interval. This number should be decreased if the complexity of the process is increased. The default value is 10.

Close Window

Check this box to automatically close the progress window when the ADO process has completed.

Enable Report

Check this box to call a report after the process is completed. This check box will in effect enable the procedure's Report button and the subsequent Report Formatter.

Print Preview

Check this box to call a Print Preview window prior to printing the report.

Detail Filters Prompts

Each Detail band is listed in the Detail Filters list. To restrict printing of a band, highlight it in the list, then press the Properties button. Provide an expression in the Filter field. The band will only print when this expression is true. For example, to force the template to not print a detail, enter FALSE.

Optionally, check the Exclude unfiltered box to restrict any other detail band which does not have a filter expression of its own.

Preview Options Prompts

The Preview Options tab lets you control the initial appearance of the report preview window. This tab is only available if you check the Print Preview box on the General tab.

Initial Zoom Setting

Sets the initial magnification for the report to one of seven discrete magnification choices. The end user may change the initial setting.

Allow User Variable Zooms?

Check this box to let the end user set custom report magnifications in addition to the preset magnification choices.

Set Initial Window Position

Check this box to enable the four following prompts to set the initial preview window position and size.

Maximize Preview Window

Check this box to initially maximize the preview window. This supersedes the Set Initial Window Position, whose coordinates are applied only when the window is restored to its normal (not maximized) state.

Data

The Data tab control contains prompts that focus on the ADO connection information:

Process Recordset as a parameter

Check this box to disable the ADO connection process (methods) for this procedure. This implies that the information (Recordset) that needs to be processed will be passed to the procedure.

Connection Group:

Connection Object

Choose a Connection object name from the drop list provided. This list should contain the connection object you created in the ADO Global support template.

Use a New Connection

If you do not wish to use any of the Global Connections that are available, check this box to create a new connection specifically for this browse box.

New Connection

Press this button to call the Connection Builder. On the subsequent dialog, enter a new name to use for the new Connection Object, and press the Connection String Settings button to access the Connection String Settings dialog.

Command Object

The ADO connection layer contains a default command object that is responsible for handling the appropriate data update behavior. If you need to select an alternative object name, enter the value here.

Data Columns/Hot Fields

The Data Columns and Hot Fields tab control provide prompts that allow control of the data elements that will appear in the Process or Report procedure.

The following prompts are presented:

Hot Fields

The Hot Fields list box displays data elements that may not be populated in the report structure, but need to be referenced or updated in the procedure source. Hot fields are normally data elements that are related to the contents of the primary data elements (i.e., Address information, text based descriptions, etc.). Press the Properties button to access the following prompts:

Hot Field

Press the ellipsis button to select a field to use as the hot field.

Column is a

Identifies the data element as one that is read directly from the ADO data source (Table Column), or one that is a variable defined within the application, like a computed or conditional field (Expression).

Use AS

Check this box if the column name is long and verbose, and you need to rename the column to a more descriptive and compacted name. This is useful if you are constructing long or complex SQL statements. This is an option for Table Columns, but required for all Expressions.

Unique Field ID/AS

Enter a unique Field ID to use with the Use AS option for Table Columns, or the AS option for an Expression..

Expression

Enter a valid expression to use for the defined Data Column

Expression Data Type

Select a valid data type from the drop list that the ADO layer will translate.

Data Columns

The Data Columns list box displays data elements that have been populated in the Report Formatter. Press the Properties button to access the following prompts:

Query Field

Identifies a column as a field that can be queried (searched).

Column is a

Identifies the data element as one that is read directly from the ADO data source (Table Column), or one that is a variable defined within the application, like a computed or conditional field (Expression).

Use AS

Check this box if the column name is long and verbose, and you need to rename the column to a more descriptive and compacted name. This is useful if you are constructing long or complex SQL statements. This is an option for Table Columns, but required for all Expressions.

Unique Field ID/AS

Enter a unique Field ID to use with the Use AS option for Table Columns, or the AS option for an Expression..

Expression

Enter a valid expression to use for the defined Data Column

Default SQL

Because the SQL to access the data is vitally important, the ADO templates allow you to view the generated SELECT statement and customize it if necessary. This also provides a convenient way to customize the process or report ordering, should you wish.

Regenerate SQL

Press this button to reset the original template-constructed SQL statements. This is useful should you need to start from scratch again before customizing your statements. Only enabled when the next prompt is active (checked).

Override SELECT SQL

Check this box to bypass the auto generation of the SELECT statement by the templates. Your custom statement will be substituted in its place. This box also enables the Regenerate SQL button, should you wish to reset the statement back to its original value.

Default SQL Select

This text box provides the base SELECT statement. Note that if your process or report contains fields from more than a single table, it will automatically provide for a JOIN.

Important Note: If you've populated a field in the Data Columns tab for which the ADO templates can't resolve the proper syntax and the resulting page doesn't print properly, examine the SQL statement here. Should you find a statement with a blank for the field name (look for an extra comma in the order that the suspect field appears in the data columns list, as in Select fieldname,fieldname,,fieldname…), you may edit the statement here or delete the suspect column from the list.

Unique Key

As stated on the tab control, ADO/SQL requires a unique key to identify a record. Should you have more than one unique key defined in your table, press the ellipsis button to select an alternate key to use.

Default Behavior

adoreportdefaultbehaviorsqladoreportdefaultfields

The default behavior of the ADO Process control has a much different interface than the standard report or process. You will also notice that the filter capabilities are expanded in the ADO template. The following prompts are available:

Fields Tab:

The Fields tab control provides control of your process or report's filtering and sorting features.

Range or Filter Columns

Press the update buttons to add (Insert), modify (Properties), or remove (Delete) a range or filter column. These are the data elements used to limit the records processes in the Process control. The Range or Filter dialog provides the following prompts:

Column

Select a column name from the drop list provided to use as criteria for the Process/report's filter or range. The columns displayed are those columns you have defined in the Data Columns/Hot Fields dialog.

Use Static Value?

Check this box to use the static value of the column selected. This is the value of the column when the process is first initialized

Negate the Range or Filter(NOT)

Check this box to apply the NOT clause to the selected column. This will allow records to be selected only if the column value evaluates to a blank or zero.

Range Limit Type

Specifies the type of range limit to apply. Choose one of the following from the drop-down list.

Single Value

Lets you limit the filter criteria to a single value. Specify the variable containing that value in the Range Limit Value box which appears.

Range of Values

Lets you specify upper and lower limits. Specify the variables containing the limits in the Low Limit Value and High Limit Value boxes.

Less Than

Lets you limit the records read to all records less than a single value. Specify the variable containing that value in the Range Limit Value box which appears.

Great Than

Lets you limit the records read to all records greater than a single value. Specify the variable containing that value in the Range Limit Value box which appears.

IN

Lets you limit the records read to all records that match the contents of a single value.

Begins with

Lets you limit the records read to all records that begin with the contents of a single value.

Ends with

Lets you limit the records read to all records that end with the contents of a single value.

Contains

Lets you limit the records read to all records that begin with the contents of a single value.

Sort Columns

Press the update buttons to add (Insert), modify (Properties), or remove (Delete) the process controls default sort elements. These are the data elements used to alternately sort the records processed by the control. The SQL tab control generates the ORDER BY clause.

Add Primary Key Fields

Press this button to automatically include all fields defined in the primary key as sort columns.

Add a Key Field

Press this button to select any key from the primary table, and apply it to the sort columns.

The Sort Columns dialog also provides the following prompts:

Column

Select a column name from the drop list provided to use as criteria for the browse filter or range. The columns displayed are those columns you have defined in the Data Columns/Hot Fields dialog.

Direction

Choose ASC from the drop list to designate an ascending sort, or DESC to specify a descending sort.

SQL Tab Prompts

The SQL tab control displays SQL statements that are generated, based on the settings displayed on the Fields tab.

Essentially, the settings of the Range or Filter are used to generate the SQL WHERE clause, and the Sort Columns setting are used to generate the ORDER BY clause.

Regenerate WHERE SQL

Press this button to regenerate the template SQL WHERE statement that is constructed based on your default settings.

Override WHERE SQL

Check this box to override the default template generated WHERE clause.

Enter a WHERE clause to filter this list

The text in this box provides the Where clause, which is concatenated to the Select statement.

Regenerate ORDER BY SQL

Press this button to regenerate the template SQL ORDER BY statement that is constructed based on your default settings.

Override ORDER BY SQL

Check this box to override the default template generated ORDER BY clause.

Enter the ORDER BY clause

The text in this box provides the Order clause, which is concatenated to the other parts of the statement.

Test SQL Query

Press this button to test the SQL statement. If a statement clause is not overridden, the statement will be regenerated based on the template's current settings.

Conditional Behavior

The Conditional Behavior tab duplicates the prompts and resultant functions found on the Default Behavior tab, with the addition of a Condition prompt:

Condition

Enter any valid Clarion expression.

Classes

Use the Classes tab to override the global settings for the Class. See Classes Tab.