Connect SQL form
|
You can enter standard SQL into the Connect SQL form. In addition, employ the syntax described here to cause the Pega Platform to make runtime substitutions to the SQL before sending it to the external database.
These keywords and notations provide a two-way mapping between the Pega Platform features (the clipboard, Database Name instances, and Database Table instances) and the tables, rows, and columns of the external database.
Note: In the Oracle implementation of Structured Query Language, column names use only uppercase letters unless you use the AS clause. To match a column name that contains lowercase letters, use AS:
Select MarketPrice as ".MarketPriceprop"
where MarketPrice is the Oracle column name and MarketPriceprop is the property name.
Note: For suggestions on avoiding resource-intensive operations with Connect SQL rules, see the PDN article Writing SQL.
A property on the clipboard can be a source of string values (as in a SELECT statement or INSERT statement) or a destination for data values. (Your source or destination property reference must be a single string.)
Similar to the HTML reference directive, use the curly brace characters to reference a property or part of a property.
{.property} or {page.property}.
For example:
WHERE zipcode={myPage.zipcode}
When used as a source of a string value, Pega Platform converts this into an SQL prepared statement variable, approximately equivalent to:
WHERE zipcode='02142'
In situations where the quote characters are not needed or not appropriate, use the Asis keyword, described below.
Use the optional Decimal or Double qualifier to indicate that a column being mapped to corresponds to the java.sql.Types constants DECIMAL or DOUBLE respectively. For example:
{pyWorkPage.pyEffortActual Decimal }
The Type value of a Pega Platform property value (such as Integer, Text, DateTime, or True or False) is not relevant here. In both retrieval operations (such as SELECT) and update operations (INSERT, UPDATE), the datatype of the column in the external database is what matters.
References to elements of Page List and Value List properties must contain integer constant index values such as 7 or 23 or keywords such as <LAST> or <CURRENT>. You can't reference a property as an index value.
Likewise, references to elements of Page Group and Value Group properties must contain constant subscript values such as "VA" or "Customer" or keywords such as <APPEND> or <CURRENT>. You can't reference a property as a subscript value.
You cannot use the Param keyword in these property references.
The ARRAY, IMAGE, and CLOB SQL types are not supported.
Optionally, follow the property name by out to indicate that this property is the result (output) of a stored procedure. Similarly, use the keyword in to indicate that this property value is a parameter to a stored procedure:
{.zipcode out }
Use both keywords when the parameter is both an input and an output.
{.zipcode in out }
When the SQL from clause identifies a table name literally, the system assumes that this table is the database associated with the Applies To class of the this connect SQL rule. For example, if the Applies To class is Data-HR-KB-Employee and the SQL statement includes
select ... from dependents
the system looks in sequence for database table instances named Data-HR-KB-Employee, Data-HR-KB-, Data-HR- and Data- to identify a database, and then uses the table named dependents in that database.
Alternatively can use the Table keyword or the Class keyword to identify a database table to use, and search through all the instances of all the classes stored in that table The system uses information in the class rules, database table instances, and database instances to identify the external database and a table (or view) within the external database.
Note: For the best performance and ease of debugging use the Class keyword rather than the Table keyword.
You can identify a Pega Platform class. At runtime, the system uses the class name to determine a Database Table instance, and from that instance, a database table. The syntax for the Class keyword is similar to the Table keyword. With Class keywords, use the format:
{class:ClassName}
for a direct reference to a fixed, known class. or:
{class: {page.property}
for an indirect reference that at runtime becomes the class.
For example,
{class:Data-Customer-Address-Details}
To use Table keywords, follow the format:
{Table:TableName}
for a direct, static reference to one Database Table instance, or:
{Table: {page.property}}
for an indirect reference to a property that, at runtime, contains in its value a Database Table name. For example,
{Table: {CustomerMaster.OracleAddressTable}}.
Caution: If your system contains two or more databases that by coincidence all contain a table specified in the Table keyword, do not use the Table keyword. You cannot control which database is used; results are indeterminate and may vary from run to run. Use the Class keyword instead.
For example, if both the PegaRULES database and a separate EMPLOYEE database contain a table named pr_other, the syntax
{Table: pr_other}
is ambiguous and may apply to either database.
Use this syntax when entering a SELECT statement:
SELECT columnname AS ".propertyname" or "page.propertyname".
For example, if the property named zip is to contain the value of the column named Postal:
SELECT Postal AS ".zip".
If the database column name is the same as the property name, you can omit the AS clause. Specify:
SELECT columnname.
You can also use:
SELECT {all}
to select all the columns in the table. Use it to replace a SELECT * from ... statement.
For example:
SELECT {all} from {class: Data-Customer-Summary}
retrieves all the columns from the table identified in the Database Table instance named Data-Customer-Summary.
Use the characters /* and */ to surround comments within your SQL statements. Text between these is ignored. For example:
/* This is a comment. */
Don't attempt to nest comments within comments.
Comments can document your rules, but are stripped from the SQL code text sent to the database software. Use the ASIS keyword — not comments — to send Oracle query hints.
Include the optional out keyword to capture the results of a stored procedure in a Single Value property. Use the optional in keyword to identify a property that supplies an input parameter to a stored procedure. If omitted, in is the default.
For example, the SQL used to call a stored procedure can look like this:
\{call my_stored_proc({Values.In1},
{Values.Out1 out},
{Values.InOut1 in out })
\}
where the first parameter is an input parameter, the second is an output parameter, and the third is an input/output parameter.
Notes:
To support the output of resultsets, a RESULTSET keyword is available. Include the RESULTSET keyword inside curly braces as any parameter for a stored procedure in the Browse tab textarea:
\{call procedure({resultset})\}
This keyword causes the system to process the output of the stored procedure as a resultset which is used to populate the Code-Pega-List.pxResults Page List property. One page is created for each result in the resultset.
To create multiple resultsets in one procedure, place a {resultset} marker for each parameter for each example that returns a resultset. For example:
\{
{resultset} = call testFunc(
{dataPage.OutProperty1 out},
{dataPage.inkey1},
{resultset},
{dataPage.inkey2},
{dataPage.OutProperty2 out},
{dataPage.inkey3},
{resultset})
\}
Note that the example above uses a {resultset} marker as a return value.
For an example of this feature, see PDN article How to process a resultset returned from Oracle stored procedures.
A stored procedure in a Microsoft SQL Server, IBM UDB, or Sybase database may execute two or more SELECT statements and return multiple resultsets. Include the MULTISET keyword anywhere in the Browse tab textarea, in curly braces:
{multiset}
Case and location are not important for the keyword.
The Connect SQL rule constructs a different clipboard structure for the results when this keyword is used. The pxResults property is a Page List; each page is of class Code-Pega-List and contains an integer property pxResultCount as well as another Page List property pxResults, which contains the pages belong to the application class.
You can affect the clipboard structure of MULTISET results by setting two Boolean properties pyRDBSuppressUpdateCounts and pyRDBSingleMultisetResultSet on the pxRequestor page. See PDN article How to return multiple return sets in Rule-Connect-SQL operations using the MULTISET keyword.
Note: For Oracle databases, calling stored procedures that return any result set is supported, so MULTISET is not supported.
You can include the value of a Single Value property or a text literal into the SQL using the Asis keyword. For a property, use the syntax:
{Asis: property-name}
In contrast to a simple property reference such as { .LastPrice }, the system does not place spaces or quotes around the value. For example, you can search for last names that start with "Smit" using:
SELECT A, B from {Class:CustomerMaster} where LastName LIKE '{Asis:Mypage.LastNamePrefix}%'
when value in LastNamePrefix is the four characters Smit.
For literal text, use the syntax:
{Asis: "/* literal text */"}
This facility can be used for passing "hints" to Oracle databases.
Case in the keyword is not significant; asis, ASIS, and AsIs are equivalent.
Caution: Use this feature sparingly and only when necessary, as it can be costly in terms of database operations and performance.
To include SQL debugging and return status information from the database software, enter a line at the top of your SQL code in the format:
{SQLPage:mySQLPage}
Enter the name of page on which the system records the error messages. The page can have a class or be a classless page.
When the Connect SQL rule executes, the system creates a page with this name and adds two properties:
If errors occur, additional properties appear on this page:
When the RDB-List method executes, the system adds error messages if any to the browse page. With the RDB-Open, RDB-Save, and RDB-List methods, the system returns a method status, but error messages are not visible unless you use the {SQLPage } mechanism.
Some database vendors provide non-standard SQL datatypes for dates and times. If the external database you are connecting to contains vendor-specific date or time formats, you may need to map them to a Text mode property of the appropriate length (rather than a Date, Time, or DateTime mode property), or may need to cast (convert) the values. For background and tips, see Understanding the Date, Time, and DateTime property types.
When performing an INSERT SQL operation, you can use the keywords Integer or DateTime with a property to cause the property to be formatted appropriately.
The Integer modifier causes the value of the property to be passed as an integer. For example:
Insert into MyTable (MyIntColumn) values ({.MyIntProperty Integer})
The DateTime modifier causes the value of the property to be passed as a DateTime value. For example:
Insert into MyTable (MyDateColumn) values ({.MyDateProperty DateTime})
Integer and DateTime keywords with the in keyword or with both in and out. You cannot use these keywords with only the out keyword.Integer and DateTime keywords regardless of the Type of the property, if the value conforms to the internal representation of an integer or DateTime.The syntax elements described in this topic are most often used to parameterize an SQL statement, changing the comparisons for a SELECT statement, providing data to insert, and so on.
If required, your application can generate an SQL statement or portion of an SQL statement dynamically, and store the statement as a property value. For example, if a WHERE clause is to vary from case in more ways that can be handled with simple value substitutions, your SQL statement can become:
SELECT Property1 WHERE Table1 WHERE {ASIS:.MyDynamicSQL }
Use care to ensure than values in the property MyDynamicSQL are valid and appropriate.