Exiting a DbVisualizer Script Following an Error
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]