Metacommands

The execsql program supports several special commands–metacommands–that import and export data, conditionally execute parts of the script, report status information, and perform other actions. Some of the things that can be done with metacommands are:

• Include the contents of another SQL script file.
• Import data from a text file or spreadsheet to a new or existing table.
• Export data to the terminal or a file in a variety of formats.
• Connect to multiple databases and copy data between them.
• Write text out to the console or to a file.
• Stop or pause script processing.
• Display a data table for the user to review.
• Display a pair of data tables for the user to compare.
• Prompt the user to respond to a question or enter a value.
• Prompt for the names of files or directories to be used.
• Create sub-scripts that can be executed repeatedly.
• Conditionally execute SQL or metacommands based on data values or user input.
• Execute an operating system command.

Whereas SQL is often embedded in programs written in other languages, execsql inverts this paradigm through the use of metacommands (and substitution variables). These allow database operations to be interleaved with user interactions and file system access in a way that may be easier to develop, easier to re-use, and more accessible to multiple users than embedded SQL in a high-level programming language.

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. An example metacommand is:

-- !x! import to staging.weather from billings2012.csv

Other illustrations of metacommand usage are in the examples.

Because metacommands are embedded in comments, they are hidden from other SQL script processors such as psql for Postgres, mysql for MySQL/MariaDB, and sqlcmd for SQL Server. Thus, a script containing execsql metacommands can potentially also be run using a DBMS’s own native script processor. Scripts that make extensive use of execsql’s features, however, may not run satisfactorily with other script processors. Scripts that use metacommands such as IF, IMPORT, INCLUDE, EXECUTE SCRIPT, or USE, or that use substitution variables in SQL statements, are not likely to run as expected with other script processors.

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. The syntax descriptions for the metacommands use angle brackets to identify required replaceable elements, and square brackets to identify optional replaceable elements.

ASK

ASK "<question>" SUB <match_string>

Prompts for a yes or no response to the specified question, presenting the prompt on the console, and assigns the result, as either “Yes” or “No”, to the substitution variable specified. The “Y” and “N” keys will select the corresponding 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.

See the PROMPT ASK metacommand for a version of this command that uses a GUI window and that can display a data table.

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.

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 AUTOCOMMIT OFF metacommand and 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 committed until a batch is completed. This allows execsql to emulate tools that operate in batch mode by default (specifically, sqlcmd).

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 sends a “rollback” command to the database to revert the action of any SQL statements that have already been executed in the batch, but does not terminate the batch.

Metacommands may be included inside a batch, but note that the IMPORT and COPY metacommands always commit the changes they make, so if these metacommands are used inside a batch, any preceding SQL statements in the batch will also be committed.

When the END BATCH metacommand is processed by execsql, a “commit” command will be sent to all databases that have been used inside the batch. Multiple databases may be used inside a batch if the USE metacommand is used inside the batch. The BATCH metacommands therefore provide a limited sort of cross-database transaction control.

The BEGIN/END BATCH metacommands can be nested. However, the inner END BATCH metacommand will commit all changes to the databases that have been used, which may include databases used in the outer batch as well. Therefore completion of a nested batch may result in premature commitment of some or all SQL statements in the outer batch. Similarly, a ROLLBACK BATCH metacommand within the inner batch will also roll back any SQL commands sent to the same databases in the outer batche. Thus, although the BATCH commands can be nested, database transactions cannot be. Nesting of BATCH metacommands allows a script file or a SCRIPT containing a batch to be INCLUDEd or EXECUTEd, respectively, within another batch.

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

• The AUTOCOMMIT metacommand, which provides a different method of integrating IMPORT and COPY metacommands with a sequence of SQL statements
• The IF metacommand, which provides a way of conditionally executing SQL statements and metacommands such as IMPORT and COPY
• The BEGIN/END SCRIPT and EXECUTE SCRIPT metacommands, which allow both SQL statements and metacommands to be grouped together and executed as a group, with AUTOCOMMIT either on or off.

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 committed.

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:

• Substitution variables should ordinarily not be used as script names because they will not have been defined yet, unless they were defined in the variables section of a configuration file; and
• The BEGIN/END SCRIPT commands are not ordinarily subject to conditional execution.

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.

“CREATE SCRIPT” can be used as an alias for “BEGIN SCRIPT”.

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.

CONFIG

Several of the configuration settings that can be specified either with command-line options or in configuration files can also be dynamically altered using metacommands.

CONFIG 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 use when creating 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.

CONFIG 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 use when creating 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.

CONFIG CONSOLE WAIT_WHEN_DONE ON|OFF

