OQL
Overview
This module allows you to execute OQL queries from a microflow. Snippet to test & export OQL queries, add parameters with actions, execute query returning entities, execute query & return a CSV, etc.
Documentation
Description
This module allows you to execute OQL queries from a microflow.It allows setting named parameters which will be properly escaped.
Typical usage scenario
Query data from the Mendix database and the result will be mapped to a Mendix entity.
The example below is part of the Example module within the project, located on the GitHub repository.
Considering the following domain model:
One can execute a query obtaining all objects of ExamplePerson as a table. The rows within this table can be converted to non persistent entities for further usage. Therefore a ExamplePersonResult non persistent entity is modeled.
Parameters can be used using their corresponding actions (for each data type there's one activity defined:
All parameters should be added before executing an OQL query.
Adding an parameter requires:
- The name of the parameter, without a $ (e.g. Gender).
- The value.
Executing an OQL query requires:
- The OQL statement.
- A resulting entity (e.g. OQLExample.ExamplePersonResult).
- Amount.
- Offset.
After executing an OQL query, all previously set parameters are cleared.
The example query used to obtain the ExamplePersonResults are is:
SELECT
P.id ExamplePersonResult_ExamplePerson,
P.Name Name,
P.Number Number,
P.DateOfBirth DateOfBirth,
P.Age Age,
P.LongAge LongAge,
P.HeightInFloat HeightInFloat,
P.HeightInDecimal HeightInDecimal,
P.Active Active,
P.Gender Gender
FROM
OQLExample.ExamplePerson P
WHERE
P.Active = $Active AND
P.Age = $Age AND
P.DateOfBirth = $DateOfBirth AND
P.Gender = $Gender AND
P.HeightInDecimal = $HeightInDecimal AND
P.HeightInFloat = $HeightInFloat AND
P.LongAge = $LongAge AND
P.Name = $Name AND
P.Number = $Number
In the example above, the resulting columns Name, Number, DateOfBirth, Age, etc. are mapped to their corresponding attributes in ExamplePersonResult. The column ExamplePersonResult_ExamplePerson is mapped to the association (so one can retrieve the original persistent entity if needed).
Features and limitations
- Named parameters (like Data Set functionality) to avoid injection.
- Automatic mapping of result table to a list of objects (of a supplied entity).
- Mapping of an ID column to an assocation.
Dependencies
- Mendix 6.9 or newer
Installation
Download and import into your project.
Known bugs
A mapped association should be mentioned without its module prefix in an OQL query (e.g. ExamplePersonResult_ExamplePerson instead of OQLExample.ExamplePersonResult_ExamplePerson).