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 butte_data_2012.csv


This metacommand (IMPORT) imports data from a text file or spreadsheet into a database table, and can automatically create the table with appropriate data types.

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, LOOP, or USE, or that use substitution variables in SQL statements, are not likely to run properly with other script processors.

Metacommands can appear anywhere in a SQL script except embedded inside a SQL statement.

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 "<question>" SUB <match_string>


Prompts the user to provide 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.

Double quotes (as shown above), apostrophes, or square brackets can be used to delimit the text of the question.

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

AUTOCOMMIT¶

AUTOCOMMIT OFF

AUTOCOMMIT ON [WITH COMMIT|ROLLBACK]


By default, execsql automatically and immediately commits each SQL statement. If AUTOCOMMIT is set to OFF, SQL commands will instead be sent to the database but will not be committed.

If AUTOCOMMIT is set to ON without using the WITH clause, the next SQL statement will be automatically committed, and that commit will also commit all SQL statements that were issued while AUTOCOMMIT was set to OFF. Alternatively, if the WITH COMMIT clause is used when AUTOCOMMIT is turned back ON, all previously issued SQL statements will be committed immediately.

If the WITH ROLLBACK clause is used when AUTOCOMMIT is turned back on, all SQL statements issued since AUTOCOMMIT was turned OFF will be rolled back.

SQL statements that were issued while AUTOCOMMIT is OFF can also be committed using a SQL “COMMIT;” statement. This approach should be avoided with SQL Server, however, because execsql connects with SQL Server using ODBC, and Microsoft warns that SQL statements to manage transactions should not be used “because this can cause indeterminate behavior in the driver”. For example, two “COMMIT;” statements may be needed the first time that SQL statements are committed this way.

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 (except when the NEW or REPLACMENT clauses are used with Firebird; in those cases the ‘create table’ statement that execsql generates and runs will be committed). The SQL statements generated by the IMPORT and COPY metacommands are sent to the database, however. Therefore the AUTOCOMMIT metacommand is recommended when explicit transaction control is to be applied to the IMPORT and COPY metacommands.

BEGIN BATCH and END BATCH¶

BEGIN BATCH

END BATCH

ROLLBACK [BATCH]


The BATCH commands provide transaction control at the script level, as an alternative to using the AUTOCOMMIT metacommands (and possibly the DBMS’s own transaction commands). By default, execsql automatically and immediately commits each SQL statement. 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 (e.g., sqlcmd, though the functionality does not exactly correspond).

BEGIN BATCH marks the beginning of a set of SQL statements to be executed in a single operation, and in effect turns off execsql’s autocommit behavior. END BATCH marks the end of that set of statements and sends a commit statement to the database. ROLLBACK BATCH sends a rollback command to the database to revert the action of all previous SQL statements 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, unless AUTOCOMMIT is OFF, so if IMPORT or COPY metacommands are used inside a batch, any preceding SQL statements in the batch will also be committed unless AUTOCOMMIT is OFF.

When the END BATCH metacommand is processed by execsql, a commit command is 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–note, however, that the commit statements are issued sequentially, and if a later commit statement fails, this will not roll back changes to databases for which the commit has already succeeded.

The BEGIN/END BATCH metacommands can be nested. However, the inner END BATCH metacommand will ordinarily commit all changes to the databases that have been used, which may include databases used in the outer batch as well. (This behavior may be DBMS-specific, and also depend on whether any additional explicit transaction control is being used.) 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 batch. Thus, although the BATCH commands can be nested, database transactions may not be, depending on the DBMS in use. 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 analogous to the “GO” command of the T-SQL language used with 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>

BEGIN SCRIPT <script_name> WITH PARAMETERS (param1[, param2[,..]])

END SCRIPT [script_name]


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.

If the WITH PARAMETERS clause is used, the parameter names specified must be assigned values within a WITH ARGUMENTS clause of any EXECUTE SCRIPT metacommand that runs this script.

The “WITH” and “PARAMETERS” keywords are both optional.

If a script name is provided with the END SCRIPT metacommand, it must match the name used in the corresponding BEGIN SCRIPT metacommand. If it does not, execsql will halt with an error message.

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

BEGIN SQL and END SQL¶

BEGIN SQL

END SQL


The BEGIN SQL and END SQL metacommands define a block of lines in the script file that will be treated as a single SQL statement. Within the block of lines defined by these metacommands, a semicolon at the end of the line will not be treated as the end of a SQL statement.

The primary intended use case for these metacommands is to bracket procedure and function definitions. A function definition may contain multiple SQL statements, each of which is ended by a semicolon, but which should all be sent to the DBMS as a single statement, not as a series of invididual SQL statements.

