Do Connect-SQL rules have any access to the blob contents
We found the following content in the PRPC help files, and it sort-of implies that RDB methods do not have access to the blob contents. Can anyone categorically confirm this for us.
RDB-List method
|
|
Use this method to retrieve rows from an external relational database and place the results as embedded pages in a specified step page of class Code-Pega-List.
This method references a Connect SQL rule instance, and executes SQL statements stored in the Browse tab of that rule instance. The search can do anything you can specify in a SQL statement, such as a SELECT WHERE statement. Any constraints on the returned data are in the SQL.
We found the following content in the PRPC help files, and it sort-of implies that RDB methods do not have access to the blob contents. Can anyone categorically confirm this for us.
RDB-List method |
|
Use this method to retrieve rows from an external relational database and place the results as embedded pages in a specified step page of class Code-Pega-List.
This method references a Connect SQL rule instance, and executes SQL statements stored in the Browse tab of that rule instance. The search can do anything you can specify in a SQL statement, such as a SELECT WHERE statement. Any constraints on the returned data are in the SQL.
On the Browse tab of the Connect SQL rule, use the special data syntax described in Connect SQL rules — Data mapping.
In all but rare cases, use Connect SQL rules only with an external database. Do not use Connect SQL rules or RDB methods for the PegaRULES database(s). Because not all properties in the PegaRULES databases are distinct database columns, use the Obj-Open and Obj-Save methods, not the RDB- methods, with the PegaRULES database to prevent loss of data.
Pegasystems
IN
Please refer to a similar post in Pega product support here - https://collaborate.pega.com/question/it-possible-read-blob-returned-oracle-stored-procedure-connect-sql-rule
That said, are you looking for access to the Pega internal table's BLOB content (column pzPVStream) or are you looking to access content from a different BLOB column in an external table?
Pegasystems Inc.
JP
Thanks Nistr, I did see that post, but the question is slightly different. We are not purposely trying to read blob content by refering to pzPVStream, we just want to know what happens if you reference a column name that is not exposed. The Obj- methods will open the Pega blob to get it, and we believe that the RDB- methods will not. Can you confirm that RDB- methods only have access to the column names that the DB can see and by design will never use the blob as a backup.
Accepted Solution
Pegasystems
IN
Can you confirm that RDB- methods only have access to the column names that the DB can see and by design will never use the blob as a backup.
Your understanding is correct. Since RDB- methods use Connect-SQL to fetch data from the database, they rely on the query provided by the developer and have no context of falling back to pzPVStream if the column name mentioned in the query is actually not a column in the table. Note that to support fallback, it would result in explicit parsing of the query itself (which in itself might be quite complicated depending on the use case). With Obj- methods or List View / Report Definitions, since developers specifically refer to properties (and not write the actual SQL query), at execution time, the Pega platform can validate the existence of an explicit column for it or not. Based on this, it can generate an appropriate query of either opening the entire BLOB or use UDFs.
Pegasystems Inc.
JP
Thanks Nistr,
I actually put together a small POC and proved this.
- Create a Class based upon Work-, but not in the pr_other DB table
- Expose one of the Class properties
- Set the value of the property via a PRPC screen, and move onto the next screen to trigger saves
- RDB-Save to update the property
- RDB-Open to retrieve the exposed property (which is now the updated value)
- Obj-Open to retrieve the entire record (which grabs the blob, which still contains the old value)
Many thanks