Back Forward RDB-List method

Methods and instructions by function

Use this method to retrieve rows from an external relational database and place the results as embedded pages in a specified step page of class Code-Pega-List.

This method references a Connect SQL rule instance, and executes SQL statements stored in the Browse tab of that rule instance. The search can do anything you can specify in a SQL statement, such as a SELECT WHERE statement. Any constraints on the returned data are in the SQL.

On the Browse tab of the Connect SQL rule, use the special data syntax described in Connect SQL rules — Data mapping.

CautionIn all but rare cases, use Connect SQL rules only with an external database. Do not use Connect SQL rules or RDB methods for the PegaRULES database(s). Because not all properties in the PegaRULES databases are distinct database columns, use the Obj-Open and Obj-Save methods, not the RDB- methods, with the PegaRULES database to prevent loss of data.

Specify a page of class Code-Pega-List as the step page of the RDB-List method. Also identify this page and class on the Pages & Classes tab of the Activity form.

Parameters

This method has several parameters. The first three parameters are required; together they identify the key of a Connect SQL rule.

Parameter

Description

RequestType

Enter the third key part — Request Type — of the Connect SQL rule containing the search operation.

Access

Enter the second key part — Package Name — of the Connect SQL rule (by convention MSSQL, Oracle, DB2, UDB, or Sybase, but not restricted to these values).

ClassName

Enter a class name, the Applies To key part of a Rule-Connect-SQL rule.

NoteIdentify the exact class of the rule. The system does not use class inheritance to find the Rule-Connect-SQL rule instance. This is also the class of the pxResults embedded pages which hold the search results. It can be a class that you created to hold the results.

This class is distinct from the class which the RDB-List method searches, which you identify in the SQL code in the Rule-Connect-SQL rule.

MaxRecords

Optional. Enter a positive integer that is the maximum number of rows to return in the search results.

BrowsePage

Optional. Identify the top-level page into which the search results are to be returned. The pxResults pages are embedded in this page. If left blank, the system uses the step page you specified for the RDB-List method in the activity step.

If you select this option, do not select the RunInParallel option. Parallel operations cannot update the single top-level browse page.

ApplyDeclaratives

Select to cause change tracking for Declare Expression and Declare Constraint rules processing to be applied to the properties returned by this method.

You cannot select this box if the RunInParallel box is selected.

RunInParallel

Select if you want processing for the connector to continue in a separate requestor, in parallel with the current requestor. When selected, use the Connect-Wait method later in the current or a later activity to access results from the connector.

Clear if you want processing in this requestor session to pause until the connector processing completes.

You cannot select this box if the ApplyDeclaratives box is selected. Do not select this option if you supplied a page name in the BrowsePage field.

Results

This method accesses the Rule-Connect-SQL rule instance identified by the first three parameters, using rule resolution. It converts table, class, and property references on the Browse tab of this rule using clipboard data, and sends the resulting updated SQL to the external database.

The RDB-List method returns data to the page you specify in the Step Page field on the activity form. This can be a previously created page — which is cleared and reused — or a new page created with this method.

When the RunInParallel box is selected, the SQL operation occurs in a new requestor session. This temporary requestor has the same access group and clipboard as the original. At most 10 such child requestor sessions can be active at once.

When the system receives information from the external database, it updates properties on the step page, which has the class Code-Pega-List. For a more complete list, see Standard properties in the Code-Pega-List class.

Property

Description

pxResultCount Number of rows of data returned by the search.
pxSQLStatementPost SQL statements as sent to the SQL database. Appears only when requested through the {SQLPage} syntax
pxSQLStatementPre SQL statements before processing of the Class keyword, Table keyword, and other clipboard substitution. Appears only when requested through the {SQLPage} syntax

If the search is successful, this method also creates a page list page named pxResults, which contains pages pxResults(1), pxResults(2), and so on. These embedded pages contain the row results as properties. Depending on the ApplyDeclaratives box, the declarative processing may be activated for the properties.

Finally, this method can create a (classless) error page, if specified in the Connect SQL rule.

Debugging SQL and SQL performance

You can use the DB Trace feature of the Performance tool to view in depth the SQL operations of a Connect SQL rule.

NoteExecution of this method may cause an alert to be added to the Alert log, of type PEGA004-PEGA007, PEGA0025, or PEGA0028. Review the Alert log to understand the frequency and sources of such alerts, and for best performance, alter your application or database to eliminate those alerts that occur frequently.

Checking the method status

This method updates the pxMethodStatus property. See How to test method results using a transition.

When a {SQLPage:pagename} directive appears as the first line of the SQL code on the Browse tab, a top-level page named pagename is created. If errors occur, properties on that page contain additional error details. (When RunInParallel is selected, this page is not available to the original requestor.)

Related topics About Connect SQL rules
Connect-Wait method
 
Connect SQL form — Data Mapping
About the Performance tool
Standard rules Atlas — Standard properties in the Code-Pega-List class

UpMethods and instructions by function