A multi-DBMS SQL script processor
execsql.py

Contents

execsql.py is a Python program that applies a SQL script stored in a text file to a PostgreSQL, MS-Access, SQLite, MS-SQL-Server, MySQL, MariaDB, or Firebird database, or an ODBC DSN. execsql.py also supports a set of special commands (metacommands) that can import and export data, copy data between databases, and conditionally execute SQL statements and metacommands. These metacommands make up a control language that works the same across all supported database management systems (DBMSs). The metacommands are embedded in SQL comments, so they will be ignored by other script processors (e.g., psql for Postgres and sqlcmd for SQL Server). The metacommands make up a toolbox that can be used to create both automated and interactive data processing applications; some of these uses are illustrated in the examples.

Capabilities

You can use execsql to:

execsql is inherently a command-line program that can operate in a completely non-interactive mode (except for password prompts). Therefore, it is suitable for incorporation into a toolchain controlled by a shell script (on Linux), batch file (on Windows), or other system-level scripting application. When used in this mode, the only interactive elements will be password prompts; passwords are not accepted on the command line or as arguments to the CONNECT metacommand. However, several metacommands can be used to generate interactive prompts and data displays, so execsql scripts can be written to provide some user interactivity.

In addition, execsql automatically maintains a log that documents key information about each run of the program, including the databases that are used, the scripts that are run, and the user's choices in response to interactive prompts. Together, the script and the log provide documentation of all actions carried out that may have altered data.

Syntax and Options

Commands: execsql.py -tp [other options] <sql_script_file> <Postgres_host> <Postgres_db> execsql.py -tl [other options] <sql_script_file> <SQLite_db> execsql.py -tf [other options] <sql_script_file> <Firebird_host> <Firebird_db> execsql.py -ta [other options] <sql_script_file> <Access_db> execsql.py -tm [other options] <sql_script_file> <MySQL_host> <MySQL_db> execsql.py -ts [other options] <sql_script_file> <SQL_Server_host> <SQL_Server_db> execsql.py -td [other options] <sql_script_file> <DSN_name> Arguments: <sql_script_file> The name of a text file of SQL commands to be executed. Required argument. <Postgres_host> The name of the Postgres host (server) against which to run the SQL. <Postgres_db> The name of the Postgres database against which to run the SQL. <SQLite_db> The name of the SQLite database against which to run the SQL. <Firebird_host> The name of the Firebird host (server) against which to run the SQL. <Firebird_db> The name of the Firebird database against which to run the SQL. <MySQL_host> The name of the MySQL or MariaDB host (server) against which to run the SQL. <MySQL_db> The name of the MySQL or MariaDB database against which to run the SQL. <SQL_Server_host> The name of the SQL Server host (server) against which to run the SQL. <SQL_Server_db> The name of the SQL Server database against which to run the SQL. <Access_db> The name of the Access database against which to run the SQL. <DSN_name> The name of a DSN data source against which to run the SQL. Options: -a <value> Define the replacement for a substitution variable $ARG_x. -b <value> Control whether input data columns containing only 0 and 1 are treated as Boolean or integer: 'y'-Yes (default); 'n'-No. -d <value> Make directories used by the EXPORT metacommand: 'n'-No (default); 'y'-Yes. -e <value> Character encoding of the database. Only used for some database types. -f <value> Character encoding of the script file. -g <value> Character encoding to use for output of the WRITE and EXPORT metacommands. -i <value> Character encoding to use for data files imported with the IMPORT metacommand. -m Display the allowable metacommands, and exit. -p <value> The port number to use for client-server databases. -s <value> The number of lines of an IMPORTed file to scan to diagnose the quote and delimiter characters. -t <value> Type of database: 'p'-Postgres, 'l'-SQLite, 'f'-Firebird, 'm'-MySQL, 's'-SQL Server, 'a'-Access, 'd'-DSN. -u <value> The database user name. -v <value> Use a GUI for interactive prompts. -w Do not prompt for the password when the user is specified. -y List all valid character encodings and exit. -z <value> Buffer size, in kb, to use with the IMPORT metacommand (the default is 32).

If the database type and connection information is specified in a configuration file, then the database type option and the server and database name can be omitted from the command line. The absolute minimum information that must be specified on the command line is the name of the script file to run.

Following are additional details on some of the command-line options:

-a
This option should be followed by text that is to be assigned to a substitution variable. Substitution variables can be defined on the command line to provide data or control parameters to a script. The "-a" option can be used repeatedly to define multiple substitution variables. The value provided with each instance of the "-a" option should be a replacement string. execsql will automatically assign the substitution variable names. The substitution variable names will be "$ARG_1", "$ARG_2", etc., for as many variables are defined on the command line. Use of the "-a" option is illustrated in Example 9. Command-line substitution variable assignments are logged.
-e, -f, -g, -i
These options should each be followed by the name of a character encoding. Valid names for character encodings can be displayed using the "-y" option.
-p
A port number should be provided if the DBMS is using a port different from the default. The default port numbers are:
  • Postgres: 5432
  • SQL Server: 1433
  • MySQL: 3306
  • Firebird: 3050
-u
The name of the database user should be provided with this option for password-protected databases; execsql will prompt for a password if a user name is provided, unless the "-w" option is also specified.
-v
This option should be followed by an integer indicating the level of GUI interaction that execsql should use. The values allowed are:
  • 0: Use the terminal for all prompts (the default).
  • 1: Use a GUI dialog for password prompts and the PAUSE metacommand.
  • 2: Additionally, use a GUI dialog for any message to be displayed with the HALT metacommand, and use a GUI dialog to prompt for the initial database to use if no other specifications are provided.
  • 3: Additionally, open a GUI console when execsql starts.
The prompt for a database password, and the prompt produced by the PAUSE metacommand, are both displayed on the terminal by default. When the "-v1" option is used, or the GUI console is open, both of these prompts will appear in GUI dialogs instead. If the "-v2" option is specified, then the HALT metacommand, if used with a message, will also be displayed in a GUI dialog. In addition, if the "-v2" or "-v3" option is used, and no server name or database name are specified either in a configuration file or on the command line, then execsql will use a GUI dialog to prompt for this information when it starts up.
-w
Ordinarily if a user name is specified (with the "-u" option), execsql will prompt for a password for that user. When this option is used, execsql will not prompt for entry of a password.

Requirements

The execsql program uses third-party Python libraries to communicate with different database and spreadsheet software. These libraries must be installed to use those programs with execsql. Only those libraries that are needed, based on the command line arguments and metacommands, must be installed. The libraries required for each database or spreadsheet application are:

Connections to SQLite databases are made using Python's standard library, so no additional software is needed.

To use the Jinja or Airspeed template processors with the EXPORT metacommand, those software libraries must be installed also.

Configuration Files

In addition to, or as an alternative to, command-line options and arguments, configuration files can be used to specify most of the same information, plus some additional information. Most of the command-line options and arguments can be specified in a configuration file, with the exception of the script name. The script name must always be specified on the command line.

execsql will read information from up to three configuration files in different locations, if they are present. The three locations are:

The name of the configuration file, in all locations, is execsql.conf.

Configuration data is read from these files in the order listed above. Information in later files may augment or replace information in earlier files. Options and arguments specified on the command line will further augment or override information specified in the configuration files.

Configuration files use the INI file format. Section names are case sensitive and must be all in lowercase. Property names are not case sensitive. Property values are read as-is and may or may not be case sensitive, depending on their use. Comments can be included in configuration files; each comment line must start with the "#" character.

The section and property names that may be used in a configuration file are listed below.

Section connect

db_type
The type of database. This is equivalent to the "-t" command-line option, and the same list of single-character codes are the only valid property values.
server
The database server name. This is equivalent to the second command-line argument for client-server databases.
db
The database name. This is equivalent to the third command-line argument for client-server databases
db_file
The name of the database file. This is equivalent to the second command-line argument for file-based databases.
port
The port number for the client-server database. This is equivalent to the "-p" command-line option.
username
The name of the database user, for client-server databases. This is equivalent to the "-u" command-line option.
access_username
The name of the database user, for MS-Access databases only. When using MS-Access, a password will be prompted for only if this configuration option is set or the "-u" command-line option is used, regardless of the setting of the username configuration parameter.
password_prompt
Indicates whether or not execsql should prompt for the user's password. The property value should be either "Yes" or "No". This is equivalent to the "-w" command-line option.
new_db
Indicates whether or not execsql should create a new PostgreSQL or SQLite database to connect to.

Section encoding

database
The database encoding to use. This is equivalent to the "-e" command-line option.
script
The script encoding to use. This is equivalent to the "-f" command-line option.
import
Character encoding for data imported with the IMPORT metacommand. This is equivalent to the "-i" command-line option.
output
Character encoding for data exported with the EXPORT metacommand. This is equivalent to the "-h" command-line option.

