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
Error checking / capture
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Error checking / capture
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:
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"
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Error checking / capture
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
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Error checking / capture
The Vertica 6.0 Programmer's Guide mentions the ON_ERROR_STOP vsql variable:
See page 233ON_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).
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Error checking / capture
Thanks, just what I need, does this also apply to version 5.0.7?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Error checking / capture
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:
Just add this line to the beginning of your script:
Code: Select all
\set ON_ERROR_STOP on
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.