The BEGIN SQL and END SQL metacommands are an alternative to the use of line continuation characters.

Metacommands that appear within a BEGIN/END SQL block will be treated as comments and will be ignored: they will not be executed when the SQL statement is run, and no error message will be produced.

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. The DIALOG_CANCELED conditional can be used to determine whether a dialog has been canceled. 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 “Yes”, “No”, “On”, or “Off”. 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” or “Off”, 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 “Yes”, “No”, “On”, or “Off”. execsql’s default behavior is to recognize values of “Y”, “N”, “T”, and “F” as Booleans. By setting BOOLEAN_WORDS to “Yes” or “On”, then only “Yes”, “No”, “True”, and “False” will be recognized as Booleans.

CONFIG CLEAN_COLUMN_HEADERS YES|NO


If set to Yes (the default is No), non-alphanumeric characters in column headers of IMPORTed data will replaced with an underscore character, and the column header will be prefixed with an underscore character if it starts with a digit. This may eliminate characters that are illegal for the DBMS in use, or eliminate the need for column names to be double-quoted.

This setting is also applied to the conversion of spreadsheet names to table names when multiple worksheets are IMPORTed.

CONFIG CONSOLE WAIT_WHEN_DONE YES|NO


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 “Yes” or “On” (the default value is “No”), the console window will remain open until explicitly closed by the user. The message “Script complete; close the console window to exit execsql.” will be displayed in the status bar. This 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 YES|NO  Controls the persistence of any console window at the completion of the script if an error occurs. If the value is set to “Yes” or “On” (the default value is “No”), 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 CREATE_COLUMN_HEADERS YES|NO


Whether or not to create column headers if they are missing from an input file. The default value is “No”. If this is set to “Yes”, missing column headers will be created as “Col” followed by the column number. This metacommand has the same action as the create_column_headers configuration setting.

CONFIG DAO_FLUSH_DELAY_SECS <seconds>


Specifies the number of seconds that execsql should wait between the time that a query is created in Access (which uses DAO) and the time that the next statement is executed using ODBC. This value must be greater than or equal to 5.0. This is equivalent to the dao_flush_delay_secs configuration setting.

CONFIG DEDUP_COLUMN_HEADERS YES|NO


Controls whether or not to make repeated column headers unique by appending an underscore and the column number. Evaluation of the equivalence of column headers is case-insensitive.

CONFIG EMPTY_ROWS YES|NO


Controls whether empty rows are allowed in data that is saved using either the IMPORT or COPY metacommands. The default is to allow empty rows. A metacommand of CONFIG EMPTY_ROWS NO will cause all empty rows to be omitted. A row containing only empty strings is not considered to be empty unless the CONFIG EMPTY_STRINGS configuration setting is also set to NO.

CONFIG EMPTY_STRINGS YES|NO


Controls whether empty (zero-length) 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 EXPORT_ROW_BUFFER <n>


Specifies the number of data rows that will be read from the database at one time and buffered when data are exported using the EXPORT metacommand. The default value is 1000 rows. Larger values will lead to faster exports for large data sets, up to a point, and with a diminishing rate of return. Larger values will also require more memory, and excessively large values could result in a memory error.

CONFIG FOLD_COLUMN_HEADERS NO|LOWER|UPPER


Specifies whether or not to fold (convert) the case of all column headers to lowercase or uppercase, or to leave them unchanged when data are IMPORTed. Valid values are “No” (the default), “Lower”, and “Upper”. Case does not matter in the specification.

This setting is also applied to the conversion of spreadsheet names to table names when multiple worksheets are IMPORTed.

CONFIG GUI_LEVEL <n>


The level of interaction with the user that should be carried out using GUI dialogs. The numeric value n must be 0, 1, or 2. The meanings of these values are:

• 0: Do not use any optional GUI dialogs.

• 1: Use GUI dialogs for password prompts and for the PAUSE metacommand.

• 2: Also use a GUI dialog if a message is included with the HALT metacommand, and prompt for the initial database to use if no database connection parameters are specified in a configuration file or on the command line.

This is equivalent to the gui_level configuration setting except that the corresponding configuration setting also allows a value of 3.

CONFIG HDF5_TEXT_LEN <n>


The width of the column to be used in an HDF5 export file when the data have a ‘text’ data type.

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 IMPORT_ROW_BUFFER <n>


Specifies the number of data rows that will be read from an input data source at one time and buffered when data are imported using the IMPORT metacommand and when a DBMS-specific fast import routine is not used. The default value is 1000 rows. Different buffer sizes may lead to faster imports, depending on the DBMS and data source. Larger values will require more memory, and excessively large values could result in a memory error.

