on_error not respected in non-interactive mode/exit code is 0

[highlyunavailable]

If I run a file with syntax errors using non-interactive mode, mssql-cli doesn't stop execution after the first error nor does it return a non-zero exit code:

Output looks like:

Incorrect syntax near ''.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
Commands completed successfully.

[ellbosch]

Hi @highlyunavailable, sorry about this. Can you please provide the following:

• The script used to repro the error
• Your version of mssql-cli (you can get this by calling mssql-cli --version)
• Your OS
• Your version of Python, if pip was used to install mssql-cli

[highlyunavailable]

* The script used to repro the error

Any script with invalid syntax works. An example is:

this_is_invalid;
select @@version;

Save to invalid_example.sql.

Run with: mssql-cli --less-chatty -i invalid_example.sql

I'm providing all server information with environment variables, so add that as appropriate.

Example powershell script to repro:

$env:MSSQL_CLI_SERVER = "yourserver"
$env:MSSQL_CLI_DATABASE = "yourdb"
$env:MSSQL_CLI_USER = "youruser"     
$env:MSSQL_CLI_PASSWORD = 'yourpassword'
mssql-cli --less-chatty -i invalid_example.sql
$LASTEXITCODE

This also repros in interactive mode:

my_db> this_is_invalid;
...................... select @@version;
Time: 1.072s (a second)
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'this_is_invalid'.
+--------------------+
| (No column name)   |
|--------------------|
| Microsoft SQL Azure (RTM) - 12.0.2000.8
        Apr  9 2020 16:39:55
        Copyright (C) 2019 Microsoft Corporation
                    |
+--------------------+
(1 row affected)
my_db>

My config file has:

# Error handling
# When one of multiple SQL statements causes an error, choose to either
# continue executing the remaining statements, or stopping
# Possible values "STOP" or "RESUME"
on_error = STOP

The actual (names obfuscated) command I was running was similar to:

IF (DATABASE_PRINCIPAL_ID('foo') IS NULL)
BEGIN
    CREATE ROLE [foo]
        AUTHORIZATION [db_owner];
END;

IF (SCHEMA_ID('bar') IS NULL)
BEGIN
    EXEC ('CREATE SCHEMA [bar] AUTHORIZATION [dbo];')
END;

GRANT SELECT
    ON SCHEMA::[bar] TO [foo];

The actual syntax errors are that there is a semicolon after AUTHORIZATION [db_owner] and after the END statements and there apparently shouldn't be.

* Your version of mssql-cli (you can get this by calling `mssql-cli --version`)

Version: 0.18.2

* Your OS

Repros on Windows 10 and Ubuntu 18.04

* Your version of Python, if `pip` was used to install mssql-cli

Python 3.8.2

[airlobster]

Here is the fix I added to resolve that: In 'mssql_cli.py' file, go to line 565 (the if is_error block), replace the continue instruction with: raise Exception(status)

Simple.

Since I'm mostly using mssql-cli in a non-interactive mode, usually embedded in other scripts, this fix suits my needs perfectly, and I don't mind failing after the first error is encountered, as long as the exit-code perfectly reflects the situation.