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.
➢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.
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.
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.
an example of an SQL syntax message showing the location of the error
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.
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.
Topic 182600, last updated on 19-May-2023