Section input

boolean_int
Whether or not to consider integer values of 0 and 1 as Booleans when scanning data during import or copying. The property value should be either "Yes" or "No". The default value is "Yes". By default, if a data column contains only values of 0 and 1, it will be considered to have a Boolean data type. By setting this value to "No", such a column will be considered to have an integer data type. This is equivalent to the "-b" command-line option.
boolean_words
Whether or not to recognize only full words as Booleans. If this value is "No" (the default), then values of "Y", "N", "T", and "F" will be recognized as Booleans. If this value is "Yes", then only "Yes", "No", "True", and "False" will be recognized as Booleans. This setting is independent of the boolean_int setting.
max_int
Establishes the maximum value that will be assigned an integer data type when the IMPORT or COPY metacommands create a new data table. Any column with integer values less than or equal to this value (max_int) and greater than or equal to -1 × max_int - 1 will be considered to have an 'integer' type. Any column with values outside this range will be considered to have a 'bigint' type. The default value for max_int is 2147483647. The max_int value can also be altered within a script using the MAX_INT metacommand.
empty_strings
Determines whether empty strings in the input are preserved or, alternatively, will be replaced by NULL. The property value should be either "Yes" or "No". The default, "Yes", indicates that empty strings are allowed. A value of "No" will cause all empty strings to be replaced by NULL. There is no command-line option corresponding to this configuration parameter, but the metacommand EMPTY_STRINGS can also be used to change this configuration item.
scan_lines
The number of lines of a data file to scan to determine the quoting character and delimiter character used. This is equivalent to the "-s" command-line option.
import_buffer
The size of the import buffer, in kilobytes, to use with the IMPORT metacommand. This is equivalent to the "-z" command-line option.

Section output

log_write_messages
Specifies whether output of the WRITE metacommand will also be written to execsql's log file. The property value should be either "Yes" or "No". This configuration property can also be controlled within a script with the LOG_WRITE_MESSAGES metacommand.
make_export_dirs
The output directories used in an EXPORT metacommand will be automatically created if they do not exist (and the user has permission). The property value should be either "Yes" or "No". This is equivalent to the "-d" command-line option.
css_file
The URI of a CSS file to be included in the header of an HTML file created with the EXPORT metacommand. If this is specified, it will replace the CSS styles that execsql would otherwise use.
css_style
A set of CSS style specifications to be included in the header of an HTML file created with the EXPORT metacommand. If this is specified, it will replace the CSS styles that execsql would otherwise use. Both css_file and css_style may be specified; if they are, they will be included in the header of the HTML file in that order.
template_processor
The name of the template processor that will be used with the EXPORT and EXPORT QUERY metacommands. The only valid values for this property are "jinja" and "airspeed". If this property is not specified, the default template processor will be used.

Section interface

console_wait_when_done
Controls the persistence of any console window at the completion of the script. If the property value is set to "Yes" (the default value is "No"), the console window will remain open until explicitly closed by the user. The message "Script complete; close the console window to exit execsql." will be displayed in the status bar. This setting has the same effect as a CONSOLE WAIT metacommand.
gui_level
The level of interaction with the user that should be carried out using GUI dialogs. The property value must be 0, 1, 2, or 3. The meanings of these values are:
  • 0: Do not use any optional GUI dialogs.
  • 1: Use GUI dialogs for password prompts and for the PAUSE metacommand.
  • 2: Also use a GUI dialog if a message is included with the HALT metacommand, and prompt for the initial database to use if no database connection parameters are specified in a configuration file or on the command line.
  • 3: Additionally, open a GUI console when execsql starts.

Section email

host
The SMTP host name to be used to transmit email messages sent using the EMAIL metacommand. A host name must be specified to use the EMAIL metacommand.
port
The port number of the SMTP host to use. If this is omitted, port 25 will be used unless either the "use_ssl" or "use_tls" configuration properties is also specified, in which case ports 465 or 587 may be used.
username
The name of the user if the SMTP server requires login authentication.
password
An unencrypted password to be used if the SMTP server requires login authentication.
enc_password
An encrypted password to be used if the SMTP server required login authentication. The encrypted version of a password should be as is produced by the SUB_ENCRYPT metacommand. A suitably encrypted version of a password can be produced by running the script
-- !x! prompt enter_sub pw password message "Enter a password to encrypt" -- !x! sub_encrypt enc_pw !!pw!! -- !x! write "The encrypted password is: !!enc_pw!!"
If both the "password" and "enc_password" configuration properties are used, the "enc_password" property will take precedence and will be used for SMTP authentication.
use_ssl
SSL/TLS encryption will be used from the initiation of the connection.
use_tls
SSL/TLS encryption will be used after the initial connection is made using unencrypted text.
email_format
Specifies whether the message will be sent as plain text or as HTML email. The only valid values for this property are "plain" and "html". If not specified, emails will be sent in plain text.
message_css
A set of CSS rules to be applied to HTML email.

Section config

config_file
The full name or path to an additional configuration file to be read. If only a path is specified, the name of the configuration file should be execsql.conf. The configuration file specified will be read immediately following the configuration file in which it is named. No configuration file will be read more than once.

Section variables

There are no fixed properties for this section. All property names and their values that are specified in this section will be used to define substitution variables, just as if a series of SUB metacommands had been used at the beginning of the script.

