Error checking / capture

[Timbo]

Hi, I'm very new to Vertica. I want to run a batch of SQL statements, but if there is a failure it should exit, is this easy to do? For example in Oracle you would use "WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING ..... is there something similar in Vertica?

Running the following type of processing:-

Create Projection......
select refresh([table])
select make_ahm_now()
drop Projection

If there are any errors I don't want it to drop the projection.

Regards
Tim

[JimKnicely]

Hi,

Welcome to the forums!

One solution may be to create a shell script and then check for errors after each call to Vertica. You would exit the script if an error occurs when running a command against Vertica.

For instance, something like this:

Code: Select all

/opt/vertica/bin/vsql -c "Create Projection......"
if [ "$?" = "1" ]; then
  echo "An error occurred"
  exit 1
fi
/opt/vertica/bin/vsql -c "select refresh([table])"
if [ "$?" = "1" ]; then
  echo "An error occurred"
  exit 1
fi
/opt/vertica/bin/vsql -c "select make_ahm_now()"
if [ "$?" = "1" ]; then
  echo "An error occurred"
  exit 1
fi
/opt/vertica/bin/vsql -c "drop Projection"

[Timbo]

Hi Jim, Thanks for the reply, is there nothing that can be done internally with in vsql? How would I check the return code after each step within vsql and exit if there is an error?

The SQL that I have creates about 50 new projections and drops the old ones, spliting this out would make a horrible shell script.

Tim

[JimKnicely]

The Vertica 6.0 Programmer's Guide mentions the ON_ERROR_STOP vsql variable:

ON_ERROR_STOP
By default, if non-interactive scripts encounter an error, such as a malformed SQL command or internal meta-command, processing continues. This has been the traditional behavior of vsql but it is sometimes not desirable. If this variable is set, script processing immediately terminates. If the script was called from another script it terminates in the same manner. If the outermost script was not called from an interactive vsql session but rather using the -f (see "f filename --file filename" on page 208) option, vsql returns error code 3, to distinguish this case from fatal error conditions (error code 1).

See page 233

[Timbo]

Thanks, just what I need, does this also apply to version 5.0.7?

[JimKnicely]

It is also listed in the 5.0 doc so it should apply to Vertica 5.0.7.

Just add this line to the beginning of your script:

Code: Select all

\set ON_ERROR_STOP on