------ Proposal ------

We should support for both sql exception and cmd exception.

Names:
STOP_ON_SQL_EXCEPTION=<true|false>
STOP_ON_CMD_EXCEPTION=<true|false>

Should we prepend CLI_SHELL_LINE_INTERPRETER to the above names? I'm not clear when we do & don't prepend this prefix.

In terms of order of precedence, if this variable is set, I think it should override any other configured option. Also, when an FS window is opened, I think we should initialize these variables based upon the corresponding FS Options setting.

The source command bring up an interesting dilemma as it has a specific option to override this behavior. Would like to get your thoughts on this but my thinking is: if the source command is invoked w/ either of these options, the effect is that it initializes these new variables to the values specified in the source command. However, a script can choose to override it, by setting these values in the script itself.

====== Implementation Notes ======

After further drill down, I realized that options defined by
  'File -> Options -> FluidShell -> Stop on SQL/Command Exception'
and
  'Server Property File -> FluidShell -> Stop on SQL/Command Exception'
are only applied to the global initialization script and server initialization script respectively; these option settings would never affect the script executed from the FS command line or executed via the \source command.

Script executed from the FS command line or executed via the \source command always use the values specified by -ss (stop on sql exception) or -sc (stop on command exception); if these options are not supplied, the system defaults are used: true for 'stop on sql exception' and false for 'stop on command exception'.  They never reference those options defined by 'File -> Options -> ...' or 'Server Property File -> ...'.

For more information on above behavior, please see 11/14/2012 comment in issue #7905:

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/issue/7905

For implementing the feature requested by issue #8175, here is my thought:
(1) After a FS is opened in GUI or a FS command line is executed, no shell variables will be set before initialization script(s) are executed; we will let initialization script(s) run without shell variables defined.
(2) After initialization script(s) finished execution, there are two scenarios: stop-on-exception variables are defined (user might set these variables in the initialization scripts) or not defined.
(3-a) If stop-on-exception variables are not defined after initialization scripts finished: for FS opened in GUI, we will set stop-on-exception variables based on system defaults; for FS command line, we will set stop-on-exception variables based on command line -ss/-sc options, system defaults are used if these options are not supplied.
(3-b) If stop-on-exception variables are defined after initialization scripts finished: for FS opened in GUI, do nothing; for FS command line, if -ss/-sc options are supplied, then overwrite defined variables based on -ss/-sc values, otherwise, do nothing.

For execution of a script via the \source command: when \source command is executed, the stop-on-exception variables may be defined or may not be defined. In either case, \source command will not change shell's state unless -ss/-sc options are supplied. If -ss/-sc options are supplied, \source will update stop-on-exception variables accordingly if variables are defined; otherwise, do nothing.

In addition, \source command can be executed nested, in this case, stop-on-exception variables set by parent could be changed by its child. There is nothing ADS can do on this at the moment.

After a command defined in the script is executed and an exception is thrown (where script can be an initialization script, a command line script or a script executed via the \source command): ADS will check if the stop-on-exception variable is defined, if yes, then exception is handled based on the value of the stop-on-exception variable; if not defined, exception is handled based on the value specified in the attribute-object.
 

Two variables are introduced to support the requested feature:
   SCRIPT_STOP_ON_CMD_EXCEPTION
   SCRIPT_STOP_ON_SQL_EXCEPTION

Valid BOOLEAN values for setting these variables are:
   'true', 'false', 'yes', 'no', 'y', 'n', 'on', 'off', '1', '0'.
If user set these variables manually with a value that is not listed above, it is evaluated to false.

SVN r30896 - 13.0 branch
SVN r30897 - trunk


In your test case, you can achieve what you were looking for as:
   set SCRIPT_STOP_ON_SQL_EXCEPTION=false
   DROP TABLE ...
   GO

   set SCRIPT_STOP_ON_SQL_EXCEPTION=true
   CREATE TABLE ...
   GO

SVN r30912 - merged r30896 change from 13.0 branch to 12.0 branch

The explanation from:

For execution of a script via the \source command: when \source command is executed, the stop-on-exception variables may be defined or may not be defined. In either case, \source command will not change shell's state unless -ss/-sc options are supplied. If -ss/-sc options are supplied, \source will update stop-on-exception variables accordingly if variables are defined; otherwise, do nothing.

