Exiting a DbVisualizer Script Following an Error: Quick Tip

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 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!