|
|
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 PRPC database.
Debugging
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.
Using views for SQL involving multiple tables
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
Testing connectivity
C-605 After you save this rule, you can click the Test Connectivity button to confirm that PRPC 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.
How this rule works
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
- Uses the database table associated with the Class of a
{Class }directive in the source SQL statement. - When the SQL contains no
{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. - Uses the database table associated with the Applies To key part of the Connect SQL rule.
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
Notes on date and time values
In its internal PegaRULES database, PRPC 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 PRPC.
For example, the Oracle DATE data type requires a time. If you store a PRPC 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
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. TURBT 4/7/05
Limitations
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:
- Do not execute stored procedures R-4598
- Do not use the
SELECT ASstatement 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 PRPC 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