Query Store

What Query Store does

Query Store is a platform action that runs SQL statements against the Data Stores of your workspace, as a single step in a process. A query may hold one statement or several.

• No setup. There is no connection string, no credential and no external database. The action works on the Data Stores that already live in your workspace.
• Reads and writes. Filter, join, group and total your data, or insert, update and delete rows in bulk.
• Point and click. Pick Data Stores and columns from a list instead of typing their names.
• Safe values. Values from your process reach the query as parameters, so they can't change what the query does.
• Guardrails. Statements that could damage your data or reach outside your workspace are refused with a clear message.

A process with a Query Store step. Click the step to open its properties; everything is set up under Configure Query.

Query Store, Data Store action or Execute Query?

A Query Store query behaves like Execute Query: it may hold several statements, it can use user variables, and it has the same outputs. What sets it apart is the guardrails, which exist because Data Stores run on PROCESIO's shared database server rather than on your own.

Before you start

• A workspace with at least one Data Store. Query Store works in a shared workspace and in your personal workspace. In a personal workspace it reaches only your own Data Stores. See Creating a Data Store.
• A process open in the Process Designer.

Step 1 — Add the action

In the Process Designer, type Query in the toolbox search. Query Store is at the top of the list. Drag it onto the canvas, or double-click it, then connect it between your steps.

Search the toolbox for "Query" and add Query Store to the canvas.

Query Store sits above the database actions. Execute Query, underneath it, is a different action: it talks to your own SQL Server.

Click the new step to open its properties panel. Give it a name that says what it does, for example Find customers in city, then click Configure Query.

Step 2 — Write the query

Configure Query opens an SQL editor with syntax highlighting.

A finished query. The highlighted chips are a Data Store and its columns; @city is a parameter.

Several statements in one query

A query may hold several statements, separated by ;. They run in order, one after the other, as they do in the Execute Query action. Use this to read from one Data Store, write to another and clean up a third in one step.

A ; at the end of the query is fine, and so is a comment after the last statement. A ; inside quoted text is part of the text, not a separator.

When a statement fails in the middle

If a statement fails, the statements before it have already run and their changes are kept. The failing statement and everything after it do not run, and the step fails. The error does not say which statement failed, so when a query has several writes, test it on a copy of the data first.

The same applies when the Time Out is reached and when two statements return rows: the query has already run, and the step fails afterwards.

One statement may return rows

Only one statement in a query may return rows. If two or more do, the step fails after the query has run (every statement ran and the changes are kept) with error code 2003 and the message:

Expected maximum one table but received 2 tables!

The number is the number of statements that returned rows. This is the same error, with the same code, that Execute Query gives for the same mistake. SELECT @name := … returns a row, so it counts as the statement that returns rows; SELECT … INTO @name does not.

Insert Data Stores and columns with Ctrl + I

You never type a Data Store's name. Instead:

1. Click in the editor where the table or column should go.
2. Press Ctrl + I (Cmd + I on Mac) to open the picker.
3. Type in the search box to filter, then click a Data Store to insert it as a table, or click one of its columns to insert that column.

Type SQL as usual. Press Ctrl + I, pick the Data Store, keep typing, press Ctrl + I again and pick a column.

The picker lists process variables at the top and your Data Stores, with their columns, underneath.

Each chip is linked to the Data Store's ID, not its name:

• Renaming is safe. Rename a Data Store or a column and the query keeps working; the chip shows the new name.
• Deleting shows up in red. If a Data Store or column used in the query is deleted, its chip turns red and reads Unknown. The step fails until you replace it.
• Any name works. Names with spaces or special characters work, because the name never goes into the SQL.

Two Data Stores in one query

When you join two Data Stores that have a column with the same name, give each one a short alias and put the alias in front of every column:

SELECT c.Name AS Customer, SUM(o.Amount) AS Revenue
FROM   DEMO_QS_Customers AS c
JOIN   DEMO_QS_Orders    AS o ON o.CustomerCode = c.CustomerCode
GROUP BY c.Name

(In the editor, each Data Store and column name above is a chip.)

Without the aliases the query is refused, because the database can't tell which CustomerCode you mean.

A sales report joining customers to orders, with AS c / AS o aliases.

Step 3 — Pass values in with parameters

To use a value from your process, such as a form field, an input or the output of an earlier step, write a parameter in the query and map it below the editor.

1. In the query, write @ followed by a name: WHERE City = @city.
2. Under Map the query parameter names to the values they are bound to, click + Add parameter (the first row is already there).
3. In the left field, type the parameter name and press Enter. The list suggests the names it finds in your query.
4. In the right field, either type a fixed value, or click + and pick a process variable.

The name goes on the left (confirm it with Enter). On the right, click + and pick the variable that supplies the value.

@city gets its value from the process variable City.

Why parameters matter: a parameter's value is always treated as data, never as part of the SQL. If someone types Cluj' OR '1'='1 into your form, the query looks for a city with exactly that name and finds nothing, instead of returning every row.

Parameter names: start with a letter or underscore, then letters, digits and underscores only, 64 characters at most, and each name once per query. The leading @ in the mapping is optional.

User variables

