Application icon

Open Database for Querying

This function is used to open a database so it can be used for querying.

The path may contain any of the escape sequences described in Escape Sequences. At runtime, the path to a database must be absolute (/) or relative to the home folder (~/) and must have a filename extension of .csv, .tsv or .tab.

If the specified path does not exist or if any errors occur while opening the file, action processing or batch processing will be terminated.

Every database opened for querying typically has one column which contains a unique value for each item (row). This is called the key column. There are a number of choices as to how the key column is specified:

Yate Track ID
The first column containing a Yate Track ID becomes the key column.
Yate Album ID
The first column containing a Yate Album ID becomes the key column.
In file
This mode extracts the key column information from the database sidecar file. This means that you must have manually assigned the key column in the Database Viewer.
Keyless
This is a special mode where there is no key column. This limits the way some of the associated query database statements operate.
Column Number
You supply the number of the column to be used as the key column. (0, 1, 2, ...)
Column Name
You supply the name of the column (case sensitive) to be used as the key column. The column specification may contain any of the escape sequences described in Escape Sequences.

Leading and trailing spaces are always ignored in keys. Further, you can specify that keys are to be treated as case insensitive. If you have case insensitivity set and you have two items with the same key name differing only in alphabetic case, the uniqueness requirement will be broken.

If you wish to make a determination at runtime as to which column should be key, you can always read the database sidecar file before opening the database. You can do this via the Read Database Sidecar File statement.

When a query database is opened, named variables are automatically created. You specify a prefix for the names. This prefix is the preferred method of referencing open query databases in other statements. If the prefix is empty, Query Database is assumed. A prefix, empty or not, must be unique. The prefix field may contain any of the escape sequences described in Escape Sequences.The following named variables are created:

prefix-Names
A list containing the column names (headers).
prefix-Encoding
A list containing information to determine the field type associated with a column. See the Read Database Sidecar File statement for a description of the format.
prefix-Qualifiers
A list containing information to qualify multi item field types associated with a column. See the Read Database Sidecar File statement for a description of the format.
prefix-Key Column
The column number of the key field. The value will be -1 if the Keyless option was chosen.
prefix-Last Row
A properly formatted value which can be used as a parameter to statements requiring a key parameter. The last item in the database will be referenced.
prefix-Path
The unescaped and possibly expanded path used to open the file. Do not change modify this value.
prefix-Row Count
The number of rows (items) in the database.

An attempt to open a query database which is already open will be ignored. In this case the action test state will be set to false. If the database is successfully opened, the action test state will be set to true. If the database is not opened because it is already open, a named variable with the supplied prefix (or Query Database if the prefix was empty), will be set to the prefix of the pre-opened database. eg. if the failed open had a prefix of DB2 and the previously opened database had a prefix of DB1, named variable DB2 will have a value of DB1.

Query databases are automatically closed when either the Batch Processor terminates or if not batch processing, when action processing terminates. Modifications are automatically saved. If you wish to discard modifications, you can use the Close Query Database statement.

Click here for information on identifying an open query database.