Shell Variables

FluidShell supports shell variables similar to Bash and gives you the power to use shell variables in your SQL statements (SQL Variable Expansion).

Creating local and global variables

1. declare and set

:$ declare myvar='My Value'

The above command would declare a local variable which is not available to child processes such as those opened via exec command.

set is an alias for declare command which also performs exactly the same function.

To set global variables such as those that could be used by child sub-processes, you can use the -x attribute along with the declare command.

:$ declare -x myGvar='My Value'

2. ask

ask allows you to define a variable interactively. You can prompt the user for the variable's value at runtime.

:$ ask -m "Enter value:" myVar
Enter value:Hello World!
:$ echo $myVar
Hello World!

3. export

export works in pretty much the same way as declare, however it always a global variable.

To list all variables within the current shell, you may execute set or declare or export

:$ set
CLI_SHELL_LINE_INTERPRETER_ERROR_ON_EXPLICIT_CMD_NOT_FOUND=true
CLI_SHELL_LINE_INTERPRETER_EVAL_COMMENT=sql
CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_CMD=true
CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_SQL=false
CLI_SHELL_LINE_INTERPRETER_EVAL_HISTORY=true
CLI_SHELL_LINE_INTERPRETER_EVAL_IMPLICIT_CMD=true
CLI_SHELL_LINE_INTERPRETER_EXPLICIT_CMD_CHAR=\
CLI_SHELL_LINE_INTERPRETER_EXPLICIT_SQL_CHAR=;
CLI_SHELL_LINE_INTERPRETER_IMPLICIT_BEHAVIOR=sql
CLI_SHELL_LINE_INTERPRETER_IS_AT_SIGN_GO=true
CLI_SHELL_LINE_INTERPRETER_IS_FORWARDSLASH_GO=true
CLI_SHELL_LINE_INTERPRETER_IS_SEMICOLON_GO=false
CLI_SHELL_LINE_INTERPRETER_PERFORM_ALIAS_EXPANSION=true
CONNECTIONS=C:\Users\vikram\.datastudio\connections

To print the value of a variable:

:$ echo $CLI_SHELL_LINE_INTERPRETER_IMPLICIT_BEHAVIOR
sql
:$ echo ${CLI_SHELL_LINE_INTERPRETER_IMPLICIT_BEHAVIOR}
sql

Note: ${myVar} and $myVar are functionally equivalent within the context of FluidShell.

Un-setting variables

unset

At times it may be required to remove or un-set a variable. FluidShell caters to this by providing the unset command

:$ echo $myVar1
My VAR 1
:$ unset myVar1
:$ echo $myVar1

:$ 

SQL Variable Expansion

FluidShell provides the flexibility to execute SQL commands within the same shell along with standard Bash commands and thus also allows the shell variables to be used within SQL statements directly. To make use of this feature, the variables should follow the ${myVar} syntax (with the braces). For instance:

:$ set name=Orders
:$ select * from ${name} << please note the use of braces here
:$ go

Variable expansion in SQL statements can be disabled by setting the SQL_VARIABLE variable to "0".

:$ set | grep SQL_VARI
:$ set SQL_VARIABLE=0

Quoting Variables

