Use Connect SQL rules when your application needs to run complex Structured Query Language statements such as joins or stored procedures to update or extract information from an external database.
The following tabs are available on this form:
As an alternative, if your application needs to perform a simple browse, open, delete, or update operation against an external database, use the Connector and Metadata wizard to configure external table access. When external table 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 and you do not need to create SQL connector rules.
Use the Application Explorer to list the Connect SQL rules in your application. Use the Records Explorer to list all Connect SQL rules that are available to you.
To configure an SQL connection to an external database, use the following elements along with the Connect-SQL rule:
- An activity that references one or more of four standard methods for operating on relational databases — RDB-Open, RDB-List, RDB-Delete, and RDB-Save. The names of the methods match the names of the tabs in the connector rule. An activity that uses RDB-Open to call a connector rule runs the SQL statement on that connectors Open tab, and so on.
- A Database instance and one or more Database Table instances that provide access to the tables or views of the external database.
- A JDBC library that provides access to the external database software.
- Special data mapping syntax to associate properties with database columns.
Connect SQL rules can be referenced in an Integrator shape ( ) of a flow rule .
Connect SQL rules are instances of the Rule-Connect-SQL class. They belong to the Integration-Connectors category.
- Connect SQL form - Data Mapping
You can enter standard SQL into the Connect SQL form. In addition, employ the syntax described here to cause Pega Platform to make runtime substitutions to the SQL before sending it to the external database.
- Connect SQL rules - Completing the Create, Save As, or Specialization form
- Connect SQL form - Completing the Open tab
You can use the Open tab to enter a SQL statement that opens a single row in the database and copies its column values as properties onto a clipboard page. To execute the SQL statement on this tab, an activity uses the RDB-Open method.
- Connect SQL form - Completing the Delete tab
Connect SQL form – Completing the Delete tab
- Connect SQL form - Completing the Save tab
Enter an SQL statement to be executed by an activity that includes the RDB-Save method.
- More about Connect SQL rules
Use Connect SQL rules with an external database. Except in unusual situations, use the Obj-Browse method or Obj-List-View method rather than Connect SQL rules and RDB methods for the PegaRULES database.