OQL

Category: Modules
Subcategory: All

Overview

This module allows you to execute OQL queries from a microflow. Key features: - Snippet to test and export OQL queries - Add parameters with actions - Execute query returning entities - Execute query and return a CSV

Documentation

Demo urlhttps://github.com/ArjenLammers/oqlmodule

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).

Releases

Version: 2.7.0
Framework Version: 8.14.0
Release Notes: Removed jQuery dependency from CSVAsTable widget.
Version: 2.6.0
Framework Version: 8.14.0
Release Notes: Re-release. OpenCSV was missing in 2.5 and causing a compilation error.
Version: 2.5.0
Framework Version: 8.14.0
Release Notes: Merged PR from Wouter. Added Atlas UI. Added new version of Community Commons. Due to conversion to 8.14 it should also be usable in Mendix 9.x
Version: 2.4.0
Framework Version: 8.0.0
Release Notes: - Solved issue #10 (Offset/Amount is not working by RichKarona) by including PR #11 by alexpostnl
Version: 2.3.0
Framework Version: 8.0.0
Release Notes: Included example in the published module. Fixed a XSS vuln in the CSVasTable widget.