The Queries screen of Portail ZAPP (menu Administration › Instance and operations › Queries) stores read-only SQL queries against the instance database, so you can run them on demand and export their result. A saved query can also feed a list in a dynamic form or be launched by a scheduled job (see "Scheduling tasks").
The screen is reserved for Administrators.
The query list
For each query the list shows its No., its Name, its Description, the Stored procedure column (Yes when it is one) and the Authorized groups: the number of groups, or the Public badge when no group is selected. The Search… field looks in the name and the description.
On each row, the play button opens the run screen and the pencil opens the edit form.
Create and edit a query
New query asks for:
- No.: a number of at least 1000 (convention: 1000 and above), used to order the list;
- Name and Description;
- Code: a stable, unique identifier used by import and export between instances. Choose it once and for all;
- Stored procedure: tick it when the SQL field only contains the name of a procedure (
nameorschema.name, nothing else); - SQL: the text of the query;
- Authorized groups: the groups whose members can run the query. Empty = available to everyone.
To prevent mistakes, a query containing one of the words INSERT, UPDATE, DELETE, DROP, ALTER, EXEC, TRUNCATE, CREATE, GRANT or REVOKE is refused, even if the word only appears in a comment or in a quoted string. A code already in use is refused too. In these cases the form comes back without being saved.
This check is a safeguard, not full protection: only a read-only connection dedicated to queries, which your hosting provider can configure, really prevents any write. Without it, queries run with the application's rights.
Run a query and export the result
The run screen shows the Parameters on the left: one field for each name preceded by @ in the SQL (for example @start in WHERE created_at >= @start::date). Values are passed as text: convert them in the SQL when the column is not text. A parameter left empty means "no value" (NULL). A stored procedure runs without parameters.
Click Run. The result is shown on the right:
- in a grid when the Data grids module is installed;
- otherwise in a table, preceded by the number of rows and the run time.
An SQL error is shown instead of the result. After a run, CSV export and Excel export run the query again with the same parameters and download the full result.
An administrator who belongs to none of a query's authorized groups cannot run it. Each run is logged (who, duration, number of rows, any error).
Delete a query
On the edit screen, Delete deletes the query after confirmation, together with the log of its runs. Deletion is permanent: first check that no form list or scheduled task relies on it.
Good to know
- A Public query can be run by anyone who reaches a run point, in particular by the users of a dynamic form with a list based on it. Restrict it to groups as soon as it returns sensitive data.
- For a form list, the first column of the result is the stored value and the second the displayed label; only the first 500 values are offered.
- A scheduled job that launches a query does not go through the authorized-groups check.
See also
- "Managing groups": the groups to authorize.
- "Setting permissions": access to the module's screens.