CONFIG LOG_DATAVARS YES|NO


Controls whether data variables that are created by the SELECT_SUB, PROMPT SELECT_SUB and PROMPT ACTION metacommands are written to execsql’s log file. By default, all data variable assignments are logged. The performance of scripts that make extensive use of these metacommands (e.g., Example 27) can be improved by changing this setting to ‘No’.

CONFIG LOG_WRITE_MESSAGES YES|NO


Controls whether output of the WRITE metacommand will also be written to execsql’s log file. When this is set to “Yes” or “On” (the default value is “No”), 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 and WRITE metacommands 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.

CONFIG ONLY_STRINGS YES|NO


Controls whether data imported with the IMPORT metacommand and the “NEW” or “REPLACEMENT” keywords will have their data types evaluated (the default) or whether all the data columns will be treated as text (character, character varying, or text). The default value is “No”; if this is set to “Yes”, data will be imported as text.

CONFIG QUOTE_ALL_TEXT YES|NO


Controls whether the EXPORT metacommand will automatically quote all text values that are written to a delimited text file. When this is set to “Yes” or “On” (the default is “No”), all text data values will be enclosed in the specified quote characters. This behavior can also be controlled with the quote_all_text configuration setting.

CONFIG REPLACE_NEWLINES YES|NO


Controls whether newline characters embedded in strings are replaced during IMPORT. Newline characters and any surrounding whitespace will be replaced with a single space.

CONFIG SCAN_LINES <n>


The number of lines of a data file to scan during IMPORT to determine the quoting character and delimiter character used. This is equivalent to the “-s” command-line option and the scan_lines configuration setting.

CONFIG TRIM_STRINGS YES|NO


Controls whether leading and trailing whitespace is removed from text values on IMPORT.

CONFIG WRITE_WARNINGS YES|NO


Controls whether warning messages are written to the console as well as to the log file. When this is set to “Yes” or “On” (the default is “No”), warning messages will be displayed on the console. This behavior can also be controlled with the write_warnings configuration setting.

CONFIG ZIP_BUFFER_MB <n>


The size of the internal buffer used when the EXPORT metacommand exports data to a zipfile, in Mb. The default value is 10. The buffer should be at least as large as the largest data row to be exported. This value typically has little effect on performance, and only affects memory usage. This is equivalent to the zip_buffer_mb configuration setting.

CONNECT¶

For PostgreSQL:

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

CONNECT USER TO POSTGRESQL(SERVER=<server_name>, DB=<database_name>
[, PORT=<port_number>] [, ENCODING=<encoding>]) 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]


For SQL Server:

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

