Skip to main content

 

Developing an Application

 

OutSystems

SQL Queries

The SQL SQL element allows you to execute, test, and review custom SQL queries in your applications. The element provides flexibility in data manipulation, but we recommend using Aggregates when applicable. Aggregates are highly optimized and easier to maintain.

SQL queries can access data that is sent through the input parameters only, and other logic can access only what the SQL query returns through the Output Structure.

Input parameters
Providing input parameters allows you to use dynamic data in the SQL query. Input parameters are optional. To reference an input parameter in your SQL statement use a @ prefix, e.g. @CustomInputParameter.
Output parameter

SQL in OutSystems queries always have two output parameters, even when the query executed does not return a result:

  • List: The list with the result returned by the query. The list is empty if there are no results.
  • Count: The number of records returned by the query without considering the SQL Max Records property.

Output Structure is mandatory. You need to define the structure (data types of the columns) that your query returns. For example, if you perform a query over the Employee Entity that selects the attributes Id, Name, Email, PhoneNumber, specify the Output Structure with the same attributes in the same order and with matching data types. This enforces that List output parameter of the SQL query returns Employee List data type. Output Structure is needed even if your SQL statement does not return any results.

To reference an entity in your SQL query write it between curly brackets (e.g. {User}) and to reference an entity attribute write it between square brackets (e.g. [PhoneNumber]).

Write Your Own SQL Query

Do the following:

  1. Add a SQL element to an action flow.
  2. If necessary, define the query parameters.
  3. Write the SQL query.
  4. Define the output structure used for the output of the SQL node.
  5. Use the output list of the SQL node to access the result of the SQL query.

Notes and guidelines

Consider the following when using the SQL element:

Avoid Data Definition Language
The SQL tool should only be used to execute the following: SELECT, INSERT, UPDATE, and DELETE statements. Using Data Definition Language (DDL) statements like CREATE TABLE, ALTER TABLE, DROP TABLE, etc. might make the OutSystems metamodel inconsistent, leading to misbehavior.
No physical layer query
To ensure the OutSystems metamodel is always consistent, you cannot perform queries directly to the physical tables of the metamodel. The platform shows an error message if your query uses tables prefixed with OSLOG, OSSYS, or OSUSR.
Exception triggers a rollback
OutSystems 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 queries run inside a transaction and that changes are rolled back if something goes wrong.
Check runtime user permissions
Your 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 specified in your query, otherwise an error will occur when you execute the query at runtime or when you test it.
Database support check
If the Database Module Property is set to All, OutSystems checks the queries to ensure they are compliant with all supported databases. If not, a warning message is displayed.
Avoid enabling the Expand Inline property of query parameters
Proper use of parameters being expanded inline is hard, since you need to make sure that any user input has been properly escaped in the right way before using it in an SQL statement. If possible, avoid enabling this property altogether.
OutSystems provides ways of implementing common use cases without enabling this property. Check Building dynamic SQL statements the right way.
  • Was this article helpful?