SQL Script Extensions

The SQL commands that may be used to select data from a database can be augmented by a set of metacommands that provide features such as conditional execution and looping in a way that operates the same across all supported database management systems (DBMSs). These metacommands are a subset of those supported by execsql.py.

The SQL script extensions include substitution variables, which allow text replacement anywhere within SQL statements and metacommands.

The metacommands and system substitution variables that are available in mapdata.py do not include any that are related to import or export of data, or user interactions.

Additional information on metacommands and substitution variables is provided in the complete documentation for execsql.py.

Metacommands

The metacommands supported by mapdata.py are:

AUTOCOMMIT

Controls whether SQL statements are executed immediately (the default) or executed as a transaction.

BEGIN/END BATCH

Allows transaction control by batching SQL statements.

BEGIN/END SCRIPT

Defines a block of SQL statements and metacommands that may be executed repeatedly, optionally including parameters, and with local substitution variables.

BEGIN/END SQL

Defines a block of SQL statements that are executed as if they were a single statement.

BREAK

Exits from any loop, script block, or included script file.

ERROR_HALT

Controls whether an error in a SQL statement causes the script to halt immediately.

EXECUTE SCRIPT

Runs a set of SQL statements that were previously defined with the BEGIN/END SCRIPT metacommands.

EXTEND SCRIPT

Modifies an already-defined script by appending a SQL statement, metacommand, or another script.

IF

Allows conditional execution based on the existence of schemas, tables, and views, and other conditions.

INCLUDE

Reads and includes another script file into the sequence of SQL commands.

LOOP

Repeats a sequence of SQL statements and metacommands while, or until, a specified condition is true.

METACOMMAND_ERROR_HALT

Controls whether an error in a metacommand causes the script to halt immediately.

ON ERROR_HALT EXECUTE SCRIPT

Specifies a script (defined with the BEGIN/END SCRIPT metacommands) that will be executed after a script encounters an error that will lead it to halt.

RESET COUNTER(s)

Resets one or more $COUNTER_x system variables.

RM_SUB

Removes a user-defined substitution variable.

SELECT_SUB

Creates or re-defines substitution variables using values in the first row of a data table.

SET COUNTER

Sets a $COUNTER_x system variable to a specified value.

SUB

Creates or re-defines a substitution variable.

SUB_ADD

Adds a numeric value to a substition variable.

SUB_APPEND

Appends text to a substitution variable.

SUB_EMPTY

Defines or re-defines a substition variable to have no replacement string.

SUB_LOCAL

Defines or re-defines a local substitution variable.

SUBDATA

Defines or re-defines a substitution variable to have the value in the first column of the first row of a table or view.

WAIT_UNTIL

Suspends execution of the SQL script until a specified conditional expression becomes true.

Substitution Variables

Only a subset of the system substitution variables available in execsql.py are supported by mapdata.py. Data variables, argument variables, local variables, and environment variables are completely supported. The system variables that are available in mapdata.py are:

$AUTOCOMMIT_STATE

Whether or not SQL statements will be automatically committed.

$COUNTER_x

The value of auto-incrementing counters.

$CURRENT_DATABASE

The name of the database in use.

$CURRENT_DBMS

The name of the DBMS for the database in use.

$CURRENT_DIR

The path to the current system directory, without a trailing separator character.

$CURRENT_PATH

The path to the current system directory, witha trailing separator character.

$CURRENT_TIME

The current date and time.

$DATE_TAG

The current date, as YYYYMMDD.

$DATETIME_TAG

The current date and time, as YYYYMMDD_hhmm.

$ERROR_HALT_STATE

Whether or not script processing will be halted if an error occurs in as SQL statement.

$ERROR_MESSAGE

The text of the most recent error message; only available if the script does not halt on errors.

$LAST_ERROR

The text of the last SQL statement or metacommand that caused an error; only available if the script does not halt on errors.

$LAST_ROWCOUNT

The number of rows that were affected by the last INSERT, UPDATE, or SELECT statement. Not provided by all DMBS.

$LAST_SQL

The last SQL statement that ran without error.

$METACOMMAND_ERROR_HALT_STATE

Whether or not script processing will be halted if an error occurs in a metacommand.

$OS

The name of the operating system.

$PATHSEP

The path separator used by the operating system.

$RANDOM

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

$SCRIPT_LINE

The line number of the script being run.

$SCRIPT_START_TIME

The date and time at which script processing started.

$UUID

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