The SQL canvas is where you type the SQL query you want to execute. OutSystems Platform validates if your query matches the output structure that you have specified and presents warning messages when something is not correct.

Since OutSystems Platform translates the entities and attribute names into their physical table names, there are a few syntax rules you should follow to ensure your queries are built to change.

Using Entities

Enclose the entity name in brackets: {EntityName}.

As an example, a query to select all attributes from the Users entity would be expressed as Select {User}.*  from {User};

Using Entity Attributes

To refer to a specific attribute you should use {EntityName}.[AttributeName]. As an example, a query to only select the Id attribute from the Users entity would be expressed as Select {User}.[Id] from {User};

Selecting all Attributes

To select all attributes you should use the notation {Entity1}.*,{Entity2}.*.

Specifying the entities' names in the select statement, instead of just using Select * has two benefits:

Since the OutSystems Platform expands the syntax Select {Entity}.* into Select PhysicalTable.Attribute1, PhysicalTable.Attribute2... this ensures that even when you change the order of the attributes, delete, or add an attribute in the entity definition, the select statement will return the expected result.

On the other hand, if you only use Select *, the OutSystems Platform executes the statement without expanding it, meaning that you might get errors if you change the entity definition after it has been created. To learn more about this issue, check this forum post.

Using Input Parameters

To refer to input parameters declared on your queries, use @ParameterName. As an example, a query to select users based on their name would be expressed as Select {User}.* From {User} Where {User}.[Name] like @Username;

Writing SQL Queries


To query an entity, simply write your SQL query following the notation explained above. As an example:

Select {User}.[Id] from {User} where {User}.[Name] like '%@SearchKeyWord%' order by {User}.[Name] Asc

Insert, Update, Delete

Inserting, updating, and deleting records from the database is also supported. However if you only intend to insert, update or delete a single record, you should use the available Entity Actions instead.

Since the Output Structure is mandatory, you need to specify one, even if your SQL query does not return any result.

Querying Entities from Other Modules

To design queries that contain entities from other modules, simply import the entities using the References window. After importing the entities, you can use them in your queries just like if they were defined in your own application module.

To insert, delete or update records from a referenced entity, in the producer module you need to set the entity 'Public' property to 'Yes' and 'Expose Read Only' to 'No', otherwise a runtime error is thrown.

Querying Tables from Other Databases

There are two ways of querying tables from other databases: using extensions, or linked server.

Using an Extension

Using the environment management console (Integration Studio), you can import tables from external databases into entities. After you publish the module, the entities become available to be reused in the development environment:

  1. In your application, use the References Window to create a reference to the new module;
  2. The imported entities become available in your application so that you can query them.

Using a Linked Server/Database Link

If your OutSystems Platform database is configured to use a linked server/database link, you can simply query the tables in the external database using standard SQL.

This technique adds a performance penalty to your applications, since the queries are sent to OutSystems Platform database server that then sends them to the linked server to be executed.

Using Stored Procedures

You can run stored procedures defined either on OutSystems Platform or external databases. To execute a stored procedure, you need to ensure OutSystems Platform runtime user has permissions to do so.

Since the Output Structure is mandatory, you need to specify one, even if the stored procedure does not return any results.

OutSystems Platform does not read the stored procedure output parameters: you will be able to execute a stored procedure but if it returns some result you will not be able to use it in your logic. If you need to read the output parameters of stored procedures, create an extension using the environment  that encapsulates this logic.

Changing the Default Connection

OutSystems Platform checks the entities used in your query to use the appropriate database connection. However there are scenarios in which you don't specify any entity in your SQL query, for example when using stored procedures. In such scenarios you need to specify which connection to use, by using the syntax:

/* %LogicalDatabase%=GetLogicalDatabase({Entity Name}) */

To do so, you need to have at least one entity imported from the same database you want to invoke the procedure.

In this example the CUSTOMER entity was imported so that we could connect to the correct database and run the usp_GetRecentPurchases stored procedure.

See Also

About SQL