Connect SQL form - Completing the Browse tab

Connect SQL form – Completing the Browse tab

The system uses information in this tab whenever an activity executes the RDB-List method. The results of the RDB-List method are a user page (of class Code-Pega-List ) containing properties that summarize the search, plus a list of embedded pages named pxResults(1), pxResults(2),... containing the properties from each search result row.

As a best practice, include only one SQL statement on this tab. If your situation requires multiple statements, place one statement each in separate Connect SQL rules. Your activity can cause the statements to be executed in the desired sequence. If your situation requires many statements executed in sequence, consider using a stored procedure.

Note: As an alternative, if your application needs to perform a simple operations against an external database, it is best practice to use the External Database Table Mapping wizard or the Connector and Metadata wizard or the to configure access to the rows as an external class. When such external access is configured for an external table, you can use the Obj-Open, Obj-Browse, and other Obj- methods to interact with that table through a class that represents it; you do not need to create SQL connector rules. However, you can still use the Browse tab (in conjunction with an activity that includes the RDB-List method) to create a rule that, at runtime, searches an external relational database and returns to the Pega Platform data from the selected rows. See About generating connector rules and Data Model category — Database Class Mappings page.

To define a SELECT statement that is expected to return exactly one row, you can complete the Open tab rather than the Browse tab. The RDB-Open method, used with an SQL command in the Open tab, places properties from the single selected database row directly into the current step page. The step page can have a class of your choosing.

By default, properties retrieved by browsing an external database are not tracked for changes by Declare Expression processing. Your activity can override this and force change tracking through a parameter of the RDB-List method.

Note: The IBM Db2 data type DATE does not directly correspond to any Pega Platform property type. Use syntax similar to the following as a workaround: 
SELECT TIMESTAMP(RPT_DATE, '00.00.00') AS ".RPT_DATE"
Field Description
Browse SQL Enter an SQL statement that define a sequential search on a portion of the external database. Follow the guidelines in Data Mapping in SQL. Any property values mentioned in a WHERE clause must correspond to exposed columns in the database table.

Enter the SQL statement directly into the text box. You can use a SELECT, or (for stored procedures) an EXECUTE or CALL statement in this tab.

For Oracle databases, follow a stored procedure name with parentheses, in the format:

CALL MYPROC()

For Oracle databases, you can't call a stored procedure that returns a result sets.
Error Handler Flow Optional. Identify a flow rule that is to be started when this connector rule is started by a flow rule but fails when the Integrator shape throws a ConnectorException exception. The default flow rule Work-.ConnectionProblem provides one approach for handling such problems. See Handling connector exceptions.
Test Connectivity After you save this rule, you can click to confirm connectivity to the database. No database operations are performed.

See More about Connect SQL rules.