Vertica transactions

Goldengate

Hello guys, hope you're doing great!

I'm working with a SQL script on Dbeaver on Vertica 11.1 and I need to create a transaction with setting autocommit off and something like this:

BEGIN TRY
BEGIN TRANSACTION

    CREATE TABLE myTable (A INT NOT NULL, B VARCHAR(10),C  NUMERIC(10,2) NOT NULL)

    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);

COMMIT TRAN  --DO IT NICELY!

END TRY
BEGIN CATCH
@TRANCOUNT > 0
-- ERROR ERROR ERROR ROLLBACK!!!!!!!!
ROLLBACK TRAN
END CATCH

Any ideas?

Regards

Fran

SruthiA

you can just create a .sql file with with create table and insert statement and set AUTOCOMMIT off and ON_ERR0R_STOP. It will rollback if insert fails with errors.

https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/ConnectingToVertica/vsql/Variables/ON_ERROR_STOP.htm

SruthiA

If you are planning to use if else control statements, then you can use stored procedures and set autocommit off

https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConnectingToVertica/vsql/AUTOCOMMIT.htm

https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/ExtendingVertica/StoredProcedures/PLvSQL/PLvSQL.htm?

Goldengate

Thx @SruthiA but I don't need SP just a script to create table, insert in the table one time. The point is I need to rollback the inserts if them fail and don't want AUTOCOMMIT in the way. So how can I accomplish my script of automatic rollback if gets any errors?

Regards

Fran