I am creating a VQL stored procedure, but it is not rolling back when I catch an exception.
I am following the example provided here:
https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_vql_stored_procedures
Here is the specific example I am following:
CREATE OR REPLACE VQL PROCEDURE testTransactionsExceptionsHandling(paramName IN INTEGER)
AS (
// Procedure variables
varName VARCHAR;
)
BEGIN
// Procedure body
BEGIN_TRANSACTION;
// If insert fails (for whatever reason), EXCEPTION part will be executed
INSERT INTO testnvarchar_1 (id,charfield) VALUES (3, 'Test');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
For my test I set the database up to fail on insert to test the rollback. In this example the one of the fields is too small, forcing an insert failure.
Here is some simplified code that throws the exception:
CREATE OR REPLACE VQL PROCEDURE sp_simple_rollback_exeption
FOLDER = '/stored procedures'(varout out varchar) --output parameter isn't used.
AS (
lvar varchar;
-- exc1 exception;
)
BEGIN
BEGIN_TRANSACTION;
delete from bv_targetexc;
Execute 'insert into bv_targetexc select * from bv_source4';
commit;
EXCEPTION
WHEN others THEN
-- when exc1 then
return row (varout) values ('Error on insert');
ROLLBACK;
END;
Exception:
SP_SIMPLE_ROLLBACK_EXEPTION [STORED PROCEDURE] [ERROR] Received exception with message 'Cannot rollback if transaction is not started.' Received exception with message 'java.lang.NullPointerException'
I also set up a second version to catch the exception differently. This creates and exception variable instead of using "others".
VQL SP Code
CREATE OR REPLACE VQL PROCEDURE sp_simple_rollback_exeption
FOLDER = '/stored procedures'(varout out varchar) --output parameter isn't used.
AS (
lvar varchar;
exc1 exception;
)
BEGIN
BEGIN_TRANSACTION;
delete from bv_targetexc;
Execute 'insert into bv_targetexc select * from bv_source4';
commit;
EXCEPTION
-- WHEN others THEN
when exc1 then
return row (varout) values ('Error on insert');
ROLLBACK;
END;
Exception:
SP_SIMPLE_ROLLBACK_EXEPTION [STORED PROCEDURE] [ERROR] Received exception with message 'Error executing data movement from view _p_17d113c7-c86f-4faa-968e-1f646ee9e86d_2943d3b8-6968-4db9-947e-3b1cef9291b8 to source ds_pg_target
Exception of plan _p_17d113c7-c86f-4faa-968e-1f646ee9e86d_2943d3b8-6968-4db9-947e-3b1cef9291b8: com.denodo.vdb.util.tablemanagement.TableManagerException: There was an error during a batch insertion: Batch entry 0 INSERT INTO "public".targetexc(customer_name, customer_address, phone_number, customer_code, job_title) VALUES ('Laura Gutierrez', '7751 Larry Junction', '356-(361)254-1886', 'qHzxXA0S', 'Web Designer IV') was aborted: ERROR: value too long for type character(10) Call getNextException to see other errors in the batch.'
Note: The transaction will eventually roll back in both versions after it times out. This creates the following message in the logs
[JotmBatch] ERROR 2024-04-23T13:49:38.562 com.denodo.vdb.engine.session.DBSession [] - executing rollback due inactivity timeout on session #395, transaction bb14:38:0:018c8f4f655f4db6ab...0a109e:
I am looking to get the VQL Stored procedured to rollback immediately upon catching the exception and to return a graceful message to the caller.
