The database query module (accessible from the admin menu) allows you to perform SQL queries against all the data tables of the current database.

In almost all cases, you will want to run queries against the Shared database, the default, and will not have to change the Database setting.

➢The Local database contains mainly temporary files, used to communicate with QuarkXPress or InDesign, and only technical support will have any use for queries against that database.

The Query History and Query Builder buttons are discussed below.

SQL Query

You can use the SQL editor at the top of the dialog to enter an SQL query.

➢Every time an SQL query is run successfully, that query text is saved in a history list, to which you can return, at any time, using the Query History button shown in the image on the right. Note that this history list is saved even after you close the Database Query Dialog and Q++Studio (ie. it will be available again the next time you return).

In addition, the SQL editor displays queries using syntax highlighting, and, additionally, as you type, the list of all the tables, and the fields contained by each table, of the current database, can be accessed.

Tables: after a double-quote, the list of available tables appears, and as you start to type that list is reduced based on the characters already typed. In the example on the right, after typing "T, only the tables whose name begins with T are displayed.
Fields: if you type a dot after a table name, then the list of the fields of that table appears, and as you start to type that list is reduced based on the characters already typed. In the example on the right, after typing P, only the fields whose name begins with P are displayed.

You can also use the Query Builder button to launch the visual query builder and create SQL scripts visually.

Regular Expressions

You can use the syntax aFieldName SIMILAR TO RegularExpression to check for regular expression matching.

The regular expressions are evaluated using the PCRE library. A quick overview of the supported syntax can be found here  and the full specification of valid patterns can be found here. Regular expressions can also be used in queries that use the SUBSTRING and OVERLAY functions.

Results

Once your SQL query is ready, click on the Run SQL button to run the current SQL query.

The results of the query are then displayed in the advanced data grid, located in the bottom half of the dialog, as shown in the example at the top of this page, with the number of matching results displayed at the bottom of the grid of results.

➢Using that advanced data grid, you can then further search in the results set, or filter and/or group the results.

When you are done, if the results set contains many entries, you can use the Export button to export the displayed results set to Excel.

Syntax Errors

If the instructions in the SQL editor contain a syntax error, then an error message appears indication the line number, and position on that line, of the error, as shown in the image above.

See also: the database query history dialog and the visual query builder.

Topic 182600, last updated on 19-May-2023