Controls the persistence of any console window at the completion of the script when the script either completes normally or exits prematurely as a result of the user’s response to a prompt. If the value is set to “ON” (the default value is “OFF”), 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 metacommand has the same action as the console_wait_when_done configuration setting. The value of this setting can be evaluated with the “$console_wait_when_done_state” system variable.

CONFIG CONSOLE WAIT_WHEN_ERROR ON|OFF

Controls the persistence of any console window at the completion of the script if an error occurs. If the value is set to “ON” (the default value is “OFF”), the console window will remain open until explicitly closed by the user after an error occurs. This metacommand has the same action as the console_wait_when_error_halt configuration setting. The value of this setting can be evaluated with the “$console_wait_when_error_state” system variable.

CONFIG EMPTY_STRINGS YES|NO

Controls 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. A string containing only space characters is considered to be an empty string.

CONFIG IMPORT_COMMON_COLUMNS_ONLY YES|NO

Controls whether the IMPORT metacommand will import CSV files with more columns than the target table. This has the same action as the `import_common_columns_only configuration setting. The argument should be either “Yes” or “No”. The default value is “No”, in which case the IMPORT metacommand will halt with an error message if the target table does not have all of the columns that are in the file to be imported.

CONFIG LOG_WRITE_MESSAGES ON|OFF

Controls whether output of the WRITE metacommand will also be written to execsql’s log file. When this is set to ON (the default value is OFF), all output of the WRITE metacommand will also be written to execsql’s log file. This behavior can also be controlled with the log_write_messages configuration option.

CONFIG MAKE_EXPORT_DIRS YES|NO

Controls whether the EXPORT metacommand will automatically create any directories that are named in an output filename and that do not already exist. The user must have appropriate permissions to create those directories.

CONFIG 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.

CONNECT

For PostgreSQL:

CONNECT TO POSTGRESQL(SERVER=<server_name>, DB=<database_name>
      [, USER=<user>, NEED_PWD=TRUE|FALSE] [, PORT=<port_number>]
      [, PASSWORD=<password>] [, 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>]
      [, PASSWORD=<password>] [, 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>]
      [, PASSWORD=<password>] [, 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 Postgres, MySQL/MariaDB, Access, and DSN connections allow a password to be specified. If a password is needed for any database but is not provided, execsql will display a prompt for the password. Embedding a password in a SQL script is a security weakness, but may be needed when a script is to be run regularly as a system job. This risk can be minimized by either:

• Using the PROMPT ENTER_SUB metacommand to prompt for the password when the script starts and using the PAUSE…CONTINUE metacommand to control the timing of successive runs of a subscript; or
• Storing an encrypted copy of the password in a substitution variable and decrypting it before passing it to the CONNECT 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 there is 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 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 discussion of 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.

The data addition to the target table is always committed. Therefore, the COPY metacommand generally should not be used within transactions or BATCHes.

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.

The data addition to the target table is always committed. Therefore, the COPY QUERY metacommand generally should not be used within transactions or BATCHes.

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. Multiline messages can be used if the message text is contained in a substitution variable.

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.

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.

When ERROR_HALT is set to OFF inside a transaction, any SQL error will ordinarily cause the entire transaction to fail.

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.

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.

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.

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 <table_or_view> [TEE] [APPEND] TO <filename>|stdout
    AS <format> [DESCRIPTION "<description>"]

EXPORT <table_or_view> [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:

B64

Data decoded from a base64-encoded format with no headers, quotes, or delimiters between either columns or rows. This is similar to the RAW export option except that base64-decoding is performed. This format is intended to be used for export of base64-encoded binary data such as images, and ordinarily should be used to export a single value. No description text will be included in the output even if it is provided.

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, and ordinarily should be used to export a single value. 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. If any columns of the table contain binary data, a message identifying the size, in bytes, of the data will be displayed instead of the data itsef.

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 (Unicode 001F) 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”. The names of the column headers are passed as a separate iterable object named “headers”. 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 referenced 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”. The names of the column headers are passed as a separate iterable object named “headers”. 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 in the same manner as the EXPORT metacommand, except that the data source is a SQL query statement that is contained 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 MESSAGE "<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 3.

Warning

A backward-incompatible change to HALT MESSAGE was made in version 1.26.1.0 (2018-06-13): the default exit status was changed from 2 to 3.

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 status is set to 3.

Warning

A backward-incompatible change to HALT DISPLAY was made in version 1.26.1.0 (2018-06-13): the default exit status was changed from 2 to 3.

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:

• A single-line IF statement that will conditionally run a single metacommand.
• A multi-line IF statement that must be terminated with an ENDIF metacommand. The multi-line form supports ELSE, ELSEIF, ANDIF, and ORIF clauses.

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.

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. This test is as forgiving as possible, and returns True whenever the two values are plausibly the same. See also IDENTICAL.

FILE_EXISTS test

FILE_EXISTS(<filename>)

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

HASROWS test

HASROWS(<table_or_view)

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. This test is as unforgiving as possible, and returns False whenever the two values are not exactly the same. 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_TRUE test

IS_TRUE(<value>)

Evaluates whether or not the specified value represents a Boolean value of True. Values of “Yes”, “Y”, “True”, “T”, and “1” are considered to represent True values; anything else is considered to represent a False value. The values are not case-sensitive and should not be quoted.

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.

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 INCLUDEd to run it again.

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, MariaDB, 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.

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 tabular 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 in the input must 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 execsql’s 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 (Unicode 001F). 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.

By default, the target table must have all of the columns that are present in the text file to be imported. If it does not, an error will result. The import_common_columns_only configuration parameter can be used to allow import of data from text files with more columns than the target table. In either case, the target table may have more columns than the text file being imported.

Under some circumstances, import of data from text files to Postgres and MySQL/MariaDB uses the fast file reading features provided by both of those databases: Postgres’ COPY command and MySQL’s LOAD DATA LOCAL INFILE command. The text file and the target table must have exactly the same columns, in the same order, for the fast file reading routines to be used. In addition, the “empty_strings” configuration setting must be set to “Yes” (the default). If these conditions are not satisfied, or, 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. Explicitly setting either the encoding or the quote and delimiter characters in the metacommand will cause execsql to use its own import routine instead of the fast file reading features of Postgres or MySQL.

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.

Because the data addition to the target table is always committed, the IMPORT metacommand generally should not be used within transactions or BATCHes.

Some performance considerations when using IMPORT are:

• Creating the table using a separate CREATE TABLE statement before the IMPORT metacommand will be faster than using the NEW or REPLACEMENT keywords. The time required for execsql to scan an entire file to determine data types can be much greater than the time required to import the file.
• When importing to Postgres from a text file that has an encoding that is recognized by Postgres, data are read and processed in chunks that are 32 kb in size. A larger or smaller value may give better performance, depending on system-specific conditions. The “-z” command-line option can be used to alter the buffer size.

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/MariaDB 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.

The name, size, and date of the IMPORTed file are written to the execsql.log file.

IMPORT_FILE

Imports an entire file into a single column, on a new row, of an existing database table. The syntax of the IMPORT_FILE metacommand is:

IMPORT_FILE TO TABLE <table_name> COLUMN <column_name> FROM <file_name>

The data type of the column must allow insertion of binary data. If the table contains any other columns that must be non-null, those columns must have default values.

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.

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.

The METACOMMAND_ERROR_HALT metacommand does not itself set or reset the internal flag that indicates whether a metacommand has encountered an error. This is so that a “METACOMMAND_ERROR_HALT Off” command can be used immediately after a potentially-failing metacommand and not alter the error flag that is set by the previous command. Specifically, constructions like this:

-- !x! metacommand_error_halt off
-- !x! connect to postgresql(server=none, db=imaginary, user=nobody, need_pw=False) as pg
-- !x! metacommand_error_halt on
-- !x! if(metacommand_error())

can be used. If the CONNECT metacommand fails, the following IF metacommand will identify that error despite the intervening “METACOMMAND_ERROR_HALT On” command.

ON CANCEL_HALT EMAIL

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

ON CANCEL_HALT EMAIL CLEAR

Sends the specified email only if the user cancels the script at a prompt. This command operates similarly to the EMAIL metacommand, except for its deferred operation.

The form of the metacommand with the “CLEAR” keyword will eliminate any email specification that was previously established.

The email specification is scanned for substitution variables at two different times: first, when the ON CANCEL_HALT EMAIL metacommand is invoked, and second, when the specified email is going to be sent. Substitution variables to be replaced when the email is sent must not be defined at the time that the metacommand is invoked. See the ON ERROR_HALT WRITE metacommand for an example of the use of deferred substitution of metacommands.

This metacommand sends email after any action triggered by an ON CANCEL_HALT WRITE metacommand has completed. This allows any output file created by the ON CANCEL_HALT WRITE metacommand to be included in the email message or as an attachment.

If an error occurs during the sending of email (for example, if no SMTP port is defined in a configuration file), then the email will not be sent and no error message describing this failure will be issued. An error message describing the error that triggered the sending of email will be issued, as it would be if the ON CANCEL_HALT EMAIL metacommand had not been used. The execsql log file will contain a message describing the failure of the ON CANCEL_HALT EMAIL metacommand.

ON CANCEL_HALT WRITE

ON CANCEL_HALT WRITE "<text>" [[TEE] TO <output>]

ON CANCEL_HALT WRITE CLEAR

Writes the specified text only if the user cancels the script in response to a prompt. The given text is written immediately before the standard error message is displayed. This command operates similarly to the WRITE metacommand, except for its deferred operation.

The text to be written may be enclosed in double quotes (as shown above), in single quotes, or in matching square brackets.

The form of the metacommand with the “CLEAR” keyword will eliminate any message that was previously established.

The text to be written is scanned for substitution variables at two different times: first, when the ON CANCEL_HALT WRITE metacommand is invoked, and second, when the specified text is actually written. Substitution variables to be replaced when the text is written must not be defined at the time that the metacommand is invoked.

If an error occurs when the text is written (for example if an attempt is made to write to a read-only file), then the text will not be written and no error message describing this failure will be issued. An error message describing the error that triggered the CANCEL_HALT WRITE action will be issued, as it would be if the ON CANCEL_HALT WRITE metacommand had not been used. The execsql log file will contain a message describing the failure of the ON CANCEL_HALT WRITE metacommand. The execsql log file will contain a message describing the failure of the ON CANCEL_HALT WRITE metacommand.

ON ERROR_HALT EMAIL

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

ON ERROR_HALT EMAIL CLEAR

Sends the specified email only if an error occurs in the definition or processing of a SQL statement or metacommand. This command operates similarly to the EMAIL metacommand, except for its deferred operation.

The form of the metacommand with the “CLEAR” keyword will eliminate any email specification that was previously established.

The email specification is scanned for substitution variables at two different times: first, when the ON ERROR_HALT EMAIL metacommand is invoked, and second, when the specified email is going to be sent. Substitution variables to be replaced when the email is sent must not be defined at the time that the metacommand is invoked. See the ON ERROR_HALT WRITE metacommand for an example of the use of deferred substitution of metacommands.

This metacommand sends email after any action triggered by an ON ERROR_HALT WRITE metacommand has completed. This allows any output file created by the ON ERROR_HALT WRITE metacommand to be included in the email message or as an attachment.

If an error occurs during the sending of email (for example, if no SMTP port is defined in a configuration file), then the email will not be sent and no error message describing this failure will be issued. An error message describing the error that triggered the sending of email will be issued, as it would be if the ON ERROR_HALT EMAIL metacommand had not been used. The execsql log file will contain a message describing the failure of the ON ERROR_HALT EMAIL metacommand.

ON ERROR_HALT WRITE

ON ERROR_HALT WRITE "<text>" [[TEE] TO <output>]

ON ERROR_HALT WRITE CLEAR

Writes the specified text only if an error occurs in the definition or processing of a SQL statement or metacommand. The given text is written immediately before the standard error message is displayed. This command operates similarly to the WRITE metacommand, except for its deferred operation.

The text to be written may be enclosed in double quotes (as shown above), in single quotes, or in matching square brackets.

The form of the metacommand with the “CLEAR” keyword will eliminate any message that was previously established.

The text to be written is scanned for substitution variables at two different times: first, when the ON ERROR_HALT WRITE metacommand is invoked, and second, when the specified text is actually written. Substitution variables to be replaced when the text is written must not be defined at the time that the metacommand is invoked. For example, this script:

create temporary view ok as
select long_text from some_data where row_number < 4;

-- !x! sub errmsg **** An error occurred:
-- !x! sub_append errmsg Last SQL (by simple substitution): !!$last_sql!!
-- !x! sub_append errmsg Last SQL (deferred): !!!!deferred_msg!!!!
-- !x! on error_halt write "!!errmsg!!"
-- !x! sub deferred_msg $last_sql

create temporary view not_ok as
select long_text from some_data where no_such_function(row_id);

-- !x! export not_ok to stdout as txt

will produce the following output when the EXPORT metacommand is run:

**** An error occurred:
Last SQL (by simple substitution): create temporary view ok as
select long_text from some_data where  row_number < 4;
Last SQL (deferred): create temporary view not_ok as
select long_text from some_data where no_such_function(row_id);
**** Error in metacommand.
     Line 69 of script on_error_halt_write.sql
     OperationalError: no such function: no_such_function in ../../execsql/execsql.py on line 2434 of execsql.
     export not_ok to stdout as txt
     Metacommand: export not_ok to stdout as txt
     Error occurred at 2017-10-07 17:56:24 UTC.

The “deferred_msg” substitution variable displays the SQL command that actually caused the error because that variable was not defined at the time that the ON ERROR_HALT WRITE metacommand was run.

If an error occurs when the text is written (for example if an attempt is made to write to a read-only file), then the text will not be written and no error message describing this failure will be issued. An error message describing the error that triggered the ERROR_HALT WRITE action will be issued, as it would be if the ON ERROR_HALT WRITE metacommand had not been used. The execsql log file will contain a message describing the failure of the ON ERROR_HALT WRITE metacommand. The execsql log file will contain a message describing the failure of the ON ERROR_HALT WRITE metacommand.

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:

The countdown of time remaining is always displayed in seconds.

If the “-v1”, “-v2”, or “-v3” command-line 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.

PG_VACUUM

PG_VACUUM <vacuum arguments>

Runs the ‘vacuum’ command on the current database if the current DBMS is Postgres. The ‘vacuum’ command will not execute successfully as a SQL command because it requires a change in the configuration of the (psycopg2) connection. This metacommand makes that change, runs the ‘vacuum’ metacommand, and restores the connection configuration to its default setting.

This metacommand has no effect if the current DBMS is not PostgreSQL.

Note

The PG_VACUUM metacommand commits all pending transactions when it runs.

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.

See the ASK metacommand for a version of this command that presents the prompt on the console.

PROMPT COMPARE

PROMPT COMPARE <table1> AND|BESIDE <table2> PK(<col1>[, col2[, col3...]]) [MESSAGE "<text>"]

Displays the two specified tables in a graphical interface. The two tables must have at least one column name in common. Clicking on a row in one of the tables will highlight that row and the first matching row in the other table. The names of all columns that are to be used to match rows must be specified within the parentheses of the PK() phrase. When more than one column name is listed, each additional colum name must be preceded by a comma.

When the AND keyword is used, the second table is displayed below the first table. When the BESIDE keyword is used, the second table is displayed to the right of the first table.

The display looks like this with the BESIDE orientation:

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.

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:

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:

If any columns of the table contain binary data, a message identifying the size, in bytes, of the data will be displayed instead of the data itsef.

PROMPT ENTER_SUB

PROMPT ENTER_SUB <match_string> [PASSWORD] MESSAGE "<text>"
    [DISPLAY <table_or_view>]
    [TYPE INT|FLOAT|BOOL|IDENT]
    [LCASE|UCASE]

Prompts for a replacement string to be assigned to the specified substitution variable (matching string). Entry of a value is not required; the ‘OK’ button (or ‘Enter’ key) can be used to continue even when nothing has been entered, and if this is done, an empty string will be returned.

If the “PASSWORD” keyword is included, the characters that are typed in response to the prompt will be displayed as asterisks.

The “TYPE” keyword can be used to limit the type of entry provided. This keyword takes any of the following arguments, and constrains the entry as described:

• INT: Only digits may be entered, optionally preceded by a minus sign.
• FLOAT: Only digits and a single decimal point may be entered, optionally preceded by a minus sign.
• BOOL: Only the values “True” or “False”, or any prefix of those words, may be entered. Characters may be entered in upper- or lower-case. If only a prefix of the words is entered, the return value will be forced to an entire word, either “True” or “False” as appropriate.
• IDENT: Only letters, digits, and the underscore character may be entered, and the first character must be a letter.

The keywords “LCASE” and “UCASE” force the returned value to be all lowercase or all uppercase, respectively.

PROMPT ENTRY_FORM

PROMPT ENTRY_FORM <specification_table> MESSAGE "<text>"
    [DISPLAY <table_or_view>]

Dynamically creates a data entry form following the specifications in the specification_table and assigns the entered values to the substitution variables named in the specification table.

The data entry form will have one data entry prompt for every row in the specification table. The following columns in the specification table will be used to construct the data entry form:

sub_var

The name of the substitution variable to which the entered value will be assigned. This column is required, and must contain non-null text.

prompt

The text to display on the form as a prompt to the user to indicate what information should be entered. This column is required, and must contain non-null text.

required

An indicator of whether a non-null value must be provided. This column is optional. If present, it should have a Boolean data type. If the column is missing or the contents are null, the value will not be required.

initial_value

The initial, or current, value. It will be displayed on the form and may be replaced. This column is optional, and if present, its contents may be null.

width

An integer specifying the width of the entry area for this value, in characters. This column is optional, and if present, its contents may be null.

entry_type

Text specifying the type of entry control to use on the form. This may take the values “checkbox” or “textarea”. The former will present a checkbox on the form, and the latter will present a multi-line text entry area. If this column has any other value, or is null, or is missing, either a text entry control will be used, or a dropdown control will be used if a lookup table is specified. If “checkbox” is specified, the values returned in the substitution variable will always be either “0”, indicating that the checkbox was cleared, or “1”, indicating that the checkbox was checked.

lookup_table

The name of a table or view containing, in its first column, a set of valid values for this entry. This column is optional, and if present, its contents may be null. If present, the entry will be constrained to only members of the given list.

validation_regex

A regular expression pattern to be used to validate the entry. This validation check will be applied when the entry is about to lose focus; if the entered value does not match the regular expression, the entry will retain focus until it is corrected. This column is optional, and if present, its contents may be null.

validation_key_regex

A regular expression pattern to be used to validate each keystroke for the entry. This validation check will be applied for each keystroke while the entry has the focus. The entire value, with the additional keystroke applied, must match the regular expression. If it does not match, the keystroke will not change the entry. This column is optional, and if present, its contents may be null.

sequence

A value used to specify the order in which values should appear on the form. This column is optional; if absent, the order of values on the form is indeterminate.

The order of the columns in the specification table does not matter. The specification table may contain additional columns other than those listed above; if it does, those columns will be ignored.

After data entry is complete and the data entry form is closed with the “Continue” button that appears on the form, the designated substitution variables will be defined to have the corresponding values that were entered. Substitution variables will not be defined for values that were not entered (were left empty on the form) even if they had been defined previously—except for checkboxes, for which the substitution variable is always defined and assigned a value of “0” or “1”.

Although the PROMPT ENTRY_FORM metacommand supports validation of individual entries through the use of either a list of valid values or a regular expression, it does not support cross-column validation or foreign key checks (except for single valid values). The primary purpose of execsql is to facilitate scripting, and therefore documentation, of data modifications, and interactive data entry runs counter to that purpose. There are nevertheless circumstances in which a data entry form is an appropriate tool to collect user input. Use of a simple custom data entry form is illustrated in Example 18 and Example 23.

PROMPT OPENFILE

PROMPT OPENFILE SUB <match_string>

Prompts for the name of an existing file (implicitly, to be opened), using a dialog box, and assigns the selected filename (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 SAVEFILE

PROMPT SAVEFILE SUB <match_string>

Prompts for the name of a new or existing file, using a dialog box, and assigns the selected filename (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 SELECT_SUB

PROMPT SELECT_SUB <table_or_view> MESSAGE "<prompt_text>" [CONTINUE]

Displays the selected data table or view, similar to the PROMPT DISPLAY metacommand, but allows you to select a single row of data, and then assigns the data values from that row to a set of substitution variables corresponding to the column names, but prefixed with the “@” character. This prefix prevents any conflict between these automatically-assigned substitution variables and any others that you may have created with the SUB command or by any other means—except for the SELECT_SUB metacommand, which uses the same prefix for substitution variables.

Data can be selected from the display either by highlighting the row with a single mouse click and then clicking on the “OK” button, or by double-clicking on a row. If data selection is canceled either with the “Cancel” button or by hitting the Escape key, script processing will be halted and the system exit value will be set to 2, unless CANCEL_HALT has been set to OFF.

Null values in the selected data row will be represented by substitution variables with zero-length string values.

If the CONTINUE keyword is used, then a “Continue” button will also be displayed in the dialog box. This option allows the user to close the dialog without either selecting an item or canceling the script.

If no data value is selected (i.e., either the “Continue” button has been used, or the “Cancel” button has been used and CANCEL_HALT has been set to OFF), all data values corresponding to column names of the table that was displayed will be undefined, even if they were defined before the table was displayed.

See Example 8 for an illustration of the use of this metacommand, and Example 17 for an illustration of the use of the CONTINUE keyword.

RESET COUNTER

RESET COUNTER <counter_no>

Resets the specified counter variable so that the next reference to it will return a value of 1.

RESET COUNTERS

RESET COUNTERS

Resets all counter variables so that the next reference to any of them will return a value of 1.

RM_FILE

RM_FILE <file_name>

Deletes the specified file. Although execsql is not intended to be a file management tool, there are occasions when deletion of a file from within the script may be a useful workflow step—for example, if header information was written to an output file in anticipation of subsequent addition of error messages, but no errors were later encountered. Whereas the EXPORT metacommand will automatically overwrite an existing file if it exists, the WRITE metacommand always appends text to an existing file. The RM_FILE metacommand is therefore useful to remove an existing output text file that you wish to rewrite. The RM_FILE metacommand is also useful when you want to create a new SQLite database (using the NEW keyword) and want to ensure that the SQLite file does not already exist, to avoid the error that the CONNECT metacommand would otherwise raise.

If the file that is to be deleted does not actually exist, no error will occur.

RM_SUB

RM_SUB <match_string>

Deletes the specified user-created subsitution variable.

SELECT_SUB

SELECT_SUB <table_or_view>

Assigns data values from the first row of the specified table or view to a set of substitution variables corresponding to the column names, but prefixed with the “@” character. This prefix prevents any conflict between these automatically-assigned substitution variables and any others that you may have created with the SUB command or by any other means—except for the PROMPT SELECT_SUB metacommand, which uses the same prefix for substitution variables.

Null values in the selected data row will be represented by substitution variables with zero-length string values.

If the selected table or view contains no data, an error will occur, and script processing will be halted.

SET COUNTER

SET COUNTER <counter_no> TO <value>

Assigns the specified value to the counter. The next time that this counter is referenced, the value returned will be one larger than the value to which it is set by this metacommand.

SUB

SUB <match_string> <replacement_string>

Defines a substitution variable (the <match_string>) which, if matched on any line of the script, will be replaced by the specified replacement string. Replacement will occur on all following lines of the script (and all included scripts) before the lines are evaluated in any other way. Every occurrence of the <match_string>, when immediately preceded and followed by two exclamation points (“!!”), will be replaced by the replacement string. Substitutions are processed in the order in which they are defined.

SUB_ADD

SUB_ADD <match_string> <numeric_value>

Adds the specified numeric value to the value of the substitution variable, which should also be numeric.

Although SQL can be used to perform computations with numeric substitution variables (see Example 16), incrementing a variable is a commonly useful operation, and this metacommand allows it to be carried out without a round trip to the database.

If the substitution variable is not numeric, it will be redefined with a suffix of “+” and the numeric value.

SUB_APPEND

SUB_APPEND <match_string> <new_line>

Appends the given new_line text to the specified substitution variable (the match_string), separated with a newline character. This metacommand allows the creation of multi-line messages that can be used with the WRITE, PROMPT, and EMAIL metacommands.

This metacommand may change the order in which substitution variables are defined. The substitution variable specified in this metacommand will become the most recently defined substitution variable.

SUB_DECRYPT

SUB_DECRYPT <sub_var_name> <encrypted_text>

Creates a substitution variable containing an unencrypted version of the given encrypted_text. The encrypted_text must have been produced by the SUB_ENCRYPT metacommand. The encryption method used is not of cryptographic quality, and is primarily intended to provide simple obfuscation of email passwords or other sensitive information that may appear in configuration or script files.

SUB_EMPTY

SUB_EMPTY <match_string>

Defines a substitution variable containing an empty string.

SUB_ENCRYPT

SUB_ENCRYPT <sub_var_name> <plaintext>

Creates a substitution variable containing an encrypted version of the given plaintext. The encryption method used is not of cryptographic quality, and is primarily intended to provide simple obfuscation of email passwords or other sensitive information that may appear in configuration or script files.

SUB_TEMPFILE

SUB_TEMPFILE <match_string>

Assigns a unique temporary file name to the specified substitution variable (the match_string). The location of (path to) this temporary file is operating-system dependent; the file may not be located in the current working directory. The temporary file will not be created, opened, or used directly by execsql. All temporary files will automatically be deleted when execsql exits (however, a temporary file will not be deleted if it is in use by another process, and then may persist until manually removed). See Example 12 and Example 13 for llustrations of the use of temporary files.

SUBDATA

SUBDATA <match_string> <table_or_view_name>

Defines a substitution variable which, if matched on any line of the script, will be replaced by the data value in the first column of the first row of the specified table or view.

If there are no rows in the specified data source, the substitution variable will be undefined. This case can be evaluated with the SUB_DEFINED conditional.

Warning

A backward-incompatible change to SUBDATA was made in version 1.24.8.0 (2018-06-03). Previously, if there were no rows in the data source, execsql would halt with an error message.

SYSTEM_CMD

SYSTEM_CMD ( <operating system command line> )

The specified command line will be passed to the operating system to execute. This command is executed by the system, not by the command shell, so commands that are processed by the shell cannot be used. Internal commands for the Bash shell are listed here: https://www.gnu.org/software/bash/manual/html_node/Bash-Builtins.html (some Bash internal commands also have analogues as external programs), and internal commands for the Windows command interpreter are listed here: https://ss64.com/nt/syntax-internal.html.

Because commands are not processed by the shell, the system path is not searched for executable commands, so full path names must be used for executable files. Execution of the SQL script does not ordinarily continue until the operating system command has completed–however, note that if this command invokes an editor or other software that is already open, the execsql script will continue immediately.

On non-POSIX operating systems (specifically, Windows), any backslashes in the command line will be doubled before the command line is passed to the operating system. Because backslashes are used as directory separators in Windows paths, this automatic alteration of the command line is meant to eliminate the need to double backslashes in path specifications on Windows.

The exit status of the command that is invoked will be stored in the system variable $SYSTEM_CMD_EXIT_STATUS.

TIMER

TIMER ON|OFF

Starts or stops an internal timer. The value of the timer can be obtained with the “$timer” system variable. Elapsed time is reported in real-time seconds (not CPU time) to at least the nearest millisecond.

USE

USE <alias_name>

Causes all subsequent SQL statements and metacommands to be applied to the database identified by the given alias name. The alias name must have been previously established by the CONNECT metacommand, or the alias name “initial” can be used to refer to the database that is used when execsql starts script processing.

WAIT_UNTIL

WAIT_UNTIL <Boolean expression> HALT|CONTINUE AFTER <n> SECONDS

Suspends execution of the SQL script until the specified Boolean expression becomes true. The Boolean expressions that can be used with the WAIT_UNTIL metacommand are the same as those that can be used with the IF metacommands.

The condition is tested once per second for up to <n> seconds. If the condition has not become true by that time, then the script either halts or continues, as specified.

The WAIT_UNTIL metacommand can be used to insert a pause in a script without issuing a message, as the PAUSE metacommand does:

WAIT UNTIL EQUALS("1","0") CONTINUE AFTER 1 SECONDS

WRITE

WRITE "<text>" [[TEE] TO <output>]

Writes the specified text to the console or a file, or both. The text to be written must be enclosed in double quotes. If no output filename is specified, the text will be written to the terminal. If the “TEE” keyword is included, the text will be written to both the console and the specified file. If the “-v3” command-line option is used, or a GUI console is opened explicitly, the text will be written to the GUI console. If the text is written to a file, it will always be appended to any existing file of the given name. The output file directory will be created if it does not exist and the make_export_dirs configuration setting is set to “Yes”.

The text to be written may be enclosed in double quotes (as shown above), in single quotes, or in matching square brackets.

WRITE CREATE_TABLE

For data in a delimited text file:

WRITE CREATE_TABLE <table_name> FROM <file_name>
    [WITH QUOTE <quote_char> DELIMITER <delim_char>]
    [SKIP <lines>]
    [COMMENT "<comment_text>"] [TO <output>]

For data in an OpenDocument spreadsheet:

WRITE CREATE_TABLE <table_name> FROM <file_name>
    SHEET <sheet_name> [SKIP <rows>] [COMMENT "<comment_text>"]
    [TO <output>]

For data in an Excel spreadsheet:

WRITE CREATE_TABLE <table_name> FROM EXCEL <file_name>
    SHEET <sheet_name> [COMMENT "<comment_text>"] [TO <output>]

For data in a table of an aliased database:

WRITE CREATE_TABLE <table_name> FROM <table_name>
    IN <alias> [COMMENT "<comment_text>"] [TO <output>]

Generates the CREATE TABLE statement that would be executed prior to importing data from the specified file or worksheet, or copying data from the specified aliased database, if the NEW or REPLACEMENT keyword were used with the IMPORT or COPY metacommand. The comment text, if provided, will be written as a SQL comment preceding the CREATE TABLE statement. The comment text must be double-quoted; table, file, and worksheet names can be quoted or unquoted. If no output filename is specified, the text will be written to the console. Text will always be appended to any existing file of the given name. The output file directory will be created if it does not exist and the make_export_dirs configuration setting is set to “Yes”. See Example 12 for an illustration of the use of this metacommand.

The SKIP key phrase specifies the number of lines at the beginning of the file to discard before evaluating the remainder of the file as a data table.

The WRITE CREATE_TABLE command may report an error when used with ODS files that have been created or edited using Excel—see the description of the IMPORT metacommand for additional information about this problem.

WRITE SCRIPT

WRITE SCRIPT <script_name> [[APPEND] TO <output_file>]

Displays the text of the specified script, which must have been defined with the BEGIN SCRIPT metacommand. The lines of the specified script will be written either to the console or to the specified file. The output file directory will be created if it does not exist and the make_export_dirs configuration setting is set to “Yes”.