Exit codes for nzsql on Windows systems

From a Windows command prompt, the exit codes from the nzsql command provide information about the execution status of the SQL commands.

When you are running queries interactively at the nzsql prompt or by using a script (nzsql -f), the command returns one of the following exit codes:

0: All queries were successful.
1: At least one query was successful.
2: All queries failed.

For example, the mytest.sql file contains a query that selects from an invalid table (T11):

d:\win\nzsql_win32> nzsql.exe -host nzhost -c "select * from t11"
ERROR:  relation does not exist SYSTEM.ADMIN.T11
[user@nzhost nz]$ echo %errorlevel%
-1

When you are running a query with the nzsql -c command, the exit codes are:

0: Query succeeded
-1: SQL failed (issues with syntax, failed authentication, permissions)
-2: Connection failed
-3: User cancellation request
-4: Session was terminated

Note: When you are running nzsql from a Windows system in a Cygwin UNIX emulator environment, the exit code is 127 for any of these errors.

The nzsql -f command runs all statements in the file. If you want the nzsql command to stop and exit when it encounters an error with a query, include -v ON_ERROR_STOP=1 on the command line. The exit code is 1 if there were successful queries before the failed query, or 2 if the first query failed.

If you create an ON_ERROR_STOP block inside a query file, as in this example:

<initial SQL queries>
\set ON_ERROR_STOP ON
     <queries inside block> 
\unset ON_ERROR_STOP
<trailing SQL queries>

The success or failure of queries in the initial SQL command section or the trailing SQL command section are ignored when there is an ON_ERROR_STOP block. The success or failure of the commands inside the block determine the exit value. The exit codes are 0 (all queries in the block succeeded), 1 (some queries were successful), or 2 (all queries inside the block failed). If you do not include the command to unset the ON_ERROR_STOP block, the block ends at the end of the file.