Usage Notes

  1. If the program is run without any arguments it will print a help message on the terminal, similar to the usage description above.
  2. Script files can contain single-line comments, which are identified by two dashes ("--") at the start of a line. Script files can also contain multi-line comments, which begin on a line where the first characters are "/*" and end on a line where the last characters are "*/".
  3. execsql recognizes a SQL statement as consisting of a sequence of non-comment lines that ends with a line ending with a semicolon. A backslash ("\") at the end of a line is treated as a line continuation character. Backslashes do not need to be used for simple SQL statements, but must be used for procedure and function definitions, where there are semicolons within the body of the definition, and a semicolon appears at the end of lines for readability purposes. Backslashes may not be used as continuation characters for metacommands.
  4. Comments and SQL statements should not be mixed on a single line. A mixture of comments and SQL statements like this:
    select scramble(eggs) -- Use custom aggregate function from refrigerator natural join stove;
    will result in an unpalatable hash.
  5. With the exception of the "CREATE TEMPORARY QUERY..." statement when used with MS-Access, the execsql program does not parse or interpret SQL syntax in any way.
  6. SQL syntax used in the script must conform to that recognized by the DBMS engine in use (e.g., scripts for Access must use SQL compatible with the Jet database engine). Because execsql can connect to several different DBMSs simultaneously, a single script can contain a mixture of different SQL syntaxes. To minimize this variation (and possible mistakes that could result), execsql metacommands provide some common features of DBMS-specific scripting languages (e.g., pgScript and T-SQL), and execsql turns on ANSI-compatible mode for SQL Server and MySQL when it connects to those databases.
  7. Metacommands can be embedded in SQL comments to export data and carry out other actions during the course of the script. These metacommands are identified by the token "!x!" immediately following the SQL comment characters at the beginning of a line, i.e.:
    -- !x! <metacommand>
    The special commands that are available are described in the Metacommands section.
  8. SQL statements are ordinarily automatically committed by execsql. Consequently, database transactions will not work as expected under default conditions. The AUTOCOMMIT and BATCH metacommands provide two different ways to alter execsql's default autocommit behavior. Transactions will work as expected either within a batch or after autocommit has been turned off. One difference between these two approaches is that within transactions inside a batch, changes to data tables are not visible to metacommands such as PROMPT DISPLAY, whereas these data are visible within transactions that follow an AUTOCOMMIT OFF metacommand. Both methods can be used to implement cross-database transactions, but the difference in data visibility affects what tests can be done to decide whether to commit or roll back a transaction.
  9. If execsql finishes normally, without errors and without being halted either by script conditions or the user, the system exit status will be set to 0 (zero). If an error occurs that causes the script to halt, the exit status will be set to 1. If the user cancels script processing in response to any prompt, the exit status will be set to 2. If the script is halted with either the HALT or HALT DISPLAY metacommands, the system exit status will be set to 2 unless an alternate value is specified as part of the metacommand.
  10. Scripts for Microsoft Access that use temporary queries will result in those queries being created in the Access database, and then removed, every time the scripts are run. This will lead to a gradual increase in the size of the Access database file. If the script halts unexpectedly because of an error, the temporary queries will remain in the Access database. This may assist in debugging the error, but if the temporary queries are not created conditional on their non-existence, you may have to remove them manually before re-running the script.
  11. The user name for password-protected Access databases is "Admin" by default (i.e., if no other user name was explicitly specified when the password was applied). To ensure that execsql prompts for a password for password-protected Access databases, a user name must be specified either on the command line with the "-u" option or in a configuration file with the access_username configuration item. When the user name in Access is "Admin", any user name can be provided to execsql.
  12. With Access databases, an ODBC connection is used for SELECT queries, to allow errors to be caught, and a DAO connection to the Jet engine is used when saved action queries (UPDATE, INSERT, DELETE) are created or modified. Because the Jet engine only flushes its buffers every five seconds, execsql will ensure that at least five seconds have passed between the last use of DAO and the execution of a SELECT statement via ODBC.
  13. The syntax of the "CREATE TEMPORARY QUERY" DDL supported by execsql when used with an MS-Access database is:
    CREATE [TEMP[ORARY]] QUERY|VIEW <query_name> AS <sql_command>
    The "TEMPORARY" specification is optional: if it is included, the query will be deleted after the entire script has been executed, and if it is not, the query will remain defined in the database after the script completes. If a query of the same name is already defined in the Access database when the script runs, the existing query will be deleted before the new one is created—no check is performed to determine whether the new and old queries have the same definition, and no warning is issued by execsql that a query definition has been replaced. The keyword "VIEW" can be used in place of the keyword "QUERY". This alternative provides compatibility with the "CREATE TEMPORARY VIEW" command in PostgreSQL, and minimizes the need to edit any scripts that are intended to be run against both Access and PostgreSQL databases.
  14. Boolean (Yes/No) columns in Access databases cannot contain NULL values. If you IMPORT boolean data into a column having Access' boolean data type, any NULL values in the input data will be converted to False boolean values. This is a potentially serious data integrity issue. To help avoid this, when the NEW or REPLACEMENT keywords are used with the IMPORT or COPY metacommands, and execsql determines that the input file contains boolean data, execsql will create that column in Access with an integer data type rather than a boolean data type, and when adding data will convert non-integer True values to 1, and False values to 0.
  15. When a DSN is used as a data source, execsql has no information about the features or SQL syntax used by the underlying DBMS. In the expectation that a DSN connection will most commonly be used for Access databases under 64-bit Windows, a DSN connection will use Access' syntax when issuing a CREATE TABLE statement in response to a COPY or IMPORT metacommand. However, a DSN connection does not (and cannot) use DAO to manage queries in a target Access database, so all data manipulations must be carried out using SQL statements. The EXECUTE metacommand uses the same approach for DSN connections as is used for SQL Server.

SQL Syntax Notes

MS-Access Quirks

The version of SQL that is used by the Jet engine when accessed via DAO or ODBC, and thus that must be used in the script files executed with execsql, is generally equivalent to that used within Access itself, but is not identical, and is also not the same in all respects as standard SQL. There are also differences in the SQL syntax accepted by the DAO and ODBC interfaces. To help avoid inconsistencies and errors, here are a few points to keep in mind when creating SQL scripts for use with Access:

Implicit DROP TABLE Statements

The "REPLACEMENT" keyword for the IMPORT and COPY metacommands allows a previously existing table to be replaced. To accomplish this, execsql issues a "DROP TABLE" statement to the database in use. PostgreSQL, SQLite, MySQL, and MariaDB support a form of the "DROP TABLE" statement that automatically removes all foreign keys to the named table. execsql uses these forms of the "DROP TABLE" statement for these DBMSs, and therefore use of the "REPLACEMENT" keyword always succeeds at removing the named table before trying to create a new table with the same name. SQL Server, MS-Access, and Firebird do not have a form of the "DROP TABLE" statement that automatically removes foreign keys. Therefore, if the "REPLACEMENT" keyword is used with any of these three DBMSs, for a table that has foreign keys into it, that table wil not be dropped, and an error will subsequently occur when execsql issues a "CREATE TABLE" statement to create a new table of the same name. To avoid this, when using any of these three DBMSs, you should include in the script the appropriate SQL commands to remove foreign keys (and possibly even to remove the table) before using the IMPORT or COPY metacommands.

Implicit Commits

By default, execsql immediately commits all SQL statements. The AUTOCOMMIT metacommand can be used to turn off automatic commits, and the BATCH metacommand can be used to delay commits until the end of a batch. IMPORT and COPY are the only metacommands that change data, and they also automatically commit their changes when complete (unless AUTOCOMMIT has been turned off). If a new table is created with either of these metacommands (through the use of the NEW or REPLACEMENT keywords), the CREATE TABLE statement will not be committed separately from the data addition, except when using Firebird. Thus, if an error occurs during addition of the data, the new target table will not exist—except when using Firebird.

When adding a very large amount of data with the IMPORT or COPY metacommands, internal transaction limits may be exceeded for some DBMSs. For example, MS-Access may produce a 'file sharing lock count exceeded' error when large data sets are loaded.

Boolean Data Types

Not all DBMSs have explicit support for a boolean data type. When execsql creates a new table as a result of the NEW or REPLACEMENT keyword in IMPORT and COPY metacommands, it uses the following data type for boolean values in each DBMS:

If boolean values are imported to some other data type in an existing table, the conversion to that data type may or may not be successful.

When scanning input data to determine data types, execsql will consider a column to contain boolean values if it contains only values of 0, 1, '0', '1', 'true', 'false', 't', 'f', 'yes', 'no', 'y', or 'n'. Character matching is case-insensitive.

Schemas, the IMPORT and COPY Metacommands, and Schema-less DBMSs

If a schema name is used with the table specifications for the IMPORT or COPY metacommands, when the command is run against either MS-Access or SQLite, the schema name will be ignored. No error or warning message will be issued. Such irrelevant schema specifications are ignored to reduce the need to customize metacommands for use with different DBMSs.

ANSI Compatibility

When execsql connects to a SQL Server or MySQL database, it automatically configures the DBMS to expect ANSI-compatible SQL, to allow the use of more standards-compliant, and thus consistent, SQL. In particular, for MySQL, note that the double-quote character, rather than the backtick character, must be used to quote table, schema, and column names, and only the apostrophe can be used to quote character data.

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.
$COUNTER_x
An integer value that is automatically incremented every time the counter variable is referenced. 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. 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_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.
$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".
$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.
$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.

Environment 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_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.

Metacommands

The execsql program supports several special commands that allow the following actions to be taken at certain points within the script file:

Metacommands recognized by execsql are embedded in SQL comments, and are identified by the token "!x!" immediately following the comment characters at the beginning of the line. Each metacommand must be completely on a single line. Metacommand usage is illustrated in several of the examples.

Metacommands can appear anywhere in a SQL script except embedded inside a SQL statement. This restriction prohibits constructions such as:

select * from d_labresult where lab = '!!selected_lab!!' -- !x! if(sub_defined(selected_sdg)) and sdg = '!!selected_sdg!!' -- !x! endif ;

This will not work because metacommands are not executed at the time that SQL statements are read from the script file, but are run after the script has been parsed into separate SQL statements and metacommands. Instead, SQL statements can be dynamically constructed using substitution variables to modify them at runtime, like this:

-- !x! sub whereclause lab = '!!selected_lab!!' -- !x! if(sub_defined(selected_sdg)) -- !x! sub whereclause !!whereclause!! and sdg = '!!selected_sdg!!' -- !x! endif select * from d_labresult where !!whereclause!!;

The metacommands are described in the following sections. Metacommand names are shown here in all uppercase, but execsql is not case-sensitive when evaluating the metacommands.

AUTOCOMMIT

AUTOCOMMIT ON|OFF

By default, execsql automatically commits each SQL statement individually. Setting AUTOCOMMIT off will change this behavior. The user is then responsible for explicitly issuing a "COMMIT;" statement to the database to ensure that all preceding SQL statements are executed.

Unlike BATCH metacommands, the SQL statements issued while AUTOCOMMIT is off will not be queued up and automatically run when AUTOCOMMIT is turned back on again. However, any SQL statements that are run after AUTOCOMMIT is turned back on will be automatically committed, and that commit operation will also commit any SQL statements that were issued while AUTOCOMMIT was off, unless a rollback statement was used as the last SQL statement while AUTOCOMMIT was off.

The AUTOCOMMIT metacommand is database-specific, and affects only the database in use when the metacommand is used. This contrasts with the BATCH metacommand, which affects all databases.

The IMPORT and COPY metacommands do not commit data changes while AUTOCOMMIT is off. The SQL statements generated by the IMPORT and COPY metacommands are sent to the database, however. Therefore the AUTOCOMMIT metacommand is recommended when explicit transaction control is to be applied to the IMPORT and COPY metacommands.

BEGIN BATCH and END BATCH

BEGIN BATCH
END BATCH
ROLLBACK [BATCH]

The BATCH commands provide a sort of transaction control at the script level, as an alternative to using the DBMS's own transaction commands. execsql ordinarily executes and commits SQL statements immediately (i.e., as if the database connection is set to autocommit, although execsql actually manages commit and rollback statements directly). The BATCH commands allow you to alter this behavior so that SQL statements are not executed and committed until a batch is completed. This allows execsql to emulate environments that operate in batch mode by default (specifically, sqlcmd). In addition, batches operate across databases: SQL statements directed to several different databases will all be held until the end of the batch, and will then be executed at the end of the batch—or discarded if the batch is rolled back.

BEGIN BATCH marks the beginning of a set of SQL statements to be executed in a single operation. END BATCH marks the end of that set of statements. ROLLBACK BATCH discards any SQL statements that have already been included in the batch, but does not terminate the batch.

The SQL statements within a batch are queued up and sent to the database only at the end of the batch. If SQL statements for several different databases are included within the batch, they will be executed at the end of the batch in the order in which they were specified.

Metacommands are executed regardless of whether or not they appear within a batch. Specifically, the IMPORT and COPY metacommands will send their data to the database immediately. Therefore, if these metacommands are run within a batch, then regardless of the metacommands' position within the batch, the resulting data changes will be made before any SQL statements within the batch are run. Because of the potential for unexpected effects when IMPORT or COPY metacommands are embedded within a batch, this construction is probably best avoided.

Alternatives to using batches to control the execution time of SQL statements are:

The END BATCH metacommand is equivalent to the "GO" command of SQL Server utilities such as sqlcmd. There is no explicit equivalent to BEGIN BATCH in sqlcmd or other SQL Server utilities. In sqlcmd a new batch is automatically begun at the beginning of the script or immediately after a GO statement. execsqsl only starts a new batch when a BEGIN BATCH statement is encountered.

If the end of the script file is encountered while a batch of statements is being compiled, but there is no END BATCH metacommand, the SQL statements in that incomplete batch will not be executed.

BEGIN SCRIPT and END SCRIPT

BEGIN SCRIPT <script_name>
END SCRIPT

The BEGIN SCRIPT and END SCRIPT metacommands define a block of statements (SQL statements and metacommands) that can be subsequently executed (repeatedly, if desired) using the EXECUTE SCRIPT metacommand.

The statements within the BEGIN/END SCRIPT block are not executed within the normal flow of the script in which they appear, and, unlike the BEGIN/END BATCH commands, neither are they executed when the END SCRIPT metacommand is encountered. These statements are executed only when the corresponding script is named in an EXECUTE SCRIPT metacommand.

A BEGIN/END SCRIPT block can be used in ways similar to a separate script file that is included with the INCLUDE metacommand. Both allow the same code to be executed repeatedy, either at different locations in the main script or recursively to perform looping.

The BEGIN SCRIPT and END SCRIPT metacommands are executed when a script file is read, not while the the script is being executed. As a consequence:

However, the BEGIN SCRIPT and END SCRIPT metacommands can be used in a separate script file that is INCLUDEd in the main script. In this case, both of the previous restrictions are eliminated. In addition the EXECUTE SCRIPT metacommand can be included in a conditional statement.

BOOLEAN_INT

BOOLEAN_INT YES|NO

Controls whether integer values of 0 and 1 are considered to be Booleans when the IMPORT and COPY metacommands scan data to determine data types to create a new table (i.e, when either the NEW or REPLACEMENT keyword is used with the IMPORT and COPY metacommands.) The argument should be either "Yes" or "No". execsql's default behavior is to consider a column with only integer values of 0 and 1 to have a Boolean data type. By setting this value to "No", such a column will be considered to have an integer data type. This is equivalent to the "-b" command-line option and the "boolean_int" configuration parameter.

BOOLEAN_WORDS

BOOLEAN_WORDS YES|NO

Controls whether execsql will recognize only full words as Booleans when the IMPORT and COPY metacommands scan data to determine data types to create a new table (i.e, when either the NEW or REPLACEMENT keyword is used with the IMPORT and COPY metacommands.). The argument should be either "Yes" or "No". execsql's default behavior is to recognize values of "Y", "N", "T", and "F" as Booleans. By setting BOOLEAN_WORDS to "Yes", then only "Yes", "No", "True", and "False" will be recognized as Booleans.

CANCEL_HALT

CANCEL_HALT ON|OFF

When CANCEL_HALT is set to ON, which is the default, if the user presses the "Cancel" button on a dialog (such as is presented by the PROMPT DISPLAY metacommand), execsql will halt script processing. If CANCEL_HALT is set to OFF, then execsql will not halt script processing, and it is the script author's responsibility to ensure that adverse consequences do not result from the lack of a response to the dialog. Example 10 illustrates a condition in which setting CANCEL_HALT to OFF is appropriate.

CONNECT

For PostgreSQL:

CONNECT TO POSTGRESQL(SERVER=<server_name>, DB=<database_name> [, USER=<user>, NEED_PWD=TRUE|FALSE] [, PORT=<port_number>] [, ENCODING=<encoding>] [, NEW]) AS <alias_name>

For SQLite:

CONNECT TO SQLITE(FILE=<database_file> [, NEW]) AS <alias_name>

For MS-Access:

CONNECT TO ACCESS(FILE=<database_file> [, NEED_PWD=TRUE|FALSE] [, PASSWORD=<password>] [, ENCODING=<encoding>]) AS <alias_name>

For SQL Server:

CONNECT TO SQLSERVER(SERVER=<server_name>, DB=<database_name> [, USER=<user>, NEED_PWD=TRUE|FALSE] [, PORT=<port_number>] [, ENCODING=<encoding>]) AS <alias_name>

For MySQL

CONNECT TO MYSQL(SERVER=<server_name>, DB=<database_name> [, USER=<user>, NEED_PWD=TRUE|FALSE] [, PORT=<port_number>] [, ENCODING=<encoding>]) AS <alias_name>

For MariaDB

CONNECT TO MARIADB(SERVER=<server_name>, DB=<database_name> [, USER=<user>, NEED_PWD=TRUE|FALSE] [, PORT=<port_number>] [, ENCODING=<encoding>]) AS <alias_name>

For Firebird:

CONNECT TO FIREBIRD(SERVER=<server_name>, DB=<database_name> [, USER=<user>, NEED_PWD=TRUE|FALSE] [, PORT=<port_number>] [, ENCODING=<encoding>]) AS <alias_name>

For a DSN:

CONNECT TO DSN(DSN=<DSN_name>, [, USER=<user>, NEED_PWD=TRUE|FALSE] [, PASSWORD=<password>] [, ENCODING=<encoding>]) AS <alias_name>

Establishes a connection to another database. The keyword values are equivalent to arguments and options that can be specified on the command line when execsql is run. The "NEW" keyword, used with PostgreSQL and SQLite, will cause a new database of the given name to be created. There must be no existing database of that name, and (for Postgres) you must have permissions assigned that allow you to create databases.

The CONNECT metacommands for Access and DSN connections are the only ones that allows a password to be specified. If a password is needed for any other database, execsql will display a prompt for the password. An exception has been made for Access because of an actual use case where data had to be extracted from over 11,000 Access files, all with the same password. Rather than embedding the password directly into the SQL script, the PROMPT ENTER_SUB can be used to prompt for the password just once, and the PASSWORD clause of the CONNECT metacommand can then reference the substitution variable that is created by the PROMPT ENTER_SUB metacommand.

The alias name that is specified in this command can be used to refer to this database in the USE and COPY metacommands. Alias names can consist only of letters, digits, and underscores, and must start with a letter. The alias name "initial" is reserved for the database that is used when execsql starts script processing, and cannot be used with the CONNECT metacommand. If you re-use an alias name, the connection to the database to which that name was previously assigned will be closed, and the database will no longer be available. Using the same alias for two different databases allows for mistakes wherein script statements are run on the wrong database, and so is not recommended.

CONSOLE

CONSOLE ON|OFF

Creates (ON) or destroys (OFF) a GUI console to which subsequent WRITE metacommands will send their output. Data tables exported as text will also be written to this console. The console window includes a status line and progress bar indicator that can each be directly controlled by metacommands listed below.

Only one console window can be open at a time. If a "CONSOLE ON" metacommand is used while a console is already visible, the same console will remain open, and no error will be reported.

A GUI console can be automatically opened when execsql is started by using the "-v3" option.

When the GUI console is turned OFF, subsequent output will again be directed to standard output (the terminal window, if you have one open).

If an error occurs while the console is open, the error message will be written on standard error (typically the terminal) rather than in the console, and the console will be closed as execsql terminates.

CONSOLE HIDE|SHOW

Hides or shows the console window. Text will still be written to the console window while it is hidden, and will be visible if the console is shown again.

CONSOLE STATUS "<message>"

The specified message is written to the status bar at the bottom of the console window. Use an empty message ("") to clear the status message.

CONSOLE PROGRESS <number> [/ <total>]

The progress bar at the bottom of the console window will be updated to show the specified value. Values should be numeric, between zero and 100. If the number is followed by a slash (or virgule) and then another number, the two numbers will be taken as a fraction and converted to a percentage for display. Use a value of zero to clear the progress bar.

CONSOLE SAVE [APPEND] TO <filename>

Saves the text in the console window to the specified file. If the "APPEND TO" keyword is used, the console text will be appended to any existing file of the same name; otherwise, any existing file will be overwritten.

CONSOLE WAIT ["<message>"]

Script processing will be halted until the user responds to the console window with either the <Enter> key or the <Esc> key, or clicks on the window close button. If an (optional) message is included as part of the command, the message will be written into the status bar. If the user responds with the <Enter> key, the console window will remain open and script processing will resume. The user can close the console window either with the <Esc> key or by clicking on the window close button.

The console window has a single menu item, 'Save as...', that allows the entire console output to be saved as a text file.

COPY

COPY <table1_or_view> FROM <alias_name_1> TO [NEW|REPLACEMENT] <table2> IN <alias_name_2>

Copies the data from a data table or view in one database to a data table in a second database. The two databases between which data are copied are identified by the alias names that are established with the CONNECT metacommand. The alias "initial" can be used to refer to the database that is used when execsql starts script processing. Neither the source nor the destination database need be the initial database, or the database currently in use.

The second (destination) table must have column names that are identical to the names of the columns in the first (source) table. The second table may have additional columns; if it does, they will not be affected and their names don't matter. The data types in the columns to be copied must be compatible, though not necessarily identical. The order of the columns in the two tables does not have to be identical.

If the NEW keyword is used, the destination table will be automatically created with column names and data types that are compatible with the first (source) table. The data types used for the columns in the newly created table will be determined by a scan of all of the data in the first table, but may not exactly match those in the first table. If the destination table already exists when the NEW keyword is used, an error will occur.

If the REPLACEMENT keyword is used, the destination table will also be created to be compatible with the source table, but any existing destination table of the same name will be dropped first. execsql uses a "drop table" statement to drop an existing destination table, and this statement may not succeed if there are dependencies on that table (see the section on implicit drop table statements). If the destination table is not dropped, then data from the source table will be added to the existing table, or an error will occur if the table formats are not compatible.

If there are constraints on the second table that are not met by the data being added, an error will occur. If an error occurs at any point during the data copying process, no new data will be added to the second table.

COPY QUERY

COPY QUERY <<query>> FROM <alias_name_1> TO [NEW|REPLACEMENT] <table> IN <alias_name_2>

Copies data from one database to another in the same manner as the COPY metacommand, except instead of specifying the source table (or view), a SQL query statement is used instead. The SQL statement must be terminated with a semicolon and enclosed in double angle brackets.

Like all metacommands, this metacommand must appear on a single line, although the SQL statement may be quite long. To facilitate readability, the SQL statement may be saved in a substitution variable and that substitution variable referenced in the COPY QUERY metacommand.

EMAIL

EMAIL FROM <from_address> TO <to_addresses> SUBJECT "<subject>" MESSAGE "<message_text>" [MESSAGE_FILE "<filename>"] [ATTACH_FILE "<attachment_filename>"]

Sends an email. The from_address should be a valid email address (though not necessarily a real one). The to_addresses should also be a valid email address, or a comma- or semicolon-delimited list of email addresses. If none of the destination email addresses are valid, an exception will occur and execsql will halt. If at least one of the email addresses is valid, the command will succeed.

The subject and the message_text should both be enclosed in double quotes and should not contain a double quote.

If the MESSAGE_FILE keyword is used, the contents of that file will be inserted into the body of the email message in addition to whatever message_text is specified. The filename may be unquoted, but must be quoted if it contains any space characters.

If the ATTACH_FILE keyword is used, the specified file will be attached to the email message. The attachment_filename may be unquoted, but must be quoted if it contains any space characters.

The SMTP host and any other connection information that is necessary must be specified in the "email" section of a configuration file.

EMPTY_STRINGS

EMPTY_STRINGS YES|NO

Determines whether empty strings are allowed in data that is saved using either the IMPORT or COPY metacommands. The default is to allow empty strings. A metacommand of EMPTY_STRINGS NO will cause all empty strings to be replaced by NULL.

ERROR_HALT

ERROR_HALT ON|OFF

When ERROR_HALT is set to ON, which is the default, any errors that occur as a result of executing a SQL statement will cause an error message to be displayed immediately, and execsql will exit. When ERROR_HALT is set to OFF, then SQL errors will be ignored, but can be evaluated with the IF SQL_ERROR conditional.

EXECUTE

EXECUTE <procedure_name>

Executes the specified stored procedure (or function, or query, depending on the DBMS). Conceptually, the EXECUTE metacommand is intended to be used to execute stored procedures that do not require arguments and do not return any values. The actual operation of this command differs depending on the DBMS that is in use.

Access has only stored queries, which may be equivalent to either a view or a stored procedure in other DBMSs. When using Access, the query referenced in this command should be an INSERT, UPDATE, or DELETE statement—executing a SELECT statement in this context would have no purpose.

Postgres has stored functions. Functions with no return value are equivalent to stored procedures. When using Postgres, execsql treats the argument as the name of a stored function. It appends an empty pair of parentheses to the function name before calling it, so you should not include the parentheses yourself; the reason for this is to maintain as much compatibility as possible in the metacommand syntax across DBMSs.

SQL Server has stored procedures. When using SQL Server, execsqsl treats the argument as the name of a stored procedure.

SQLite does not support stored procedures or functions, and (unlike Access queries), views can only represent SELECT statements. When using SQLite, execsql cannot treat the argument as a stored procedure or function, so it treats it as a view and carries out a SELECT * FROM <procedure_name>; statement. This is unlikely to be very useful in practice, but it is the only reasonable action to take with SQLite.

MySQL and MariaDB support stored procedures and user-defined functions. User-defined functions can be invoked within SQL statements, so execsql considers the argument to the EXECUTE metacommand to be the name of a stored procedure, and calls it after appending a pair of parentheses to represent an empty argument list.

Firebird supports stored procedures, and execsql executes the procedure with the given name, providing neither input parameters nor output parameters.

EXECUTE SCRIPT

EXECUTE SCRIPT <script_name>

This metacommand will execute the set of SQL statements and metacommands that was previously defined and named using the BEGIN/END SCRIPT metacommands.

EXPORT

EXPORT <view_or_table> [TEE] [APPEND] TO <filename>|stdout AS <format> [DESCRIPTION "<description>"]
EXPORT <view_or_table> [TEE] [APPEND] TO <filename>|stdout WITH TEMPLATE <template_file>

Exports data to a file. The data set named in this command must be an existing table or view. The output filename specified will be overwritten if it exists unless the APPEND keyword is included. If the output name is given as "stdout", the data will be sent to the console instead of to a file. If specified by the "-d" command-line option or the "make_export_dirs" configuration option, execsql will automatically create the output directories if needed.

If the "TEE" keyword is used, the data will be exported to the terminal in the "TXT" format (as described below) in addition to whatever other type of output is produced.

The EXPORT metacommand has two forms, as shown above. The first of these will export the data in a variety of established formats, and the second of which will use one of several different template processors with a template specification file. The first form is more convenient if any of the supported formats is suitable, and the latter form allows more flexible customization of the output.

Exporting Data to Specific Supported Formats

The format specification in the first form of the EXPORT metacommand controls how the data table is written. The allowable format specifications and their meanings are:

CSV
Comma-delimited with double quotes around text that contains a comma or a double quote. Column headers will not be written if the APPEND keyword is used. No description text will be included in the output even if it is provided.
HTML
Hypertext markup language. If the APPEND keyword is not used, a complete web page will be written, with meta tags in the header to identify the source of the data, author, and creation date; simple CSS will be defined in the header to format the table. If the APPEND keyword is used, only the table will be written to the output file. If the APPEND keyword is used and the output file contains a </body> tag, the table will be written before that tag rather than at the physical end of the file. The HTML tags used to create the table have no IDs, classes, styles, or other attributes applied. Custom CSS can be specified in configuration files. If the DESCRIPTION keyword is used, the given description will be used as the table's caption.
JSON
Javascript Object Notation. The data table is represented as an array of JSON objects, where each object represents a row of the table. Each row is represented as a set of key:value pairs, with column names used as the keys. No description text will be included in the output even if it is provided.
LATEX
Input for the LaTeΧ typesetting system. If the APPEND keyword is not used, a complete document (of class article) will be written. If the APPEND keyword is used, only the table definition will be written to the output file. If the APPEND keyword is used and an existing output file contains an \end{document} directive, the table will be written before that directive rather than at the physical end of the file. Wide or long tables may exceed LaTeΧ's default page size. If the DESCRIPTION keyword is used, the given description will be used as the table's caption.
ODS
OpenDocument spreadsheet. When the APPEND keyword is used, each data set that is exported will be on a separate worksheet. The name of the view or table exported will be used as the worksheet name. If this conflicts with a sheet already in the workbook, a number will be appended to make the sheet name unique. (If a workbook with sheet names longer than 31 characters is opened in Excel, the sheet names will be truncated.) A sheet named "Datasheets" will also be created, or updated if it already exists, with information to identify the author, creation date, description, and data source for each data sheet in the workbook.
PLAIN
Text with no header row, no quoting, and columns delimited by a single space. This format is appropriate when you want to export text—see Example 11 for an illustration of its use. No description text will be included in the output even if it is provided.
RAW
Data exactly as stored with no headers, quotes, or delimiters between either columns or rows. This format is most suitable for export of binary data. No description text will be included in the output even if it is provided.
TAB or TSV
Tab-delimited with no quoting. Column headers will not be written if the APPEND keyword is used. No description text will be included in the output even if it is provided.
TABQ or TSVQ
Tab-delimited with double quotes around any text that contains a tab or a double quote. Column headers will not be written if the APPEND keyword is used. No description text will be included in the output even if it is provided.
TXT
Text with data delimited and padded with spaces so that values are aligned in columns. Column headers are underlined with a row of dashes. Columns are separated with the pipe character (|). Column headers are always written, even when the APPEND keyword is used. This output is compatible with Markdown pipe tables—see Example 8. If the DESCRIPTION keyword is used, the given description will be written as plain text on the line before the table.
TXT-ND
This is the same as the TXT format, except that table cells where data are missing are filled with "ND" instead of being blank. Some tables with blank cells are not parsed correctly by pandoc, and this format ensures that no cells are blank. If the DESCRIPTION keyword is used, the given description will be written as plain text on the line before the table.
US
Text with the unit separator as the column delimiter, and no quoting. Column headers will not be written if the APPEND keyword is used. No description text will be included in the output even if it is provided.
VALUES
Data are written into the output file in the format of a SQL INSERT...VALUES statement. The name of the target table is specified in the form of a substitution variable named target_table; the format of the complete statement is:
insert into !!target_table!! (<list of column headers>) values (<Row 1 data>), (<Row 2 data>), ... (<Row N data>) ;
If the DESCRIPTION keyword is used, the description text will be included as a SQL comment before the INSERT statement. The INCLUDE metacommand can be used to include a file written in this format, and the target table name filled in with an appropriately-named substitution variable. This output format can also be used to copy data between databases when it is not possible to use execsql's CONNECT and COPY metacommands.

Exporting Data Using a Template

Template-based exports provide a simple form of report generation or mail-merge capability. The template used for this type of export is a freely-formatted text file containing placeholders for data values, plus whatever additional text is appropriate for the purpose of the report. The exported data will therefore not necessarily be in the form of a table, but may be presented as lists, embedded in paragraphs of text, or in other forms.

execsql supports three different template processors, each with its own syntax. The template processor that will be used is controlled by the "template_processor" configuration property. These processors and the syntax they use to refer to exported data values are:

The default (no template processor specified)
Data values are referenced in the template by the column name prefixed with a dollar sign or enclosed in curly braces prefixed with a dollar sign. For example if an exported data table contains a column named "vessel", that column could be referred to in either of these ways:
Survey operations were conducted from $vessel. The ${vessel}'s crew ate biscuits for a week.
The default template processor does not include any features that allow for conditional tests or iteration within the template. The entire template is processed for each row in the exported data table, and all of the output is combined into the output file.
Jinja
Data values are referenced in the template within pairs of curly braces. The Jinja template processor allows conditional tests and iteration, as well as other features, within the template. The entire exported data set is passed to the template processor as an iterable object named "datatable". For example, if an exported data table contains a column named "hire_date", that column could be referred to, while iterating over the entire data set, as follows:
{% for row in datatable %} Hire date: {{ row.hire_date }} . . . {% endfor %}
The template syntax used by Jinja is very similar to that used by Django. Jinja's Template Designer Documentation provides more details about the template syntax.
Airspeed
Data values are refenced in the template by the name (or object) name prefixed with a dollar sign, or enclosed in curly braces and prefixed with a dollar sign, just as for the default template processor. The Airspeed template processor also allows conditional tests and iteration, and as with Jinja, the entire exported data set is passed to the template processor as an iterable object named "datatable". For example, if an exported data set contains bibliographic information, those columns could be referenced, while iterating over the entire data set to produce a BibTex bibliography, as follows:
#foreach ($doc in $datatable) @$doc.doc_type {$doc.doc_id, author = {$doc.author}, title = {$doc.title}, . . . } #end
The template syntax used by Airspeed duplicates that used by Apache Velocity, and the Velocity User's Guide and Reference Guide provide details about the template syntax.

The Jinja and Airspeed template processors are both more powerful than the default, but as a result are also more complex. The different alternatives may be suitable for different purposes, or for different users, based on prior experience. One potentially important difference between Jinja and Airspeed is that Airspeed requires that the entire data set be processed at once, whereas Jinja does not; for very large data sets, therefore, Airspeed could encounter memory limitations.

EXPORT QUERY

EXPORT QUERY <<query>> [TEE] [APPEND] TO <filename>|stdout AS <format> [DESCRIPTION "<description>"]
EXPORT QUERY <<query>> [TEE] [APPEND] TO <filename>|stdout WITH TEMPLATE <template_file>

Exports data to a file in the same manner as the EXPORT metacommand, except that the data source is a SQL query statement in the metacommand rather than a database table or view. The SQL query statement must be terminated with a semicolon and enclosed in double angle brackets (i.e., literally "<<" and ">>").

Like all metacommands, this metacommand must appear on a single line, although the SQL statement may be quite long. To facilitate readability, the SQL statement may be saved in a substitution variable and that substitution variable referenced in the EXPORT QUERY metacommand.

HALT

HALT ["<error_message>"] [EXIT_STATUS <n>]

Script processing is halted, and the execsql.py program terminates. If an error message is provided, it is written to the console, unless the "-v2" or "-v3" option is used, in which case the message is displayed in a dialog. If an EXIT_STATUS value is specified, the system exit status is set to that value, otherwise, the system exit status is set to 2.

HALT DISPLAY

HALT MESSAGE "<error_message>" [DISPLAY <table_or_view>] [EXIT_STATUS <n>]

Script processing is halted, and the error message is displayed in a GUI window. If a table or view name is provided, the data from that table or view is also displayed. If an EXIT_STATUS value is specified, the system exit status is set to that value, otherwise, the system exit status is set to 2.

IF

The IF metacommand allows you to test for certain conditions and control which script statements are subsequently executed. There are two forms of the IF metacommand:

The syntax for the single-line IF metacommand is:

IF([NOT] <conditional test>) {<metacommand>}

The conditional tests that can be used are listed below. For the single-line form of the IF metacommand, the metacommand to be executed must be enclosed in curly braces following the conditional test.

The syntax for the multi-line IF metacommand can take several forms, depending on whether the additional ELSE, ELSEIF, ANDIF, and ORIF clauses are used. The simplest form of the multi-line IF metacommand is:

IF([NOT] <conditional test>) <SQL statements and metacommands> ENDIF

Multi-line IF metacommands can be nested within one another, and single-line IF metacommands can appear within a multi-line IF metacommand.

The ELSE clause allows you to conditionally execute either of two sets of script commands. The form of this set of statements is:

IF([NOT] <conditional test>) <SQL statements and metacommands> ELSE <SQL statements and metacommands> ENDIF

The ELSEIF clause combines the actions of the ELSE clause with another IF metacommand—effectively, nesting another IF metacommand within the ELSE clause, but not requiring a second ENDIF statement to terminate the nested conditional test. The form of this set of statements is:

IF([NOT] <conditional test>) <SQL statements and metacommands> ELSEIF([NOT] <conditional test>) <SQL statements and metacommands> ENDIF

Multiple ELSEIF clauses can be used within a single multi-line IF metacommand. An ELSE clause can be used in combination with ELSEIF clauses, but this is not recommended because the results are not likely to be what you expect—the ELSE keyword only inverts the current truth state, it does not provide an alternative to all preceding ELSEIF clauses. To achieve the effect of a case or switch statement, use only ELSEIF clauses without a final ELSE clause.

The ANDIF clause allows you to test for the conjunction of two conditionals without having to nest IF metacommands and use two ENDIF statements. The simplest form of usage of the ANDIF clause is:

IF([NOT] <conditional test>) ANDIF([NOT] <conditional test>) <SQL statements and metacommands> ENDIF

The ANDIF clause does not have to immediately follow the IF metacommand. It could instead follow an ELSE statement, or appear anywhere at all within a multi-line IF metacommand. Usage patterns other than that illustrated above may be difficult to interpret, however, and nested IF metacommands may be preferable to complex uses of the ANDIF clause.

The ORIF clause is similar to the ANDIF clause, but allows you to test the disjunction of two conditionals. The simplest form of usage of the ORIF clause is:

IF([NOT] <conditional test>) ORIF([NOT] <conditional test>) <SQL statements and metacommands> ENDIF

The IF metacommands can be used not only to control a single stream of script commands, but also to loop over sets of SQL statements and metacommands, as shown in Example 6.

The conditional tests that can be used with IF and WAIT_UNTIL metacommands are listed in the following subsections.

ALIAS_DEFINED test

ALIAS_DEFINED(<alias>)

Evaluates whether a database connection has been made using the specified alias. Database aliases are defined using the CONNECT and PROMPT CONNECT metacommands.

COLUMN_EXISTS test

COLUMN_EXISTS(<column_name> IN <table_name>)

Evaluates whether there is a column of the given name in the specified database table. The table name may include a schema. execsql queries the information schema tables for those DBMSs that have information schema tables. You must have permission to use these system tables. If you do not, an alternative approach is to try to select data from the specified column table and determine if an error occurs.

CONSOLE test

CONSOLE

Evaluates whether the GUI console is running.

DATABASE_NAME test

DATABASE_NAME(<database_name>)

Evaluates whether the current database name matches the one specified. Database names used in this conditional test should exactly match those contained in the "$CURRENT_DATABASE" substitution variable.

DBMS test

DBMS(<dbms_name>)

Evaluates whether the current DBMS matches the one specified. DBMS names used in this conditional test should exactly match those contained in the "$CURRENT_DBMS" substitution variable.

DIRECTORY_EXISTS test

DIRECTORY_EXISTS(<directory_name>)

Evaluates whether there is an existing directory with the given name.

EQUAL test

EQUAL("<string_1>", "<string_2>")

Evaluates whether the two values are equal. The two string representations of the values first are converted to a normalized Unicode form (Normal Form C) and then are compared as integers, floating-point values, date/time values with a time zone, date/time values, dates, Boolean values, and strings. String comparisons are case insensitive. The first of these data types to which both values can be successfully converted is the basis for determining whether the values are equal. See also IDENTICAL

FILE_EXISTS test

FILE_EXISTS(<filename>)

Evaluates whether there is a disk file of the given name.

HASROWS test

HASROWS(<view_name>)

Evaluates whether the specified table or view has a non-zero number of rows.

IDENTICAL test

IDENTICAL("<string_1>", "<string_2>")

Evaluates whether the two quoted strings are exactly identical. No Unicode normalization is done, and the comparison is case-sensitive. See also EQUAL.

IS_GT test

IS_GT(<value1>, <value2>)

Evaluates whether or not the first of the specified values is greater than the second value. If the values are not numeric, an error will occur, and script processing will halt.

IS_GTE test

IS_GTE(<value1>, <value2>)

Evaluates whether or not the first of the specified values is greater than or equal to the second value. If the values are not numeric, an error will occur, and script processing will halt.

IS_NULL test

IS_NULL("<value>")

Evaluates whether or not the specified value is null—that is, whether it is a zero-length string.

IS_ZERO test

IS_ZERO(<value>)

Evaluates whether or not the specified value is equal to zero. If the value is not numeric, an error will occur, and script processing will halt.

SCHEMA_EXISTS test

SCHEMA_EXISTS(<schema_name>)

Evaluates whether or not the specified schema already exists in the database. For DBMSs that do not support schemas (SQLite, MySQL, Firebird, and Access), this will always return a value of False. execsql queries the information schema tables, or analogous tables, for this information. You must have permission to use these system tables.

METACOMMAND_ERROR test

METACOMMAND_ERROR()

Evaluates whether the previous metacommand generated an error. This test for SQL errors will only be effective if the METACOMMAND_ERROR_HALT OFF metacommand has previously been issued. This conditional must be used in the first metacommand after any metacommand that might have encountered an error.

NEWER_DATE test

NEWER_DATE(<filename>, <date>)

Evaluates whether the specified file was last modified after the given date. This can be used, for example, to compare the date of an output file to the latest revision date of all the data rows that should be included in the output; if the data have been revised after the output file was created, the output file should be regenerated.

NEWER_FILE test

NEWER_FILE(<filename1>, <filename2>)

Evaluates whether the first of the specified files was last modified after the second of the files. This can be used, for example, to compare the date of an output file to the date of the script file that produces that output; if the script is newer, it may be IMPORTed and run again.

SQL_ERROR test

SQL_ERROR()

Evaluates whether the previous SQL statement generated an error. Errors will result from badly-formed SQL, reference to non-existent database objects, lack of permissions, or database locks. A query (e.g., an update query) that does not do exactly what you expect it to will not necessarily cause an error to occur that can be identified with this statement. This test for SQL errors will only be effective if the ERROR_HALT OFF metacommand has previously been issued.

Errors in metacommands and some other errors encountered by execsql will cause the program to halt immediately, regardless of the setting of ERROR_HALT or the use of the IF( SQL_ERROR() ) test.

SUB_DEFINED test

SUB_DEFINED(<match_string>)

Evaluates whether a replacement string has been defined for the specified substitution variable (matching string).

TABLE_EXISTS test

TABLE_EXISTS(<tablename>)

Evaluates whether there is a database table of the given name. execsql queries the information schema tables, or analogous tables, for this information. You must have permission to use these system tables. If you do not, an alternative approach is to try to select data from the table and determine if an error occurs; for example:

-- !x! error_halt off select count(*) from maybe_not_a_real_table; -- !x! error_halt on -- !x! if(sql_error())

VIEW_EXISTS test

VIEW_EXISTS(<viewname>)

Evaluates whether there is a database view of the given name. For Access, this tests for the existence of a query of the given name. execsql queries the information schema tables, or analogous tables, for this information. You must have permission to use these system tables. If you do not, the alternative approach described for the TABLE_EXISTS conditional can be used.

IMPORT

Imports data from a file into a new or existing database table. Data can be imported from either a text file or a spreadsheet. The syntax of the IMPORT metacommand for importing data from a text file is:

IMPORT TO [NEW|REPLACEMENT] <table_name> FROM <file_name> [WITH [QUOTE <quote_char> DELIMITER <delim_char>] [ENCODING <encoding>]] [SKIP <lines>]

The syntax for importing data from an OpenDocument spreadsheet is:

IMPORT TO [NEW|REPLACEMENT] <table_name> FROM <file_name> SHEET <sheet_name> [SKIP <rows>]

The syntax for importing data from an Excel spreadsheet is:

IMPORT TO [NEW|REPLACEMENT] <table_name> FROM EXCEL <file_name> SHEET <sheet_name>

Column names and column order in the input must exactly match those in the target table. The column names in the input must also be valid for the DBMS in use.

If the "WITH QUOTE <quote_char> DELIMITER <delim_char>" clause is not used with text files, execsql will scan the text file to determine the quote and delimiter characters that are used in the file. By default, the first 100 lines of the file will be scanned. You can control the number of lines scanned with the "-s" option on the command line. If the "WITH..." clause is used, the file will not be scanned to identify the quote and delimiter characters regardless of the setting of the "-s" option.

execsql will read CSV files containing newlines embeded in delimited text values. Scanning of a CSV file to determine the quote and delimiter characters may produce incorrect results if most of the physical lines scanned consist of text that makes up only part of a logical data column.

The quoting characters that will be recognized in a text file, and that can be specified in the "WITH..." clause are the double quote (") and the single quote ('). If no quote character is used in the file, this can be specified in the metacommand as "WITH QUOTE NONE".

The delimiter characters that will be recognized in a text file, and that can be specified in the "WITH..." clause are the comma (,), semicolon (;), vertical rule (|), tab, and the unit separator. To specify that the tab character is used as a delimiter, use "WITH...DELIMITER TAB", and to specify that the unit separator is used as a delimiter, use "WITH...DELIMITER US".

The SKIP key phrase specifies the number of lines (or rows) at the beginning of the file (or worksheet) to discard before evaluating the remainder of the input as a data table.

If the NEW keyword is used, the input will be scanned to determine the data type of each column, and a CREATE TABLE statement run to create a new table for the data. Scanning of the file to determine data formats is separate from the scanning that is done to determine the quote and delimiter characters. If the table already exists when the NEW keyword is used, a fatal exception will result. If the REPLACEMENT keyword is used, the result is the same as if the NEW keyword were used, except that an existing table of the given name will be deleted first. If the table does not exist, an informational message will be written to the log.

If a table is scanned to determine data types, any column that is completely empty (all null) will be created with the text data type. This provides the greatest flexibility for subsequent addition of data to the table. However, if that column ought to have a different data type, and a WHERE clause is applied to that column assuming a different data type, the DBMS may report an error because of incomparable data types.

The handling of Boolean data types when data are imported depends on the capabilities of the DBMS in use. See the relevant section of the SQL syntax notes.

If a column of imported data contains only numeric values, but any non-zero value has a leading digit of "0", that column will be imported as a text data type (character, character varying, or text).

When execsql generates a CREATE TABLE statement, it will quote column names that contain any characters other than letters, digits, or the underscore ("_"). A mixture of uppercase and lowercase letters in a column name is not taken as an indication that a quoted identifier should be used for the column name, and execsql does not fold column names to either uppercase or lowercase. Case sensitivity and case-folding behavior varies between DBMSs, and execsql leaves it to the user to manage these differences.

The case-folding behavior of the DBMS should also be considered when specifying the table name in the IMPORT metacommand. When execsql checks to see if a table exists, it queries the information schema using the table name exactly as given (i.e., execsql does not do any case folding); if the actual table name differs because of case folding by the DBMS, the check will fail and an error will occur.

If neither the NEW or REPLACEMENT keywords are used, the table must already exist, and have column names identical to those in the file, and in the same order. The data types in the table must also be compatible with those in the file.

If the NEW keyword is used, the target table will be created without a primary key or other constraints. If data are imported to an existing table, they must meet any constraints already in place on that table. If data are imported to an existing table, the imported data will be added to any already-existing data. If existing data are to be replaced, they should be deleted before the IMPORT metacommand is run.

The NEW keyword cannot be used within a batch with Firebird. Firebird requires that the CREATE TABLE statement be committed—the table actually created—before data can be added. There is only one commit statement for a batch, at the end of the batch, and therefore the CREATE TABLE statement is not committed before data are added.

If the ENCODING keyword is not used, the character encoding of text files imported with the IMPORT metacommand is as specified with the "-i" command-line option or the corresponding configuration file option. If not specified in either of these ways, the encoding is assumed to be UTF-8. If a UTF byte order mark is found at the start of a data file, the encoding indicated by that marker will be taken as definitive regardless of the ENCODING keyword or the "-i" option.

Import of data from text files to Postgres and MySQL uses the fast file reading features provided by both of those databases: Postgres' COPY command and MySQL's LOAD DATA LOCAL INFILE command. For Postgres, if the file encoding is of a type that is not recognized by Postgres (see https://www.postgresql.org/docs/current/static/multibyte.html), a slower loading routine will be used, with encoding conversion handled by execsql.

The sheet name used when importing data from a spreadsheet can be either the sheet name, as it appears on the tab at the bottom of the sheet, or the sheet number. Comparison of the actual sheet names to the value given is case-insensitive. Sheet numbers start at 1.

When MS-Excel saves an OpenDocument spreadsheet, it may create an additional empty column to the right of all data columns. This spurious column is not eliminated by opening and re-saving the spreadsheet using LibreOffice Calc (as of version 5.0.2 at least). The IMPORT metacommand will report an error with such a file because of the absence of a column header on the extra column. To avoid this problem, as well as other issues related to incorrect implementation of the OpenDocument standard in Excel, and the data corruption that can occur when Excel imports and exports CSV files, and the ambiguous representation of dates in Excel, Excel should not be used for data that may be transferred to or from databases or other formats. Import of data from Excel may also take 10-100 times longer—or more—than import from a text file.

Some performance considerations when using IMPORT are:

In general, if an error occurs while importing data, none of the new data should be in the target table (the operation is not committed). However, MySQL may issue messages about data type incompatibility to the standard error device (ordinarily the terminal), yet load some or all of the data. If the NEW or REPLACEMENT keywords are used, depending on the DBMS and where the error occurred, the target table may be created even if the data are not loaded.

INCLUDE

INCLUDE <filename>

The specified file should be a script that contains SQL statements and/or metacommands. Those SQL statements and metacommands will be inserted into the script at the point where the INCLUDE metacommand occurs.

LOG

LOG "<message>"

Writes the specified message to execsql's log file.

LOG_WRITE_MESSAGES

LOG_WRITE_MESSAGES <ON|OFF>

When this is set to ON, all output of the WRITE metacommand will also be written to execsql's log file. The default is not to echo WRITE messages to the log. This behavior can also be controlled with a configuration option.

MAX_INT

MAX_INT <integer_value>

Specifies the threshold between integer and bigint data types that is used by the IMPORT and COPY metacommands when creating a new table. Any column with integer values less than or equal to this value (max_int) and greater than or equal to -1 × max_int - 1 will be considered to have an 'integer' type. Any column with values outside this range will be considered to have a 'bigint' type. The default value for max_int is 2147483647. The max_int value can also be altered using a configuration option.

METACOMMAND_ERROR_HALT

METACOMMAND_ERROR_HALT ON|OFF

When METACOMMAND_ERROR_HALT is set to ON, which is the default, any errors that occur during execution of a metacommand will cause an error message to be displayed immediately, and execsql to exit. When METACOMMAND_ERROR_HALT is set to OFF, then metacommand errors will be ignored, but can be evaluated with the IF METACOMMAND_ERROR conditional.

PAUSE

PAUSE "<text>" [HALT|CONTINUE AFTER <n> MINUTES|SECONDS]

Displays the specified text and pauses script processing. You can continue script processing with the <Enter> key, or halt script processing with the <Esc> key. The message will be displayed on the console by default; if the "-v" command-line option is used, the message will be displayed in a GUI dialog.

If the "HALT|CONTINUE..." clause is used, the PAUSE prompt will disappear after the specified time, regardless of whether the <Enter> or <Esc> keys were struck. If the PAUSE prompt times out in this way, script processing will be either halted or continued, as specified. The prompt with a timeout limit will look like this on the console:

PAUSE prompt with timeout

The countdown of time remaining is always displayed in seconds.

If the "-v1", "-v2", or "-v3" option is used, the prompt will appear in a GUI dialog instead of on the console.

If the "HALT" action is taken, either as a result of user input or as a result of a timeout, the effect on the script depends on the CANCEL_HALT setting. If script processing is halted, the system exit value will be set to 2.

PROMPT ASK

PROMPT ASK "<question>" SUB <match_string> [DISPLAY <table_or_view>]

Prompts for a yes or no response to the specified question, using a dialog box, and assigns the result, as either "Yes" or "No", to the substitution variable specified. A data table or view can optionally be displayed with the question (as shown for the PROMPT DISPLAY metacommand). The "Y" and "N" keys will select the corresponding response, and the <Enter> key will also select the "Yes" response. The <Esc> key will cancel the script. The selection is also logged. If the prompt is canceled, script processing is halted, and the system exit value is set to 2.

PROMPT CONNECT

PROMPT [MESSAGE "<text>"] CONNECT AS <alias>

Prompts for database connection parameters in a dialog box, and assigns that connection to the specified database alias. Any database connection previously associated with this alias will be closed, even if the prompt is canceled.

The connection dialog looks like this:

Database connection dialog

The prompt provides several common options for the database encoding. If the database uses a different encoding, you can type in the name of that encoding.

If the port is not specified, the default port for the selected DBMS will be used.

If a password is not provided, a connection will be attempted without using any password; there will be no additional prompt for a password.

If a file-based DBMS (MS-Access or SQLite) is selected, the prompt for the server and other information will be replaced by a prompt for a file name.

PROMPT DIRECTORY

PROMPT DIRECTORY SUB <match_string>

Prompts for the name of an existing directory, using a dialog box, and assigns the selected directory name (including the full path) to the substitution variable specified. The selection is also logged. If the prompt is canceled, unless CANCEL_HALT is set to OFF, script processing is halted, and the system exit value is set to 2. If CANCEL_HALT is set to ON, the specified substitution variable will be undefined.

PROMPT DISPLAY

PROMPT MESSAGE "<text>" DISPLAY <table_or_view_name>

Displays the selected view or table in a window with the specified message and both 'Continue' and 'Cancel' buttons. If the 'Continue' button is selected, the script will continue to run. If the 'Cancel' button is selected, the script will immediately halt. The Enter key also carries out the action of the 'Continue' button, and the Escape key carries out the action of the 'Cancel' button.

The prompt display looks like this: