Question
Tata Consultancy Services
IN
Last activity: 8 Aug 2024 10:06 EDT
Execute a query without Commit to DB
Hi All
I have a requirement in my current project where queries need to be validated before execution. In the validation process, we need the query to be executed but there should be no commit to DB. Is there a way in Pega to support the above functionality?
Also please let me know whether there are any OOTB rules available to set AutoCommit to false before Query execution, like it is achievable through Database end?
Note : I have already tried setting DSS AutoDBSchemaChanges to false and executed query through RDB, but data is getting committed to DB immediately.
Maantic
IN
When you're running SQL queries typically that involves UPDATE / DELETE kind of statements using SQL Connectors from Pega it is something like EMBED SQL in any other programming language.
We open DB Connection, Execute an SQL Statement and close the DB Connection except we don't see it as we do in traditional programming in terms of Code. But that's what essentially happens. So to answer your question no we cannot control the commit from SQL Connector from Pega.
Tata Consultancy Services
IN
@Anantha Nag K Yes I agree. But apart from RDBs, is there any other way to achieve this is the question? Maybe there must be some API or something else, not sure though.
I got hold of this API/method which can be run through JAVA, tried this approach as well but no go.
https://docs-previous.pega.com/data-management-and-integration/87/inserting-data-records-using-sql
Maantic
IN
If you look at the program snippet in java it doesn't give any explicit control over "commit" on DB side. It just throws an exception if the execution of the query causes any errors / failures.
Irrespective of the program if some error or failure happens the db would not continue to commit the changes.
The program try{} catch{} blocks only helps in capturing the failures that's all.