Usage Notes

This section contains miscellaneous notes on execsql usage.

Required Arguments

If the program is run without any arguments it will print a help message on the terminal, similar to the syntax description.

At least one argument, the name of the script file to run, is required. This single argument can be used when the database connection information is specified in one or more configuration files.

SQL Statement Recognition and SQL Syntax

execsql recognizes a SQL statement as consisting of a sequence of non-comment lines that ends with a line ending with a semicolon. A backslash (“\”) at the end of a line is treated as a line continuation character. Backslashes do not need to be used for simple SQL statements, but must be used for procedure and function definitions, where there are semicolons within the body of the definition, and a semicolon appears at the end of lines for readability purposes. Backslashes may not be used as continuation characters for metacommands.

With the exception of the “CREATE TEMPORARY QUERY…” statement when used with MS-Access, the execsql program does not parse or interpret SQL syntax in any way.

SQL syntax used in the script must conform to that recognized by the DBMS engine in use. Because execsql can connect to several different DBMSs simultaneously, a single script can contain a mixture of different SQL syntaxes. To minimize this variation (and possible mistakes that could result), execsql metacommands provide some common features of DBMS-specific scripting languages (e.g., pgScript and T-SQL), and execsql turns on ANSI-compatible mode for SQL Server and MySQL when it connects to those databases.

Comments in SQL Scripts

Script files can contain single-line comments, which are identified by two dashes (“--“) at the start of a line. Script files can also contain multi-line comments, which begin on a line where the first characters are “/*” and end on a line where the last characters are “*/”.

execsql strips single-line and multi-line comments from the script file when compiling SQL statements to send to the DBMS. execsql does not strip comments that follow part of a SQL statement on the same line, such as:

select
    scramble(eggs)      -- Use custom aggregate function
from
    refrigerator natural join stove;

The DBMS in use must be able to recognize and ignore any such comments. If any such comment occurs on the last line of the SQL statement, following the semicolon, then execsql will not recognize the end of the SQL statement, and an error will result.

Metacommands

Metacommands are directives to execsql that can control script processing, import and export data, report status information, and perform other functions. Metacommands are embedded in single-line SQL comments. These metacommands are identified by the token “!x!” immediately following the SQL comment characters at the beginning of a line, i.e.:

-- !x! <metacommand>

The special commands that are available are described in the Metacommands section.

Autocommit

SQL statements are ordinarily automatically committed by execsql. Consequently, database transactions will not work as expected under default conditions. The AUTOCOMMIT and BATCH metacommands provide two different ways to alter execsql’s default autocommit behavior. Transactions will work as expected either within a batch or after autocommit has been turned off. One difference between these two approaches is that within transactions inside a batch, changes to data tables are not visible to metacommands such as PROMPT DISPLAY, whereas these data are visible within transactions that follow an AUTOCOMMIT OFF metacommand. This difference in data visibility affects what tests can be done to decide whether to commit or roll back a transaction.

Rollback on Exit

When execsql exits, or closes a database connection because the same alias will be used again in a CONNECT metacommand, a rollback command will be sent to the database immediately before each connection is closed. Therefore if, for example, a PROMPT DISPLAY metacommand is used within a transaction, and the user cancels the display, and thus the script, a rollback command will be sent to the database, thereby terminating the transaction. This prevents transactions from being left open and incomplete, which may cause problems in some circumstances.

Exit Status

If execsql finishes normally, without errors and without being halted either by script conditions or the user, the system exit status will be set to 0 (zero). If an error occurs that causes the script to halt, the exit status will be set to 1. If the user cancels script processing in response to any prompt, the exit status will be set to 2. If the script is halted with either the HALT or HALT DISPLAY metacommands, the system exit status will be set to 3 unless an alternate value is specified as part of the metacommand.

DSN Connections

When a DSN is used as a data source, execsql has no information about the features or SQL syntax used by the underlying DBMS. In the expectation that a DSN connection will most commonly be used for Access databases, a DSN connection will use Access’ syntax when issuing a CREATE TABLE statement in response to a COPY or IMPORT metacommand. However, a DSN connection does not (and cannot) use DAO to manage queries in a target Access database, so all data manipulations must be carried out using SQL statements. The EXECUTE metacommand uses the same approach for DSN connections as is used for SQL Server.

MS-Access-Specific Considerations

Temporary Queries

The syntax of the “CREATE TEMPORARY QUERY” DDL supported by execsql when used with an MS-Access database is:

CREATE [TEMP[ORARY]] QUERY|VIEW <query_name> AS <sql_command>

The “TEMPORARY” specification is optional: if it is included, the query will be deleted after the entire script has been executed, and if it is not, the query will remain defined in the database after the script completes. If a query of the same name is already defined in the Access database when the script runs, the existing query will be deleted before the new one is created—no check is performed to determine whether the new and old queries have the same definition, and no warning is issued by execsql that a query definition has been replaced. The keyword “VIEW” can be used in place of the keyword “QUERY”. This alternative provides compatibility with the “CREATE TEMPORARY VIEW” command in PostgreSQL, and minimizes the need to edit any scripts that are intended to be run against both Access and PostgreSQL databases.

Scripts for Microsoft Access that use temporary queries will result in those queries being created in the Access database, and then removed, every time the scripts are run. This will lead to a gradual increase in the size of the Access database file. If the script halts unexpectedly because of an error, the temporary queries will remain in the Access database. This may assist in debugging the error, but if the temporary queries are not created conditional on their non-existence, you may have to remove them manually before re-running the script.

Password-Protected Databases

The user name for password-protected Access databases is “Admin” by default (i.e., if no other user name was explicitly specified when the password was applied). To ensure that execsql prompts for a password for password-protected Access databases, a user name must be specified either on the command line with the “-u” option or in a configuration file with the access_username configuration item. When the user name in Access is “Admin”, any user name can be provided to execsql.

ODBC and DAO Connections

With Access databases, an ODBC connection is used for SELECT queries, to allow errors to be caught, and a DAO connection to the Jet engine is used when saved action queries (UPDATE, INSERT, DELETE) are created or modified. Because the Jet engine only flushes its buffers every five seconds, execsql will ensure that at least five seconds have passed between the last use of DAO and the execution of a SELECT statement via ODBC.

Boolean Columns

Boolean (Yes/No) columns in Access databases cannot contain NULL values. If you IMPORT boolean data into a column having Access’ boolean data type, any NULL values in the input data will be converted to False boolean values. This is a potentially serious data integrity issue. To help avoid this, when the NEW or REPLACEMENT keywords are used with the IMPORT or COPY metacommands, and execsql determines that the input file contains boolean data, execsql will create that column in Access with an integer data type rather than a boolean data type, and when adding data will convert non-integer True values to 1, and False values to 0.