|
|
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. CLINIC 5/05
C-605 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.
C-1704The 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: GENTJ 3/27/06
More about Connect SQL rules{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. Q-1334
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. B-17147
You can use the DB Trace feature of the Performance tool to view in depth the SQL operations of a Connect SQL rule. TURBT 4/7/05
B-3276 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
R-4605
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: SR-7589
B-14387
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). SR-740 and Clinic 12/10/04 SR-6802
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. ~Clinic
11/10/06
About Connect SQL
rules