Substitution Variables

Substitution variables are words that have been defined to be equivalent to some other text, so that when they are used, those words will be replaced (substituted) by the other text in a SQL statement or metacommand before that statement or metacommand is executed. Substitution variables can be defined using the SUB metacommand, as follows:

SUB <match_string> <replacement_string>

The <match_string> is the word (substitution variable) that will be matched, and the <replacement_string> is the text that will be substituted for the matching word. Substitution variables are only recognized in SQL statements and metacommands when the match string is preceded and followed by two exclamation points (“!!”). For example:

-- !x! SUB author Date
create or replace temporary view docs as
select * from documents
where author = '!!author!!';

Substitution variable names may contain only letters, digits, and the underscore character. Substitutions are processed in the order in which they are defined. Substitution variable definitions can themselves include substitution variables. SQL statements and metacommands may contain nested references to substitution variables, as illustrated in Example 7. Complex expressions using substitution variables can be evaluated using SQL, as illustrated in Example 16.

In addition to user-defined substitution variables, there are three additional kinds of substitution variables that are defined automatically by execsql or by specific metacommands. These are system variables, data variables, and environment variables. System, data, and environment variable names are prefixed with “$”, “@”, and “&” respectively. Because these prefixes cannot be used when defining substitution variables with the SUB metacommand, system variable, data variable, and environment variable names will not conflict with user-created variable names.

System Variables

Several special substitutions (pairs of matching strings and replacement strings) are automatically defined and maintained by execsql. The names and definitions of these substitution variables are:

$ARG_x

The value of a substitution variable that has been assigned on the command line using the “-a” command-line option. The value of <x> must be an integer greater than or equal to 1. See Example 9 for an illustration of the use of “$ARG_x” variables.

$AUTOCOMMIT_STATE

A value indicating whether or not execsql will automatically commit each SQL statement as it is executed. This will be either “ON” or “OFF”. The autocommit state is database specific, and the value applies only to the database currently in use.

$CANCEL_HALT_STATE

The value of the status flag that is set by the CANCEL_HALT metacommand. The value of this variable is always either “ON” or “OFF”. A modularlized sub-script can use this variable to access and save (in another substitution variable) the CANCEL_HALT state before changing it, so that the previous state can be restored.

$CONSOLE_WAIT_WHEN_DONE_STATE

The value of the status flag that is set by the console_wait_when_done configuration setting or by the CONSOLE WAIT_WHEN_DONE metacommand. The value of this variable is always either “ON” or “OFF”.

$CONSOLE_WAIT_WHEN_ERROR_STATE

The value of the status flag that is set by the console_wait_when_error_halt configuration setting or by the CONSOLE WAIT_WHEN_ERROR metacommand. The value of this variable is always either “ON” or “OFF”.

$COUNTER_x

An integer value that is automatically incremented for every command that references the counter variable. As many counter variables as desired can be used. The value of x must be an integer that identifies the counter variable. Counter variable names do not have to be used sequentially. The first time that a counter variable is referenced, it returns the value 1. If a counter variable is referenced multiple times in one command, each reference will have the same value. The RESET COUNTER and RESET COUNTERS metacommands can be used to reset counter variables. See examples 6, 7, 11, and 19 for illustrations of the use of counter variables.

$CURRENT_ALIAS

The alias of the database currently in use, as defined by the CONNECT metacommand, or “initial” if no CONNECT metacommand has been used. This value will change if a different database is USEd.

$CURRENT_DATABASE

The DBMS type and the name of the current database. This value will change if a different database is USEd.

$CURRENT_DBMS

The DBMS type of the database in use. This value may change if a different database is USEd.

$CURRENT_DIR

