Question
Areteans
NZ
Last activity: 3 Jun 2022 9:09 EDT
Generated SQL during Report Definition execution
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
Updated: 14 May 2021 19:36 EDT
TalentSprint
IN
@SatishKumarL5551 Hi please check on you will get on pyReportContentPage for standard thread when you unit test the Report definition. I am attaching screenshot..
Areteans
NZ
@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.
Cognitonic Systems Australia
AU
@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
Cognitonic Systems Australia
AU
@Dharanidharan As per my knowledge we cannot because in the higher versions of Pega, these properties are removed from the Page.
Ordina
NL
@SatishKumarL5551We 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 https://community.pega.com/marketplace/components/database-query-extractor 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:
labb
PH
@BasRulesMatter! 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!
Updated: 24 Jul 2022 9:56 EDT
Ordina
NL
@PaulaI'll check if that works next friday! :) I've added a .zip file containing the code to generate full SQL queries. It works as following:
- Save the ruile in your own ruleset and remove the default input for the parameters.
- In SQL: enter the SQL statement
- In Inserts: entere the SQL parameters (trace DB Query events, those are stored there)
labb
PH
Hello @BasRulesMatter! , 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! :D