CONNECT USER TO SQLSERVER(SERVER=<server_name>, DB=<database_name>
[, 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>]

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


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

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


For Firebird:

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

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


For Oracle:

CONNECT TO ORACLE(SERVER=<server_name>, DB=<service_name>
[, USER=<user>, NEED_PWD=TRUE|FALSE]  [, PORT=<port_number>]

CONNECT USER TO ORACLE(SERVER=<server_name>, DB=<service_name>
[, PORT=<port_number>] [, ENCODING=<encoding>]) AS <alias_name>


For a DSN:

CONNECT TO DSN(DSN=<DSN_name>,
[, USER=<user>, NEED_PWD=TRUE|FALSE] [,


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 CONNECT metacommands, without the “USER” keyword, for Postgres, MySQL/MariaDB, Access, Oracle, 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.

If the command form with the “USER” keyword is used, the user name that is used for the connection will be that of the current user, and the password used will be that which was provided when execsql last prompted for a password (e.g., when making the initial database connection). This also eliminates the need to include a password in the script, although its use is limited to cases where the current user and previously-entered password are to be used for a new database connection.

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.

If the “NEW” keyword is used with PostgreSQL or SQLite, a new database of the given name will 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.

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), 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 HEIGHT <lines>


Changes the height of any existing console window, and specifies the height of any console window that is subsequently created with CONSOLE ON. The resulting height will be approximately lines lines tall.

CONSOLE WIDTH <chars>


Changes the width of any existing console window, and specifies the width of any console window that is subsequently created with CONSOLE ON. The resulting width will be approximately chars characters wide.

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

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” 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 unless AUTOCOMMIT is OFF. Therefore, the COPY metacommand should be used with care within transactions that are managed with explicit SQL statements or with 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 unless AUTOCOMMIT is OFF. Therefore, the COPY metacommand should be used with care within transactions that are managed with explicit SQL statements or with or BATCHes.

DISCONNECT¶

DISCONNECT [[FROM] <alias>]


Closes the current database connection, or the connection associated with the alias alias, if specified. Aliases are defined by the CONNECT metacommand.

Attempting to disconnect from the initial database connection will result in an error, and execsql will halt.

Attempting to disconnect from a database that is still in use in a BATCH will result in an error, and execsql will halt.

Explicitly disconnecting from a database is not necessary before re-using that alias. The CONNECT metacommand will automatically disconnect from a database if its alias is re-used.

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 [IF EXISTS] <script_name>

EXECUTE SCRIPT [IF EXISTS] <script_name> WHILE (<conditional expression>)

EXECUTE SCRIPT [IF EXISTS] <script_name> UNTIL (<conditional expression>)

EXECUTE SCRIPT [IF EXISTS] <script_name>
WITH ARGUMENTS (param1=val1 [, param2=val2 [,...]])

EXECUTE SCRIPT [IF EXISTS] <script_name>
WITH ARGUMENTS (param1=val1 [, param2=val2 [,...]])
WHILE (<conditional_expression>)

EXECUTE SCRIPT [IF EXISTS] <script_name>
WITH ARGUMENTS (param1=val1 [, param2=val2 [,...]])
UNTIL (<conditional_expression>)


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

If the IF EXISTS clause is included, the script will be executed only if it has been defined.

If the WITH ARGUMENTS clause is included, the specified argument names and values will be available within the script as script-specific substitution variables, or argument variables. Each argument name must be prefixed with the hash character (“#”) when it is used as a substitution variable reference (i.e., within doubled exclamation points) within the script. No direct assignments can be made to argument variables with the SUB metacommand or any other metacommand; they are read-only, and only available within the script where they are used as arguments.

If the WITH PARAMETERS clause was used with the BEGIN SCRIPT metacommand that was used to define this script, the argument list must include all of those parameters. If it does not, execsql will issue an error message and halt. The argument list may also include parameter names and values that were not defined in the WITH PARAMETERS clause.

The “WITH” and “ARGUMENTS” keywords are both optional.

If the WHILE clause is included, the script will be executed repeatedly as long as the specified conditional expression is true. The conditional expression is tested before each execution of the script. Therefore, if the conditional expression is initially false, the script will not be executed at all.

If the UNTIL clause is included, the script will be executed repeatedly until the specified conditional expression becomes true. The conditional expression is tested after each execution of the script (similar to Pascal’s “repeat…until” loop construct), so the script will always be executed at least once.

Conditional expressions that can used with the WHILE and UNTIL clauses are the same as those that can be used with the IF metacommand.

When a WHILE or UNTIL clause is used, the conditional expression will be evaluated at two different times. First, the entire metacommand, including the conditional expression, will be evaluated and substitution variables replaced. Second, the conditional expression will be evaluated again during every iteration of the loop.

If the conditional expression includes tests that use substitution variables, the time of evaluation of those substitution variables should be considered, because deferred substitution may need to be used for some of them. For example, a script that is intended to be run 10 times, using a counter variable in the UNTIL clause, should use deferred substitution like this:

EXECUTE SCRIPT do_me_over UNTIL (EQUAL("!{$COUNTER_1}!", "10"))  If deferred substitution were not used, the value of$COUNTER_1 would be immediately substituted as a fixed value during the first evaluation of the metacommand, and, presuming that this value does not already equal 10, the loop would then run forever, never finishing.

Regular substitution variables can be used in conditional tests, however, if they are not defined at the time that the EXECUTE SCRIPT metacommand is run. For example, this will work:

BEGIN SCRIPT do_me_over
SUB loop_ctr !!COUNTER_1!! WRITE "Doing over." END SCRIPT do_me_over RM_SUB loop_ctr EXECUTE SCRIPT do_me_over UNTIL (EQUAL("!!loop_ctr!!", "10"))  Because the variable ‘loop_ctr’ is initially undefined (a ‘phantom variable’), it will not be substituted before the first iteration of the loop. The ‘loop_ctr’ variable comes into existence when it is first defined within the ‘do_me_over’ script, so thereafter, when the conditional expression is evaluated after the first time through the loop (and all subsequent times), the variable will be defined, and will then be substituted and evaluated as intended. Using phantom variables in this way should be done with care because, particularly for WHILE loops, The first evaluation of the EQUAL test will be performed using the literal string “!!loop_ctr!!” rather than the substituted value of that (still phantom) variable. The number of iterations of the loop may therefore be different than expected, depending on the test used. Additionally, when using phantom variables, if the write_warnings configuration setting is set to True, execsql will display a warning about an unsubstituted variable during the first evaluation of the metacommand. EXPORT¶ EXPORT <table_or_view> [TEE] [APPEND] TO <filename>|stdout [IN ZIPFILE <zipfilename>] AS <format> [DESCRIPTION "<description>"]  EXPORT <table_or_view> [TEE] [APPEND] TO <filename>|stdout [IN ZIPFILE <zipfilename>] 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 “APPEND” keyword has different meanings depending on whether the data file is being written into a zipfile: • When no zipfile is used, the data will be appended to any existing file with the specified filename. • When a zipfile is used, the data file will be added to any existing zipfile with the specified zipfile name. If the file is written into a zipfile, “stdout” may not be used as the output name. Some data formats can not be written directly into a zipfile, as noted below. When using Python versions lower than 3.3, data will be stored in the zipfile without compression. When using later versions of Python, bzip2 compression will be used. The EXPORT metacommand has two forms, as shown above, with different actions: • The first form will export the data in a variety of formats. The format to be used is determined by the format keyword. These formats and the types of output produced are described in the next section below. • The second form will use one of several different template processors with a template specification file. Template specifications can be designed to produce either tabular or non-tabular output. The template processors that can be used are described in the second section below. The first form is more convenient if any of the supported formats is suitable, and the second form allows more flexible customization of the output. Using Specific 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. FEATHER The Feather binary file format established by the Apache Arrow project. The “APPEND” and “DESCRIPTION” keywords are ignored when this format is used. Exporting data in this format requires that the entire data set be first converted to a pandas data frame in memory, so there is a system-specific limit to the size of the data set that can be exported in this format. The feather and pandas libraries must be installed to export data in the Feather format. Not all data types that may be present in a database can necessarily be exported to a Feather data file, so some data types, like timestamps, may have to be converted to character data before export. Data exported in feather format cannot be written into a zipfile. HDF5 Hierarchichal Data Format (v5). The HDF format is a binary format designed for large volumes of data. It is supported by the HDF Group. Data types supported for export to HDF5 are more limited than typical database data types, and include only strings, integers, floating-point numbers, and Boolean values. Date and time types are converted to strings. Data that are stored in a ‘text’ data type (i.e., with unspecified length) must have a length specified when exported; the default length is 1,000 characters; this is a configurable setting. Non-ASCII encodings are not supported. Each exported data table is placed in its own group under the root of the tree structure in the HDF5 file. Both the group and the table have the table (or view) name given in the EXPORT metacommand. If a descriptions is provided, it is used as the group description. The tables library must be installed to export data in HDF5 format. Data exported in HDF5 format cannot be written into a zipfile. 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. JSON_TS or JSON_TABLESCHEMA JSON Table Schema. The data themselves are not exported. Ordinarily this form would be used in conjunction with an additional export of the data to a CSV file. The column type descriptions in the output are derived by scanning the entire table to evaluate the data type of each column. If a “NOTYPE” keyword is included before the “DESCRIPTION” keyword, this scanning is not done, and no type information is included in the output file.: EXPORT <table_or_view> [TEE] [APPEND] TO <filename>|stdout AS JSON_TS [NOTYPE] [DESCRIPTION "<description>"]  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. Data exported in LaTeX format cannot be written into a zipfile. 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. Data exported in ODS format cannot be written into a zipfile. 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 itself. 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. XML Data are written as an Extensible Markup Language (XML) document. If the “DESCRIPTION” keyword is used, the description text will be included as an XML comment before the table 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 fromvessel.
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},

DBMS test¶

DBMS(<dbms_name>)




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 <conditional_expression> HALT|CONTINUE AFTER <n> SECONDS


Suspends execution of the SQL script until the specified conditional expression becomes true. The conditional 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 primary purpose of the WAIT_UNTIL metacommand is to allow synchronization with a separate process. Checks for table, view, or file existence, or number of rows in a table, are tests that are therefore likely to be used in the conditional expression.

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), or in single quotes, matching square brackets, backticks, tildes, or hash marks (#).

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>]
[ENCODING <encoding>] [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> [SKIP <rows>] ENCODING <encoding>]
[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 or worksheet 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”.

ZIP¶

ZIP <filename> [APPEND] TO ZIPFILE <zipfilename>
`

Adds the specified file to the zipfile. Wildcards may be used in the filename.

When data are EXPORTed into a zipfile, other information such as a copy of the data request, a custom logfile, or a data dictionary may be valuable to include with the zipped data. The ZIP metacommand allows addition of such additional documentation from within an execsql script.

If the APPEND keyword is not used, the zip file will be created, overwriting any previous file of the same name. If the APPEND keyword is used, the specified file(s) will be added to an existing zipfile, or a new zipfile created if it does not already exist.