The full path to the current directory. The value will not have a directory separator character (i.e., “/” or “") at the end.

$CURRENT_SCRIPT

The file name of the script from which the current command originated. This value will change if a different script is INCLUDEd. This file name may or may not include a path, depending on how the script file was identified on the command line or in an INCLUDE metacommand.

$CURRENT_SCRIPT_NAME

The base file name, without a path, of the script from which the current command originated. This value will change if a different script is INCLUDEd.

$CURRENT_SCRIPT_PATH

The complete path of the script from which the current command originated, including a terminating path separator character. This value will change if a different script is INCLUDEd.

$CURRENT_TIME

The date and time at which the current script line is run. See Example 3 for an illustration of its use.

$DATE_TAG

The date on which execsql started processing the current script, in the format YYYYMMDD. This is intended to be a convenient short form of the date that can be used to apply sequential version indicators to directory names or file names (e.g., of exported data). See Example 2 for an illustration of its use.

$DATETIME_TAG

The date and time at which execsql started processing the current script, in the format YYYYMMDD_hhmm. This is intended to be a convenient short form of the date and time that can be used to apply sequential versions to directory names or file names. See Example 8 for an illustration of its use.

$DB_NAME

The name of the database currently in use, as specified on the command line or in a CONNECT metacommand. This will be the database name for server-based databases, and the file name for file-based databases.

$DB_NEED_PWD

A string equal to “TRUE” or “FALSE” indicating whether or not a password was required for the database currently in use.

$DB_SERVER

The name of the database server for the database currently in use, as specified on the command line or in a CONNECT metacommand. If the database in use is not server-based, the result will be an empty string.

$DB_USER

The name of the database user for the database currently in use, as specified on the command line or in a CONNECT metacommand. If the database connection does not require a user name, the result will be an empty string.

$ERROR_HALT_STATE

The value of the status flag that is set by the ERROR_HALT metacommand. The value of this variable is always either “ON” or “OFF”. A modularlized sub-script can use this variable to access and save (in another substitution variable) the ERROR_HALT state before changing it, so that the previous state can be restored.

$ERROR_MESSAGE

The message generated by any error, as it would be printed on the terminal by default. This is initially an empty string, and is set by any SQL error or metacommand error. If an error occurs, the error message is only accessible if the ERROR_HALT OFF or METACOMMAND_ERROR_HALT OFF metacommands have been used, or in an ON ERROR_HALT EMAIL or ON ERROR_HALT WRITE metacommand.

$LAST_ERROR

The text of the last SQL statement that encountered an error. This value will only be available if the ERROR_HALT OFF metacommand has been used.

$LAST_ROWCOUNT

The number of rows that were affected by the last INSERT, UPDATE, or SELECT statement. Note that support for $LAST_ROWCOUNT varies among DBMSs. For example, for SELECT statements, Postgres provides an accurate count, SQLite always returns -1, and Firebird always returns 0.

$LAST_SQL

The text of the last SQL statement that ran without error.

$METACOMMAND_ERROR_HALT_STATE

The value of the status flag that is set by the METACOMMAND_ERROR_HALT metacommand. The value of this variable is always either “ON” or “OFF”.

$OS

The name of the operating system. This will be “linux”, “windows”, “cygwin”, “darwin”, “os2”, “os2emx”, “riscos”, or “atheos”.

$PYTHON_EXECUTABLE

The path and name of the Python interpreter that is running execsql. This can be used with the SYSTEM_CMD metacommand to run a Python program in an operating-system-independent manner.

$RANDOM

A random real number in the semi-open interval [0.0, 1.0).

$RUN_ID

The run identifier that is used in execsql’s log file.

$SCRIPT_LINE

The line number of the current script for the current command.

$SCRIPT_START_TIME

The date and time at which execsql started processing the current script. This value never changes within a single run of execsql.

$STARTING_SCRIPT

The file name of the script specified on the command line when execsql is run. This value never changes within a single run of execsql. This file name may or may not include a path, depending on how it was specified on the command line.

$STARTING_SCRIPT_NAME

The base file name of the script specified on the command line when execsql is run, without any path specification. This value never changes within a single run of execsql. This may or may not be the same as $STARTING_SCRIPT; the latter may include a path.

$SYSTEM_CMD_EXIT_STATUS

The exit status of the command executed by the SYSTEM_CMD metacommand. The value is “0” (zero) prior to the first use of the SYSTEM_CMD metacommand.

$TIMER

The elapsed time of the script timer. If the TIMER ON command has never been used, this value will be zero. If the timer has been started but not stopped, this value will be the elapsed time since the timer was started. If the timer has been started and stopped, this value will be the elapsed time when the timer was stopped.

$USER

The name of the person logged in when the script is started. This is not necessarily the same as the user name used with any database.

$UUID

A random 128-bit Universally Unique Identifier in the canonical form of 32 hexadecimal digits.

The system variables can be used for conditional execution of different SQL commands or metacommands, and for custom logging of a script’s actions using the WRITE metacommand.

Data Variables

Two metacommands, SELECT_SUB and PROMPT SELECT_SUB, will each create a set of substitution variables that correspond to the data values in a single row of a data table. The column names of the data table, prefixed with “@”, will be automatically assigned as the names of these data variables. The prefix of “@” cannot be assigned using SUB or similar metacommands, and so will prevent data variables from overwriting any user-defined substitution variables that may have the same name as a data table column. See Example 8 for an illustration of the use of a data variable. All assignments to data variables are automatically logged.

Evironment Variables

The operating system environment variables that are defined when execsql starts will be available as substitution variables prefixed with “&”. New environment variables cannot be added by any metacommand.

Metacommands to Assign Substitution Variables

In addition to the SUB metacommand, several other metacommands can be used to define substitution variables based on values in a data table, user input, or a combination of the two. All of the metacommands that can be used to define substitution variables are:

PROMPT DIRECTORY

Opens a dialog box and prompts the user to identify an existing directory on the file system. The name of the substitution variable is specified in the metacommand, and the full path to the selected directory will be used as the replacement string.

PROMPT ENTER_SUB

Opens a dialog box and prompts the user to interactively enter the text that will be used as a replacement string. The name of the substitution variable is specified in the metacommand.

PROMPT ENTRY_FORM

Displays a custom data entry form and assigns each of the values entered to a specified substitution variable.

PROMPT OPENFILE

Opens a dialog box and prompts the user to select an existing file. The name of the substitution variable is specified in the metacommand, and the full path to the selected file will be used as a replacement string.

PROMPT SAVEFILE

Opens a dialog box and prompts the user to enter the name of a new or existing file; the full path to this file will be used as a replacement string.

PROMPT SELECT_SUB

Opens a dialog box, displays a data table or view, and prompts the user to select a row. The data values on the selected row will be assigned to a set of data variables.

SELECT_SUB

The data values on the first row of a specified table or view will be assigned to a set of data variables. No prompt is displayed.

SUB

Directly assigns a replacement string to a substitution variable.

SUB_APPEND

Appends text to a substitution variable. The appended text is separated from the existing text with a newline.

SUB_TEMPFILE

Assigns a temporary file name to the specified substitution variable.

SUBDATA

The data value in the first column of the first row of a specified table or view will be assigned to a user-specified substitution variable.

Substitution variables can also be defined in configuration files.