When setting variable values please note the single (') and double (") quote escape pattern which are similar to Bash.  Single quoted strings are literal values, while double quoted strings expand the special characters. For instance:

:$ declare myVar1="My VAR 1"
:$ echo $myVar1                                                          
My VAR 1
:$ echo "$myVar1"                                                                     
My VAR 1
:$ echo '$myVar1'                                                                     
$myVar1 << Expanded literally

Another example:

:$ declare myNewVar="$myVar1 Again"                                                    
:$ echo $myNewVar                                                                     
My VAR 1 Again << myVar1 expanded

Some observations regarding behaviour of quotes

• Enclosing characters in double quotes preserves the literal value of all characters within the quotes; with the exception of $, ` (backtick), \, and ! (when history expansion is enabled).

• The characters $ and ` retain their special meaning within double quotes. The backslash retains its special meaning only when followed by one of the following characters: $, `, ", \, or \n.

• A double quote may be quoted within double quotes by preceding it with a backslash. If enabled, history expansion will be performed unless an ! appearing in double quotes is escaped using a backslash. The backslash preceding the ! is not removed.

Pre-defined Variables

The following table defines pre-set variables and their function.

CONNECTIONS	Provides the directory name for "Local Database Servers" folder.
DATABASE	Contains database name which will be used by \connect and \reconnect commands.
DATABASEUSERNAME	This variable contains the current database user name, when connected.
HISTCONTROL	This variable allows control over how FluidShell stores the command history.
HISTFILE	Provides the location of the command buffer file.
HISTFILESIZE	This variable defines the maximum number of commands that will be retained (in the physical file) by FluidShell when it exits.
HISTSAVE	Determines whether the command history should be saved on disk or not.
HISTSIZE	Number of lines to be kept in the command history buffer.
HOME	Home directory of the current user.
IS_CONNECTED	This variable stores the connection status of the FluidShell.
LINENO	An internal variable which contains the current line number within the SQL buffer.
MAX_ROWS	Determines the maximum number of rows returned by the \go command.
PROMPT	Allows changing the command prompt.
SERVERNAME	This variable contains the current connection server name, when connected.
SHELL_VERSION	Contains the FluidShell version number.
SQL_VARIABLES	Determines whether the variables should be expanded in SQL statements or not.
USER	This variable contains the name of user currently logged-in (to this machine).
USERNAME	This variable contains the current connection user name, when connected.

CLI Settings

The following are the default settings for the Command Line Shell mode. These determine how \cli shell interprets sql statements and commands. See the \cli page for more on the three available modes ( \cli fluid, \cli shell, \cli sql). By default, the FluidShell is in \cli fluid mode.

• CLI_SHELL_LINE_INTERPRETER_ERROR_ON_EXPLICIT_CMD_NOT_FOUND="true"

  Reports an error if it cannot find an explicit command that is preceded by \. For instance;

  :$ \expert
  expert: command not found 
  

• CLI_SHELL_LINE_INTERPRETER_EVAL_COMMENT="shell"

  Evaluates a line to a comment with a leading #. For instance;

  :$ #expert
  :$ 
  

• CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_CMD="true"

  Allows use of fluidshell commands without a preceding \

  :$ clear << Notice that there is no "\" before the command
  :$ 
  

• CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_SQL="true"

  Disables explicit SQL, meaning the preceding character in CLI_SHELL_LINE_INTERPRETER_EXPLICIT_SQL_CHAR="" is required for interpreting SQL statements.

  For instance in this case the CLI_SHELL_LINE_INTERPRETER_EXPLICIT_SQL_CHAR is ";"

  :$ ;select count(*) from dbo.products                                                                                
  :$ ;go                                                                                                               
  
   column1   
   ----------
   77        
  
   1 record(s) selected [Fetch MetaData: 100ms] [Fetch Data: 48ms] 
  
   [Executed: 11/1/2012 12:14:36 PM] [Execution: 182ms] 
   
  

• CLI_SHELL_LINE_INTERPRETER_EVAL_HISTORY="true"

  Enables the command history

• CLI_SHELL_LINE_INTERPRETER_EVAL_IMPLICIT_CMD="true"

  Evaluates commands when they are not preceded by \

• CLI_SHELL_LINE_INTERPRETER_EXPLICIT_CMD_CHAR="\\"

  Determines which character must precede a command for it to be interpreted when CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_CMD="true"

• CLI_SHELL_LINE_INTERPRETER_EXPLICIT_SQL_CHAR=";"

  Determines which character must precede an SQL statement for it to be interpreted when CLI_SHELL_LINE_INTERPETER_EVAL_EXPLICIT_SQL="true"

• CLI_SHELL_LINE_INTERPRETER_IMPLICIT_BEHAVIOR="shell"

  Determines which mode

• CLI_SHELL_LINE_INTERPRETER_IS_AT_SIGN_GO="true"

  Determines if @ can be used as a statement separator. For instance;

  :$ select count(*) from dbo.products@                                                                                
  
   column1   
   ----------
   77        
  
   1 record(s) selected [Fetch MetaData: 100ms] [Fetch Data: 48ms] 
  
   [Executed: 11/1/2012 12:14:45 PM] [Execution: 182ms] 
   
  

• CLI_SHELL_LINE_INTERPRETER_IS_FORWARDSLASH_GO="true"

  Determines if / can be used as a statement separator. For instance;

  :$ select count(*) from dbo.products/                                                                                
  
   column1   
   ----------
   77        
  
   1 record(s) selected [Fetch MetaData: 100ms] [Fetch Data: 48ms] 
  
   [Executed: 11/1/2012 12:14:36 PM] [Execution: 182ms] 
   
  

• CLI_SHELL_LINE_INTERPRETER_IS_SEMICOLON_GO="false"

  Determines if ; can be used as a statement separator

• CLI_SHELL_LINE_INTERPRETER_PERFORM_ALIAS_EXPANSION="true"

  Allows alias expansion

Furthermore, the behaviour of CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_CMD and CLI_SHELL_LINE_INTERPRETER_EVAL_IMPLICIT_CMD variables is dependent on the CLI_SHELL_LINE_INTERPRETER_IMPLICIT_BEHAVIOR variable (which defines the mode in which the FluidShell interpreter is operating). The following table outlines the aforementioned behaviour;

CLI...IMPLICIT_BEHAVIOR	CLI..._EVAL_EXPLICIT_CMD	CLI..._EVAL_IMPLICIT_CMD	Sample command
sql	TRUE	TRUE	ls command gets executed. \ls command gets executed.
sql	TRUE	FALSE	ls command is not executed and is stored in the SQL Buffer. \ls command gets executed.
sql	FALSE	TRUE	ls command gets executed. \ls command gets executed.
sql	FALSE	FALSE	ls command is not executed and is stored in the SQL Buffer. \ls command is not executed and is stored in the SQL Buffer. \declare and go are the only two commands which are executed under this configuration.
shell	TRUE/FALSE	TRUE/FALSE	All commands are executed.

SQL Results Options

You can set your preferences on how SQL results are displayed in Fluidshell using the \set command.

To display SQL Statement, use the command	\set CMD_GO_SHOW_SQL
Display column headers	\set CMD_GO_SHOW_COLUMNHEADERS
Display query statistics	\set CMD_GO_SHOW_QUERYSTATS
Display warnings	\set CMD_GO_SHOW_WARNINGS
Display warnings headers	\set CMD_GO_SHOW_WARNINGHEADERS
Always display 'records affected' count	\set CMD_GO_SHOW_ALWAYSDISPLAYSUCCESS
Display only the total number of affected records	\set CMD_GO_SHOW_SUM
Margin character length	\set CMD_GO_SHOW_MARGINCHARLENGTH
Display client statistics	\set CMD_GO_SHOW_CLIENTSTATS
Display SQL results as form entries	\set CMD_GO_SHOW_RESULTSASFORM
Display when execution stops on an error	\set CMD_GO_SHOW_EXEC_STOPONERROR

Aliases

The following table lists command aliases defined in the FluidShell:

Alias	Command
go	\go
GO	\go
ls	\ls -l
man	\help
set	\declare