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.
When you need to access objects in a PegaRULES database, the RDB- methods and Connect SQL rules may not work, because not all the properties in the PegaRULES database are distinct database columns. Use the Obj-Open and Obj-Save methods, not the RDB- methods, with the Process Commander database.
Use the Clipboard tool to see the final SQL statement sent to the database and the results. (Use the Tracer tool with breakpoints to pause processing before or after the RDB method executes.)
For advanced and detailed debugging, use the DB Trace feature of the Performance tool.
As a best practice, restrict the SQL statements to operate on a single table, aligned with a database table value and the Applies To key part of the Connect SQL rule.
If your SQL needs to operate on multiple tables, consider implementing a view within the database that presents the columns. This approach places some responsibility for good database design and performance with the database administrator and database software.
After you save this rule, you can click the Test Connectivity button to confirm that Process Commander can find, log into, and communicate with the database. This button does not cause the SQL to be processed and does not alter the contents of the database.
The system presents test results in a separate window, identifying the parameters used in the test, the steps attempted, and the outcome of each step.
At runtime, the system interprets the keywords in the context of your clipboard, Database Table and Database information to form the final SQL. It then executes the SQL using standard database access capabilities.
Each operation involves the SQL statement on one tab of the rule. To determine which database to access, the system uses this search algorithm:
{Class } directive in the source SQL
statement.{Class} directive
but contains a {Table} directive, searches
through Data-Admin-DB-Table instances for a
match on the table name, and uses the database identified in
the referenced Data-Admin-DB-Name.
Algorithm 2. can produce
uncertain results if two tables in separate databases have the
same name. Use {Class} rather than
{Table} when feasible.
Data retrieved from a relational database is saved in clipboard pages.
Your activity can perform two or more RDB methods, for example first to open one record (row) in the database and later to save the record with updated values. The activity can first execute the RDB-Open method, referencing the first instance. The row data is then on the clipboard; the activity can modify the data (using Property-Set and other methods). The same activity can then execute the RDB-Save method, to store the updated row into the database.
Each RDB-Delete and RDB-Save method execution commits the changes to the external database. You cannot combine the database commit operations of multiple statements.
In its internal PegaRULES database, Process Commander stores date and time information adjusted to GMT time, based on the time zone of the server. The PegaRULES database contains few dates without a time and zone, and few times without a zone and a date. This approach facilitates date and time comparisons and arithmetic regardless of the time zone of users or external systems that interact with your system. See Understanding the Date, TimeOfDay, and DateTime property types.
If the external database accessed through Connect SQL rules contains dates without a time zone, or times without a date and time zone, analyze these values carefully. Use caution before making comparisons of external dates and times with date/time values within Process Commander.
For example, the Oracle DATE data type requires
a time. If you store a Process Commander property of mode
Date (which has no time) into an Oracle
DATE column, the time portion of the value is
defaulted to 00:00:00 GMT, which may not be appropriate. As a
workaround, store Date values into Oracle columns
of data type VARCHAR.
You can use the DB Trace feature of the Performance tool to view in depth the SQL operations of a Connect SQL rule.
The database you
reference in the Connect SQL rule must be the default
database of the database user account identified in the
Database data instance.
Connect SQL rules can access an IBM
DB2/390 database, with two restrictions:
SELECT AS statement with a
column name
Connect SQL rules can access an IBM DB2
Universal Database (UDB) database, except for database columns
with a DATE data type. Use syntax similar to the
following as a workaround:
SELECT TIMESTAMP(RPT_DATE, '00.00.00') AS ".RPT_DATE"
SQL connectors cannot process columns of
data type ARRAY, IMAGE, or
CLOB (Character Large Object).
Microsoft does not supply a JDBC driver
for Microsoft Access databases, although third-party facilities
are available. As a workaround, some Process Commander
applications have converted Microsoft Access databases to SQL
Server databases, or exported the data to CSV files that are
then imported using Service File rules.
