Connect SQL form - Data Mapping

These keywords and notations provide a two-way mapping between Pega Platform features (the clipboard, Database Name instances, and Database Table instances) and the tables, rows, and columns of the external database.

Select MarketPrice as ".MarketPriceprop"

where MarketPrice is the Oracle column name and MarketPriceprop is the property name.

For suggestions on avoiding resource-intensive operations with Connect SQL rules, see the PDN article Writing SQL.

Referencing properties

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 }

In and Out keywords for stored procedures

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 }

Identifying a database table or a Pega Platform class

Literal table name

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.

Class 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}

Table keyword

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}}.

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.

Using SELECT statements

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.

SQL Comments

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.

Calling stored procedures

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.

• You can't specify the datatype of an out parameter. Consider returning a text value that is then converted.
• Stored procedure execution is not available for Db2/S390 databases. (However, stored procedures can be executed in UDB and in Db2/AS400) .
• To process result sets from a UDB SQL stored procedure, include in the UDB SQL stored procedure source the optional clause 'DYNAMIC RESULT SETS N', where N is the number of result sets that are returned.

Resultsets from Oracle stored procedures

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.

Multiple resultsets from SQL Server or Sybase 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.

The Asis keyword

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.

Use this feature only when necessary, as it can be costly in terms of database operations and performance.

Capturing SQL statements and status responses

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:

• pxSQLStatementPre — The SQL you entered in the Connect SQL form.
• pxSQLStatementPost — The SQL you entered in the Connect SQL form tab with the references resolved to show their values, so you can verify the references.

If errors occur, additional properties appear on this page:

• pxRDBError — An error message from Pega Platform.
• pxRDBSQLCode — A result code from the database access driver.
• pxRDBSQLVendorError1 — An error code from the database vendor.
• pxRDBSQLVendorError2 — An error code from the database vendor.
• pxRDBSQLVendorMessage1 — An error message from the database vendor software.
• pxRDBSQVendorMessage2 — An error message from the database vendor software.

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.

Mapping of Dates, Times, and DateTime properties

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.

Output of integers and DateTime values

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})

Dynamic SQL

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.