The SQL element allows you to execute standard SQL queries in your applications. Instead of writing your own SQL queries consider using Aggregates, since they are highly optimized and easier to maintain.

SQL Interface

SQL 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, SQL can have:

Since the OutSystems Platform is 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 query have the User List data type. Learn more about the SQL Output Structure.

Execute an SQL Query

To execute an SQL query:

  1. Inside an action, drag the SQL tool from the toolbox on the left to the action flow;
  2. Double-click the SQL query to start editing it;
  3. Define its input parameters and specify its output structure;
  4. Write your query. Ensure that the returned table will match the output structure defined and you are using the syntax {EntityName}.[AttibuteName];
  5. Use the 'Test Inputs' tab to test your queries with different values. For each input parameter you need to write a value that respects the parameter data type;
  6. Test your query by clicking the 'Test' button.

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 Data 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 the environment management console.

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

What you Should Know

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

See Also

SQL Properties