Exiting a DbVisualizer Script Following an Error

Jim_Knicely

After reading yesterday’s Vertica Quick Tip “Exiting a vsql Script Following an Error”, a client asked if the ON_ERROR_STOP variable is available in the popular third party Vertica client tool DbVisualizer. The answer to that is no as ON_ERROR_STOP is a Vertica vsql client specific setting. However, many clients, including DbVisualizer have a similar feature!

Example (in DbVisualizer):

In the following SQL Script the first INSERT will fail because it will try to insert a NULL value into a column having a NOT NULL CONSTRAINT. Since I included the DbVisualizer client side command “@STOP ON ERROR;”, the script will terminate immediately upon that error.

@STOP ON ERROR;
SET SESSION AUTOCOMMIT TO OFF;

DELETE FROM tbl1 WHERE pkid IN (SELECT pkid FROM tbl1_temp);
INSERT INTO tbl1 SELECT * from tbl1_temp;

DELETE FROM tbl2 WHERE pkid IN (SELECT pkid FROM tbl2_temp);
INSERT INTO tbl2 SELECT * FROM tbl2_temp;

COMMIT;

The log file produced below shows when the script exited displaying the message “Stopped because of errors”:

09:09:51  START Executing for: 'verticademos' [Vertica], Schema: public
09:09:51  SUCCESS  [@stop on error - 0.000 secs]  OK  
@STOP ON ERROR;
09:09:51  SUCCESS  [SET - 0 rows, 0.110 secs]  OK. No rows were affected  
SET SESSION AUTOCOMMIT TO OFF;
09:09:51  SUCCESS  [DELETE - 0 rows, 0.141 secs]  OK. No rows were affected  
DELETE FROM tbl1 WHERE pkid IN (SELECT pkid FROM tbl1_temp);
09:09:51  FAILED  [INSERT - 0 rows, 0.109 secs]  [Code: 2501, SQL State: 22004]  [Vertica][VJDBC](2501) ERROR: Cannot set a NOT NULL column (pkid) to a NULL value in INSERT/UPDATE statement  
INSERT INTO tbl1 SELECT * from tbl1_temp;
09:09:51  STOPPED  Stopped because of errors  
09:09:51  END Execution 4 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.360/0.000 secs   [3 successful, 1 errors]

Helpful links:
https://forum.vertica.com/discussion/239932/exiting-a-vsql-script-following-an-error
http://confluence.dbvis.com/display/UG100/Executing+SQL+Statements

Have fun!