Generated SQL during Report Definition execution

General
, ,
1

Hello,

We are using Pega 8.4.1. I dont see pxSQLStatementPre and pxSQLStatementPost properties on the clipboard after executing a report definition which I remember seeing in earlier versions of pega making it easy to debug any issues related to report definition behaviour.

Any reason why these are not available and how we can make them available?

Any suggestions or thoughts are appreciated.

Thanks,

Satish

2

@SatishKumarL5551 Hi please check on you will get on pyReportContentPage for standard thread when you unit test the Report definition. I am attaching screenshot..

New Bitmap Image (2).jpg
New Bitmap Image (2).jpg1366×768 272 KB

3

@AmarGajananW - Thanks for the response. I have observed pre and post sql in earlier versions of Pega. However in the current version that we are using 8.4.1 below is the snapshot of pyreportcontentpage where I dont see the generated SQL. So want to check how the generated SQL can be made available on clipboard.

Report Content Page.png
Report Content Page.png1680×727 68.8 KB

4

@SatishKumarL5551 Pega is not maintaining these properties (pxSQLStatementPre and pxSQLStatementPost) in pyReportContentPage from Pega 8 version. To check the Pre and Post execution queries of report definition, enable DB Query option in Tracer settings and execute the report definition.

Once report is executed switch back to tracer and navigate to SQL Query step :

For Pre SQL: Navigate to the step where Even Type is SQL: Click on the step, the query that is present in SQL attribute is Pre SQL

For post SQL : Navigate to the step where Even Type is Package Results: Click on the step, the query that is present in SQL attribute is Post SQL

5

@KotaC382 Is there any way to map the Pre SQL and Post SQL values to properties as these values are not on any page?

6

@Dharanidharan As per my knowledge we cannot because in the higher versions of Pega, these properties are removed from the Page.

7

@SatishKumarL5551 We have created an activity to generate the SQL of a report definition with ? parameters removed, it’s as simple as doing a trace with the DB Query option checked. We used Database Query Extractor | Pega but this is not working anymore from platform 8.6.3.

I can make a RAP of this activity and maybe you can make a component of it, let me know. Outline:

Query merge activity steps.png
Query merge activity steps.png1512×547 58.8 KB

8

@BasG0629 Hello! Is it okay if you can also share the RAP with me? Our application is currently in 8.6.3 thus, we can’t use the one in available in Marketplace. Thank you!

9

@Paula I’ll check if that works next friday! :slight_smile:

I’ve added a .zip file containing the code to generate full SQL queries. It works as following:

  1. Save the ruile in your own ruleset and remove the default input for the parameters.
  2. In SQL: enter the SQL statement
  3. In Inserts: entere the SQL parameters (trace DB Query events, those are stored there)

Querymerge.zip (16.1 KB)

10

Hello @BasG0629 , Thank you for sharing the zip file! I’ve tried the activity and it was working as expected from our side. This is really a big help in getting the SQL without manually inserting the bind variables. Thank you so much! :smiley: