Define Advanced Queries

The Advanced Query element allows you to execute standard SQL queries over your application's Entities. Before using an advanced query, consider using Simple Queries instead since they are highly optimized and future-proof.

Advanced Query Interface

Advanced queries are black-boxes, meaning that they can only have access to data that is sent through input parameters, and other logic can only access what they return through the output parameter.

As such, an advanced query can have:

- Input parameters: The only way to send data that can be used inside the advanced query. Input parameters are optional. Learn more about Advanced Query Parameters;
- Output parameter: In the OutSystems Platform, advanced queries always have two output parameters, even when the query executed did not return any results:
- - List: The record list returned by the query. If the query did not return any results, this list is empty.
  - Count:The number of records returned by the query without considering the Max Records limitation specified in the advanced query properties.

Since the OutSystems Platform has a strongly typed language, you need to define the structure (data types of the columns) that your query returns. For example if you perform a query over the User entity, that selects the attributes Id, Name, Email, PhoneNumber, you need to specify that your query has an output structure that has the User Record type.

This makes the List output parameter of the advanced query have the User Record List data type. Learn more about the Advanced Query Output Structure.

Execute an Advanced Query

To execute an advanced query use the following steps:

1. Inside an action, drag the advanced query from the toolbox on the left to the middle of the action flow;
2. Double-click the advanced query to start editing it;
3. Define its input parameters and specify its output structure;
4. Write your SQL query. Ensure that the returned table will match the output structure defined and you are using the syntax {EntityName}.[AttibuteName];
5. Test your query by clicking the Test button and close the advanced query editor when everything works as expected;
6. If you defined any input parameter, you need to specify the arguments to send: click the advanced query, and in the properties on your right hand side specify the arguments to send.

What you Should Know

When defining, testing, and executing an advanced query, there are some details that you should be aware of:

- Advanced Queries should only be used to execute SQL queries: Select, Insert, Update and Delete statements. Using Data Definition Language (DDL) statements like Create Table, Alter Table, Drop Table... might make the OutSystems Platform metamodel inconsistent, leading to misbehavior;
- To ensure the OutSystems Platform metamodel is always consistent, you cannot perform queries directly to the physical tables of the metamodel. Service Studio presents an error message if your query uses tables prefixed with OSLOG, OSSYS, or OSUSR;
- The OutSystems Platform starts a transaction when the web request arrives to the server, and commits the transaction before sending the reply to the user. If an exception is left uncaught, the transaction is rolled back to ensure your data is always consistent. This means that your advanced queries run inside this transaction and their changes are rolled back if something goes wrong;
- Advanced queries are tested and run in the database using the Runtime user specified in the Configuration Tool. You need to make sure this user has permissions to run the SQL statements you specified in the advanced query, otherwise an error is displayed when you test the query or execute the advanced query at runtime;
- If the Database eSpace property is set to 'Both', Service Studio analyses the SQL query to ensure they are compliant between SQL Server and Oracle. If not, a warning message is displayed.

Re-executing a Query

There are scenarios in which you might need to execute the same query twice. For example fetching the data in the Preparation, before the screen loads, and then execute the same logic after the end-user has executed an action. In such situations you can place your query in the Preparation of the Web Screen and use the Refresh Query tool on the flow of the screen action where you need to re-execute the query.

Query Time-out

To ensure the scalability of your infrastructure, queries are only allowed to run for a specified time: if the query does not return results within that time frame, the query is aborted ensuring your database performance is not compromised, an exception is thrown and the error is logged in Service Center.

By default, the query time-out is 30 seconds, but you can adjust this value to your needs as follows:

- Globally change the time-out for all queries: in the Platform Server Configuration Tool, change the Default query timeout parameter at the Database tab. If you don't have access to the Configuration Tool, ask your OutSystems Platform administrator.
- Change the time-out for a specific query: define the Timeout in Seconds property with the suitable value.

Due to limitations of the database connector used in the .Net version of the Agile Platform, queries executed over Oracle databases don't have a timeout, even if one is specified. In these scenarios you should ensure that your queries are carefully designed, to avoid deteriorating the performance of the database server.

Improving Runtime Performance

To improve the runtime performance of your Simple Queries, you can use cache, to avoid fetching twice the same data from the database. To do so, simply set a value in the Cache in Minutes property of the advanced query.

Learn more about caching contents.