Skip to main content

Basic SQL Statement

SQL is used in many parts of ADITO. One central statement is the basic SQL query executed when a Context is opened, for example to display data in a FilterView. Like most SQL statements, it consists of the following clauses:

  • a SELECT clause containing the columns to load,
  • a FROM clause containing the involved tables,
  • an optional WHERE clause containing conditions, and
  • an optional ORDER BY clause containing the sort criteria.
SELECT MYTABLE.MYCOLUMN1, MYTTABLE.MYCOLUMN2, (...)
FROM MYTTABLE
WHERE MYCOLUMN1 = 'myParameter1' AND MYCOLUMN2 = 'myParameter2'
ORDER BY MYTTABLE.MYCOLUMN2

The SELECT clause is defined in the RecordContainer's RecordFieldMappings such as MYFIELD.value and MYFIELD.displayValue by using the following properties:

  • recordfield: The database column to load.
  • expression: An SQL expression used instead of a specific column. In the example above, this corresponds to the placeholder (...). If result.string() simply returns "MYTABLE.MYCOLUMN1", the result is equivalent to selecting that column directly. However, expression can also contain more advanced SQL, for example
    • concatenation of multiple columns, such as result.string("MYTABLE.MYCOLUMN1 || MYTABLE.MYCOLUMN1"), or
    • a subselect such as SELECT ... FROM ... WHERE ....
warning

Depending on the type of subselect, the expression may be executed for every single dataset. This can reduce performance significantly.

The FROM clause is configured with the following RecordContainer properties:

  • linkInformation: The tables to which the configured columns belong. If you enter more than one table here, ADITO loads the cross product of all listed tables by default. This can create very large result sets, so multiple tables should only be used together with additional configuration such as fromClauseProcess and conditionProcess.
  • fromClauseProcess: Optionally contains the complete FROM clause without the keyword FROM. JOIN statements may be included. All referenced tables must still be listed in linkInformation. As usual for processes, return the SQL via result.string(), for example:
result.string("MYTABLE JOIN OTHERTABLE ON (...)")

The WHERE clause can be configured in conditionProcess, again without the keyword WHERE. Return the SQL via result.string(), for example:

result.string("MYTABLE.MYCOLUMN1 = (...)")`.

The ORDER BY clause can be configured in orderClauseProcess without the keywords ORDER BY. Return the clause as an object via result.object(), for example:

 result.object({"MYTABLE.MYCOLUMN1":db.ASCENDING, "MYTABLE.MYCOLUMN2":db.DESCENDING});
important

When accessing a database, use prepared statements instead of plain SQL wherever external input is involved, for example user input or variables filled by import processes. This is important for security, especially to prevent SQL injection attacks.

The library SqlBuilder_lib in module utility provides helper classes for this purpose, especially SqlBuilder and SqlUtils. Additional information and examples are available in the documentation property of SqlBuilder_lib.