| **Navigation:**  [[templates.htm|Templates]] > Guide to all Templates > Extension Templates >====== ADO Process Extension ====== | [[tplcodeaddadofile.htm|{{btn_prev_n.gif|Previous page}}]][[templates.htm|{{btn_home_n.gif|Return to chapter overview}}]][[tplextensionblobincontrol.htm|{{btn_next_n.gif|Next page}}]] | | || {{adoprocess1_shg.jpg|ADOProcess1_shg.jpg}}[[tplcontroladoprocess.htm#adoprocessdatacolumns|]][[tplcontroladoprocess.htm#adoprocessdata|]][[tplcontroladoprocess.htm#adoprocessgeneral|]]{{adoprocess2_shg.jpg|ADOProcess2_shg.jpg}}[[tplcontroladoprocess.htm#adoprocessdefaultbehavior|]][[tplcontroladoprocess.htm#adoprocessdefaultsql|]]{{adoprocess3_shg.jpg|ADOProcess3_shg.jpg}}[[tplcontroladoprocess.htm#adoprocessclasses|]][[tplcontroladoprocess.htm#adoprocessconditionalbehavior|]] 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** {{adoprocessgeneral_shg.jpg|ADOProcessGeneral_shg.jpg}}[[tplcontroladoprocess.htm#adoprocessgenpreview|]][[tplcontroladoprocess.htm#adoprocessgendetailfilters|]][[tplcontroladoprocess.htm#adoprocessgenprocess|]] **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 [[javascript:BSSCPopup('expression.htm');|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. | **X Position** | The initial horizontal position of the left edge of the window. | | **Y Position** | The initial vertical position of the top edge of the window. | | **Width** | The initial width of the window. | | **Height** | The initial height of the window. | **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 [[tpladosupport.htm#connectionstring|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** {{adoreportdefault_shg.jpg|ADOReportDefault_shg.jpg}}[[tplcontroladoprocess.htm#adoreportdefaultbehaviorsql|]][[tplcontroladoprocess.htm#adoreportdefaultfields|]] 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 [[tplcontroladobrowsebox.htm#adobrowsedatacolumnshotfields|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 [[tplcontroladobrowsebox.htm#adobrowsedatacolumnshotfields|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.htm|Classes Tab]].