-
1. Re: Multiple delete statements Procedure, Output as XML
rareddy Aug 24, 2017 9:28 AM (in response to rflesken)If you are using Designer, add a return to the procedure and have it type as "xml". Then change the procedure like below
BEGIN delete from CDBTables.cdb_product_specifications where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber); DECLARE INTEGER product_deleted = VARIABLES.ROWCOUNT delete from CDBTables.cdb_g_productdetails where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber); DECLARE INTEGER details_deleted = VARIABLES.ROWCOUNT SELECT XMLELEMENT("deleted", XMLELEMENT("product", product_deleted), XMLELEMENT("details", details_deleted)); End
Note the return is SQLXML object, so viewing the result in tools like SQuirreL will not show up correctly until you convert to string. You can also return the individual updated columns which can be read as resultset.
-
2. Re: Multiple delete statements Procedure, Output as XML
rflesken Oct 18, 2017 10:56 AM (in response to rareddy)Hi Ramesh,
Thanks for your answers, that worked for me.
I have a follow up question, i can't get it to work using the example. I want to add an exception that catches any SQL errors and shows me some kind of useful error in an XML output.
I checked out the article on Exception Handling with Teiid but that didn't help for me yet.. How would i do this? What i want is something like below:
BEGIN delete from CDBTables.cdb_product_specifications where caseid in (select caseid from CDBTables.cdb_order_request_headers where external_requestid = DeleteCase.CaseNumber); DECLARE INTEGER product_deleted = VARIABLES.ROWCOUNT delete from CDBTables.cdb_g_productdetails where caseid in (select caseid from CDBTables.c db_order_request_headers where external_requestid = DeleteCase.CaseNumber); DECLARE INTEGER details_deleted = VARIABLES.ROWCOUNT <exception handler NOK?> SELECT XMLELEMENT("Error", XMLELEMENT("Code", SQLSTATE), <exception handler OK?> SELECT XMLELEMENT("deleted", XMLELEMENT("product", product_deleted), XMLELEMENT("details", details_deleted)); End
-
3. Re: Multiple delete statements Procedure, Output as XML
shawkins Oct 18, 2017 11:45 AM (in response to rflesken)See Procedure Language · Teiid Documentation
You will want the end of your procedure block to look like:
SELECT XMLELEMENT("deleted", XMLELEMENT("product", product_deleted), XMLELEMENT("details", details_deleted)); EXCEPTION e SELECT XMLELEMENT("Error", XMLELEMENT("Code", e.STATE), End
-
4. Re: Multiple delete statements Procedure, Output as XML
rflesken Oct 20, 2017 8:06 AM (in response to shawkins)Hi Steven,
Will this rollback all statements within the block (there are multiple delete statements on different tables) ?
-
5. Re: Multiple delete statements Procedure, Output as XML
shawkins Oct 20, 2017 8:23 AM (in response to rflesken)> Will this rollback all statements within the block (there are multiple delete statements on different tables) ?
To utilize a block level transaction you need to use
BEGIN ATOMIC
...
If there is exception handling for an atomic block and there is an exception the transaction will only be allowed to rollback. A caveat is that you need all your sources to participate in XA transactions for a rollback to be effective.