You can keep a value for a later statement in the same query in a user variable. Three forms work, as they do in Execute Query on MySQL:

Then use @name in any later statement of the same query. SET @a = 1, @b = 2 sets several at once, and := works in SET too. A variable lasts for that run of the step only.

Passing a list

A list variable, for example customer codes selected in a form, arrives in the query as a JSON array. Use one of these patterns:

Finding a row by Guid

LOWER(CONCAT_WS('-',
  HEX(REVERSE(SUBSTRING(Id, 1, 4))),
  HEX(REVERSE(SUBSTRING(Id, 5, 2))),
  HEX(REVERSE(SUBSTRING(Id, 7, 2))),
  HEX(SUBSTRING(Id, 9, 2)),
  HEX(SUBSTRING(Id, 11, 6))))

Step 4 — Time out and outputs

Result Rows and Total Count each write into a process variable.

Two details for Total Count:

• For an UPDATE, every row that matched the WHERE counts, whether or not its value changed.
• SELECT … INTO @name counts the rows it selected. SET @name = … counts nothing.

There is no row limit. A SELECT with no WHERE or LIMIT returns the whole table, so add a LIMIT when you only need a few rows.

Time Out with several statements

The Time Out applies to each statement that returns rows, not to the whole query, the same as Execute Query on a MySQL database. A query with two statements that each return rows can run for up to twice the Time Out. Statements that return no rows share one timer until the next statement that returns rows.

When the Time Out is reached, the step fails with "The query did not finish within the configured time limit." The statements that finished before it are kept. Whatever the query does, the step waits no longer than the Time Out plus 30 seconds; past that it fails with "The query did not finish within N seconds and was stopped. Narrow the query, or raise its Time Out."

Step 5 — Test the step on its own

You can run a Query Store step without running the whole process. Open the step, click the vertical Test Action tab on the left edge of the properties panel, then click Test action.

Test Action runs only this step. Test output shows the status and the value of each output.

Step 6 — Run the process and see the results

Click Save, Validate and Run. If the process has inputs, a dialog asks for them. Enter them and click Run.

Run, fill in the input, then open the instance. The results are on the Variables tab.

The process's inputs appear in the Run dialog.

When the run finishes, click Check instance (it opens in a new tab), or open the process's instances and choose History → Show details. On the Variables tab, the query's outputs are listed under Output.

Customers holds the rows as JSON; CustomerCount holds how many there were.

What you can write

A query holds one or more statements, each of one of these types:

UNION, subqueries and the usual MySQL functions work inside these statements.

Examples

In the editor, each Data Store and column name below is a chip inserted with Ctrl + I.

Filter with a parameter

SELECT CustomerCode, Name, City, Tier
FROM   DEMO_QS_Customers
WHERE  City = @city
ORDER BY Name

A report across two Data Stores

SELECT c.Name AS Customer, COUNT(*) AS Orders, SUM(o.Amount) AS Revenue
FROM   DEMO_QS_Customers AS c
JOIN   DEMO_QS_Orders    AS o ON o.CustomerCode = c.CustomerCode
WHERE  o.Status <> 'Cancelled'
GROUP BY c.Name
ORDER BY Revenue DESC

Update in bulk

UPDATE DEMO_QS_Orders
SET    Status = 'Shipped'
WHERE  CustomerCode = @customer AND Status = 'New'

Total Count tells you how many orders were shipped.

Several statements: clean up, then report

SET @cutoff = DATE_SUB(NOW(), INTERVAL 90 DAY);
DELETE FROM DEMO_QS_Orders WHERE Status = 'Cancelled' AND CreatedOn < @cutoff;
SELECT Status, COUNT(*) AS Orders FROM DEMO_QS_Orders GROUP BY Status

The DELETE runs first and is saved. Result Rows holds the report from the last statement, and Total Count the number of report rows.

Add rows

INSERT INTO DEMO_QS_Customers (CustomerCode, Name, City, CreatedById, UpdatedById)
VALUES (@code, @name, @city, @userId, @userId)

Guardrails: what is refused

Query Store only ever touches the Data Stores of the workspace the process runs in. Allowed statement types are SELECT, WITH, INSERT, REPLACE, UPDATE, DELETE, and SET @name = value for user variables.

The Data Store runs on a database server that every workspace shares, so a query may not change how the server behaves for the rest of the query, hold locks on it, or reach outside its own tables. These are refused before anything runs, whatever else the query holds. Execute Query and Execute Command run on your own database server and have no such limits.

In a query of several statements, the message starts with the position of the refused statement, for example Statement 2: Statement type 'USE' is not allowed. ….

All messages

You always get one of these messages, never a raw database error.

Handling errors in the process

When a Query Store step fails, because a query was refused, a SQL error or a time-out, the process stops and the instance is marked with errors. To handle the failure yourself, add an error branch: drag a connection from the step's error port to the step that should run instead. You can store the error in a variable of type Error to use its message.

When the DROP TABLE is refused, the process follows the red Error line. The error branch records the message and the process finishes successfully.

Action errors shows the message for the failed step. The error variable holds the same message for later steps.

Combining Query Store with other features

Troubleshooting