Configuration Files

In addition to, or as an alternative to, command-line options and arguments, configuration files can be used to specify most of the same information, plus some additional information. Most of the command-line options and arguments can be specified in a configuration file, with the exception of the script name. The script name must always be specified on the command line.

execsql will read information from up to four configuration files in different locations, if they are present. The four locations are:

  • The system-wide application data directory. This is /etc on Linux, and %APPDATA% on Windows.
  • The user-specific configuration directory. This is a directory named .config under the user’s home directory on both Linux and Windows.
  • The directory where the script file is located.
  • The directory from which execsql was started.

The name of the configuration file, in all locations, is execsql.conf.

Configuration data is read from these files in the order listed above. Information in later files may augment or replace information in earlier files. Options and arguments specified on the command line will further augment or override information specified in the configuration files.

Configuration files use the INI file format. Section names are case sensitive and must be all in lowercase. Property names are not case sensitive. Property values are read as-is and may or may not be case sensitive, depending on their use. Comments can be included in configuration files; each comment line must start with the “#” character.

The section and property names that may be used in a configuration file are listed below.

Section connect

db_type
The type of database. This is equivalent to the “-t” command-line option, and the same list of single-character codes are the only valid property values.
server
The database server name. This is equivalent to the second command-line argument for client-server databases.
db
The database name. This is equivalent to the third command-line argument for client-server databases
db_file
The name of the database file. This is equivalent to the second command-line argument for file-based databases.
port
The port number for the client-server database. This is equivalent to the “-p” command-line option.
username
The name of the database user, for client-server databases. This is equivalent to the “-u” command-line option.
access_username
The name of the database user, for MS-Access databases only. When using MS-Access, a password will be prompted for only if this configuration option is set or the “-u” command-line option is used, regardless of the setting of the username configuration parameter.
password_prompt
Indicates whether or not execsql should prompt for the user’s password. The property value should be either “Yes” or “No”. This is equivalent to the “-w” command-line option.
new_db
Indicates whether or not execsql should create a new PostgreSQL or SQLite database to connect to.

Section encoding

database
The database encoding to use. This is equivalent to the “-e” command-line option.
script
The script encoding to use. This is equivalent to the “-f” command-line option.
import
Character encoding for data imported with the IMPORT metacommand. This is equivalent to the “-i” command-line option.
output
Character encoding for data exported with the EXPORT metacommand. This is equivalent to the “-h” command-line option.
error_response

How to handle conditions where input or output files have incompatible encodings. If not specified, incompatible encodings will cause an error to occur, and execsql will halt. The property values you can use for this setting are:

  • “ignore”: The inconvertible character will be omitted.
  • “replace”: The inconvertible character will be replaced with a question mark.
  • “xmlcharrefreplace”: The inconvertible character will be replaced with the equivalent HTML entity.
  • “backslashreplace”: The inconvertible character will be replaced with an escape sequence consisting of decimal digits, preceded by a backslash.

Section input

access_use_numeric
Whether or not to translate decimal (numeric) data types to double precision when the IMPORT or COPY metacommands construct a CREATE TABLE statement for MS-Access. This property value should be either “Yes” or “No.” The default value is “No”.
boolean_int
Whether or not to consider integer values of 0 and 1 as Booleans when scanning data during import or copying. The property value should be either “Yes” or “No”. The default value is “Yes”. By default, if a data column contains only values of 0 and 1, it will be considered 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.
boolean_words
Whether or not to recognize only full words as Booleans. If this value is “No” (the default), then values of “Y”, “N”, “T”, and “F” will be recognized as Booleans. If this value is “Yes”, then only “Yes”, “No”, “True”, and “False” will be recognized as Booleans. This setting is independent of the boolean_int setting.
max_int
Establishes the maximum value that will be assigned an integer data type when the IMPORT or COPY metacommands create a new data 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 within a script using the MAX_INT metacommand.
empty_strings
Determines whether empty strings in the input are preserved or, alternatively, will be replaced by NULL. The property value should be either “Yes” or “No”. The default, “Yes”, indicates that empty strings are allowed. A value of “No” will cause all empty strings to be replaced by NULL. When this is set to “No”, a string value consisting of a sequence of zero or more space characters will be considered to be an empty string. There is no command-line option corresponding to this configuration parameter, but the metacommand EMPTY_STRINGS can also be used to change this configuration item.
import_only_common_columns
Determines whether the IMPORT metacommand will import data from a CSV file when the file has more data columns than the target table. The property value should be either “Yes” or “No”. The default, “No”, indicates that the target table must have all of the columns present in the CSV file; if the target table has fewer columns, an error will result. A property value of “Yes” will result in import of only the columns in common between the CSV file and the target table.
scan_lines
The number of lines of a data file to scan to determine the quoting character and delimiter character used. This is equivalent to the “-s” command-line option.
import_buffer
The size of the import buffer, in kilobytes, to use with the IMPORT metacommand. This is equivalent to the “-z” command-line option.