seems to conflict with part of the source cmd's manual, i.e.:

       -sc BOOLEAN

              Stop on any exception occurred in a FluidShell command, default to 'false'.

       -ss BOOLEAN

              Stop on any exception occurred in an SQL statement, default to 'true'.

Additionally, as tested with ADS 13 RC 1 and ADS 12.0.15, the -sc and -ss options seem not to work for the source command, the options don't affect the behavior of the command, only the values from the SCRIPT... vars are considered.

(1) Made change so that the default behavior of the \source command is preserved.

SVN r31101 - 12.0 branch
SVN r31102 - 13.0 branch
SVN r31104 - trunk

Now, upon execution of a script via the \source command:
(1-1) if SCRIPT_STOP_ON_xxx_EXCEPTION variables are defined, \source will update these variables based on its settings: if -ss/-sc options are supplied, \source will update corresponding variables accordingly; if -ss/-sc options are not supplied, \source's default settings are used to update these variables.
(1-2) if SCRIPT_STOP_ON_xxx_EXCEPTION variables are not defined, \source simply proceeds and will not create these variables.


(2) >>Additionally, as tested with ADS 13 RC 1 and ADS 12.0.15, the -sc and -ss options seem not to work for the source command, the options don't affect the behavior of the command, only the values from the SCRIPT... vars are considered.

I was not able to reproduce this.  Did you place the -sc/-ss options before the script name on the command line as "\source -ss off -sc on script_name"?  Note that placing -ss/-sc options after the script name as "\source script_name -ss OFF -sc ON" won't work, this is because '-ss OFF -sc ON' will be passed to script_name as script's arguments but not \source's arguments.

If you did place -sc/-ss in front of script_name, can you try the test case described below?

(a) Open a fluid shell from a server node.
(b) In the newly created FS shell, cd to a directory where you usually perform your test.
(c) Create a script, tmp.sh, with the following contents:
echo TEST BEGIN
SELECT * from table_does_not_exist_1
go
SELECT * from table_does_not_exist_2
go
echo TEST END
(d) execute the following commands in FS shell, where > is shell prompt and # means command's output:
> session
# make sure the connection to the DB server is established.
> declare SCRIPT_STOP_ON_SQL_EXCEPTION=true
> declare | grep SCRIPT_STOP_ON_SQL
# SCRIPT_STOP_ON_SQL_EXCEPTION=true
> source -ss OFF tmp.sh
# two SQL error messages are displayed and all of commands in tmp.sh should be executed even SCRIPT_STOP_ON_SQL_EXCEPTION is set to 'true' before the script execution (will be overwritten by '-ss OFF').
> declare | grep SCRIPT_STOP_ON_SQL
# SCRIPT_STOP_ON_SQL_EXCEPTION=OFF (this variable's value is changed from 'true' to 'OFF' by "-ss OFF")
> source -ss yes tmp.sh
# only one SQL error message is displayed and tmp.sh will stop execution on the first error even SCRIPT_STOP_ON_SQL_EXCEPTION is set to 'OFF' before the script execution.
> declare | grep SCRIPT_STOP_ON_SQL
# SCRIPT_STOP_ON_SQL_EXCEPTION=yes (this variable's value is changed from 'OFF' to 'yes' by "-ss yes")

If you see different output than the ones shown above from your test, please stop here and send me your output. Otherwise, let's try a few more commands, the result from commands below might be the reason why you said -ss/-sc do not seem to work.

> declare SCRIPT_STOP_ON_SQL_EXCEPTION=false
> declare | grep SCRIPT_STOP_ON_SQL
# SCRIPT_STOP_ON_SQL_EXCEPTION=false
> disconnect
# disconnect: Disconnected
> source -ss yes tmp.sh
# two errors generated and all of commands defined in tmp.sh are executed

You might wonder why the two SELECT statements both get executed even "-ss yes" is passed to the \source command.  This is because the exception "go: Connection to RDBMS not established" thrown from the \go command is not an SQL exception.  In the current ADS/FS implementation, only SQL exceptions are evaluated/applied to -ss and SCRIPT_STOP_ON_SQL_EXCEPTION.

(1) Verified using ADS 12.0.5-9 & ADS 14 Dev 8, works fine.

(2) Here was my mistake, I put the file name first. Testing the described scenario, it works as expected.

Closed.