Appendix A. SqlTool System PL Variables

As of SqlFile revision 6559

SqlTool System PL variables are the mechanism used to configure SqlTool behavior. You can list all set PL variables by running the SqlTool command * listvalues. If a SqlTool System variable is not shown, then it is unset (which is equivalent to non-null). But if a system variable is not set, that doesn't mean that the setting behavior will be unset, but rather that the default behavior will apply. For example, if you * listvalues and the variable *DSV_COL_DELIM is not listed, that doesn't mean that there will be no DSV column delimiter, but that the default DSV column delimiter will be used. The in-program help can be used to determine what the default behavior is. (In the case of *DSV_COL_DELIM, you can see the default behavior by running \x?.

Besides System PL variables, there are also user PL variables which have names beginning with a letter, and the special variables ? and NULL. NULL is completely equivalent to *NULL, which is explained below.

Where the table below reports that a variable is read only, for now that usually means only that you should treat is as read-only. Behavior will be unpredictable if you write to these. At some point in future I may add enforcement of this. (Contact me if this is important for you).

?	Last retrieved table cell result value. See the SqlTool chapter about `?`.
#	Row count from last retrieved result set. See the SqlTool chapter about `#`.
*ALL_QUOTED	Boolean. Whether to quote all values (excluding null values) in a \xq export. No effect on any command other than \xq.
*BOTTOM_HTMLFRAG_FILE	File path to HTML fragment file to close the HTML report or DSV import reject file.
*DSV_COL_DELIM	Literal string (which may contain character escapes). DSV or CSV output column delimiter literal. Run \x? to see default value and details.
*DSV_COL_SPLITTER	Regular expression. DSV or CSV input column delimiter regular expression. Run \x? to see default value and details.
*DSV_CONST_COLS	A list of `column_name = column_value\|...` settings. Specifies constant import values. Run \x? to see default value and details.
*DSV_RECORDS_PER_COMMIT	Integer. How often to commit upon DSV/CSV imports. Run \x? to see default value and details.
*DSV_REJECT_FILE	File path. Path to DSV file of rejects rejected upon CSV/DSV imports. Run \x? to see default value and details.
*DSV_REJECT_REPORT	File path. Path to HTML report about CSV/DSV import failures. Run \x? to see default value and details.
*DSV_ROW_DELIM	Literal string (which may contain character escapes). DSV or CSV output row delimiter literal. Run \x? to see default value and details.
*DSV_ROW_SPLITTER	Regular expression. DSV or CSV input row delimiter regular expression. Run \x? to see default value and details.
*DSV_SKIP_COLS	A list of column names to skip, like `column1\|column2` Specifies columns to omit from CSV or DSV importing or exporting. Run \x? to see default value and details.
*DSV_SKIP_PREFIX	Literal string (which may contain character escapes). Specifies comment delimiter character or string in DSV or CSV files. Run \x? to see default value and details.
*DSV_TRIM_ALL	Boolean. Trim leading and trailing white space from every cell in CSV or DSV file upon import. Run \x? to see default value and details.
*DSV_TARGET_FILE	File path. File where to export CSV or DSV to. Run \x? to see default value and details.
*DSV_TARGET_TABLE	Table name. Table where to import CSV or DSV to. Run \x? to see default value and details.
*HOST	Unqualified (short) hostname. I.e. host name without domain.
*HOSTNAME	Will be as reported by command 'hostname', which may be qualified or unqualified (by domain).
*IGNORE_BANG_STATUS	Boolean. If true, then if an external command executed by `\!` returns error (non-zero) status, SqlTool will not report or try to act on the error. (This will have no effect on what the external program may do).
*NULL	Null (i.e. always unset).
*NULL_REP_HTML	Literal string (which may contain character escapes). Same as `*NULL_REP_TOKEN`, but only applies to HTML reports.
*NULL_REP_TOKEN	Literal string (which may contain character escapes). String value to represent SQL nulls from VARCHAR columns and null (unset) PL variable values. Applies to what displays on screen and what gets written into export files.
*REVISION	Read only. Literal string.
*ROW	Read only. Literal string. Set only inside of `* forrow` loop bodies. If there is only a single column fetched, then this is equal to that cell of the current row, unless that value is null, in which case `ROW` will be the `NULL_REP_TOKEN` value. Otherwise it is these values for the columns joined together with the `DSV_COL_DELIM`.
*ROWS	Read only literal string for use with forrows, but inversely to `ROW` availability, `ROWS` is never available inside of a forrows, only after a forrows. (To use a previous ROWS value inside a forrows loop, you would have to assign it to a user PL variable). The value is just the contatenation of `ROW` for each row joined with `DSV_ROW_DELIM` delimiter. It is basically a simple DSV export for the query. Be aware that the \p command can't take an argument with line delimiters, so though you can use `ROWS` with the default `DSV_ROW_DELIM` value for exporting, comparing, and other purposes, to print it with \p you will need to change `*DSV_ROW_DELIM` so that it doesn't include a line break.
*SCRIPT	Full name of current script, which is usually an URL or the string `<stdin>`.
*SCRIPT_BASE	Base name of the current script. Same as *SCRIPT_FILE but with final filename suffix also removed.
*SCRIPT_FILE	Current script file name without directory or any protocol prefix.
*START_TIME	Read only. Literal string. Automatically set to a localized string presenting the date and time.
*TIMESTAMP	Read only. Literal string. Only usable if `*TIMESTAMP_FORMAT` has been set. Displays the date and/or time at which this variable is dereferenced.
*TIMESTAMP_FORMAT	Formatting string, as described below. Setting this variable enables the `*TIMESTAMP` read-only variable to be used. Set to a date and/or time format like `yyyy-MM-dd'T'HH:mm:ss.SSSZ`, as described at http://download.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html
*TOP_HTMLFRAG_FILE	File path. File path to HTML fragment file to open the HTML report or DSV import reject file.

	Appendix A. SqlTool System PL Variables
Chapter 4. Transfer Tool		Appendix B. HyperSQL File Links