Section output

log_write_messages
Specifies whether output of the WRITE metacommand will also be written to execsql’s log file. The property value should be either “Yes” or “No”. This configuration property can also be controlled within a script with the LOG_WRITE_MESSAGES metacommand.
make_export_dirs
The output directories used in the EXPORT and WRITE metacommands will be automatically created if they do not exist (and the user has permission). The property value should be either “Yes” or “No”. This is equivalent to the “-d” command-line option.
css_file
The URI of a CSS file to be included in the header of an HTML file created with the EXPORT metacommand. If this is specified, it will replace the CSS styles that execsql would otherwise use.
css_style
A set of CSS style specifications to be included in the header of an HTML file created with the EXPORT metacommand. If this is specified, it will replace the CSS styles that execsql would otherwise use. Both css_file and css_style may be specified; if they are, they will be included in the header of the HTML file in that order.
template_processor
The name of the template processor that will be used with the EXPORT and EXPORT QUERY metacommands. The only valid values for this property are “jinja” and “airspeed”. If this property is not specified, the default template processor will be used.

Section interface

console_wait_when_done
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 property value is set to “Yes” (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 setting has the same effect as a CONSOLE WAIT_WHEN_DONE metacommand.
console_wait_when_error_halt
Controls the persistence of any console window at the completion of the script if an error occurs. If the property value is set to “Yes” (the default value is “No”), the console window will remain open until explicitly closed by the user after an error occurs. The message “Script error; close the console window to exit execsql.” will be displayed in the status bar. This setting has the same effect as a CONSOLE WAIT_WHEN_ERROR metacommand.
gui_level

The level of interaction with the user that should be carried out using GUI dialogs. The property value must be 0, 1, 2, or 3. 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.
  • 3: Additionally, open a GUI console when execsql starts.

Section email

host
The SMTP host name to be used to transmit email messages sent using the EMAIL metacommand. A host name must be specified to use the EMAIL metacommand.
port
The port number of the SMTP host to use. If this is omitted, port 25 will be used unless either the use_ssl or use_tls configuration properties is also specified, in which case ports 465 or 587 may be used.
username
The name of the user if the SMTP server requires login authentication.
password
An unencrypted password to be used if the SMTP server requires login authentication.
enc_password

An encrypted password to be used if the SMTP server required login authentication. The encrypted version of a password should be as is produced by the SUB_ENCRYPT metacommand. A suitably encrypted version of a password can be produced by running the script:

-- !x! prompt enter_sub pw password message "Enter a password to encrypt"
-- !x! sub_encrypt enc_pw !!pw!!
-- !x! write "The encrypted password is: !!enc_pw!!"

If both the password and enc_password configuration properties are used, the enc_password property will take precedence and will be used for SMTP authentication. Note that this is not a cryptographically secure encryption, merely an obfuscation of the password.

use_ssl
SSL/TLS encryption will be used from the initiation of the connection.
use_tls
SSL/TLS encryption will be used after the initial connection is made using unencrypted text.
email_format
Specifies whether the message will be sent as plain text or as HTML email. The only valid values for this property are “plain” and “html”. If not specified, emails will be sent in plain text.
message_css
A set of CSS rules to be applied to HTML email.

Section config

config_file
The full name or path to an additional configuration file to be read. If only a path is specified, the name of the configuration file should be execsql.conf. The configuration file specified will be read immediately following the configuration file in which it is named. No configuration file will be read more than once.

Section variables

There are no fixed properties for this section. All property names and their values that are specified in this section will be used to define substitution variables, just as if a series of SUB metacommands had been used at the beginning of the script.

Section include_required

This section lists additional script files that should be automatically included before the main script is run, without the use of any explicit INCLUDE metacommand in the main script.

Each property in this section should be an integer, and the property value should be a filename. The integers specify the order in which the files should be included. If any integer is listed more than once, only the last filename associated with that integer in this configuration section will be included. If any of the specified files does not exist, an error will occur and execsql will stop. Each file may be included only once.

Files specified in this section will be included before any files specified in the include_optional section. This priority ordering applies to lists of required and optional files specified in all configuration files that are read.

The order in which these files are imported is also affected by the order in which multiple configuration files (if they exist) are read.

Section include_optional

This section lists additional script files that will, if they exist, be automatically included before the main script is run, without the use of any explicit INCLUDE metacommand in the main script.

Each property in this section should be an integer, and the property value should be a filename. The integers specify the order in which the files should be included. If any integer is listed more than once, only the last filename associated with that integer in this configuration section will be included. If any of the specified files does not exist, it will be ignored. Each file may be included only once.

Files specified in this section will be included after any files specified in the include_required section. This priority ordering applies to lists of required and optional files specified in all configuration files that are read.

The order in which these files are imported is also affected by the order in which multiple configuration files (if they exist) are read.