Skip to main content

Basic SQL Statement

Although SQL is used in many parts of the ADITO application, there is one basic SQL statement that is executed when a Context is opened, e.g., to display data in the FilterView. This basic SQL statement has got, as most SQL statements, the following clauses:

  • a SELECT clause (including all columns to be load)
  • a FROM clause (including all involved tables)
  • an optional WHERE clause (including one or multiple conditions)
  • an optional ORDER BY clause (including one or multiple columns to use as order 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 MYFIELD.value and MYFIELD.displayValue, with the properties

  • recordfield: one specific database column to load
  • expression: an SQL expression to use instead of a specific column (marked with "(...)" in the above example SELECT). If the result.string() argument of this property's code is simply "MYTABLE.MYCOLUMN1", then the effect is the same as if we had selected MYTABLE.MYCOLUMN1 as recordfield. However, you can also enter advanced SQL code here, e.g.
    • to concatenate multiple columns, e.g. result.string("MYTABLE.MYCOLUMN1 || MYTABLE.MYCOLUMN1")
    • to enter a sub-select, e.g., SELECT ... FROM ... WHERE... (Caution: Depending on the kind of sub-select, this will be executed for every single dataset, which may decrease the performance).

The FROM clause is specified in the following properties of the RecordContainer:

  • linkInformation: one or multiple tables to which the specified columns (see above) belong to. Caution: If you specify more than one table here, the cross product of all tables is loaded by default, which can result in huge data masses and therefore be a performance killer; therefore, multiple tables should only be specified along with additional properties, especially fromClause (with, e.g., JOINs) and conditionProcess (see below).
  • fromClauseProcess: Optionally, you can enter the complete FROM clause here (without the word "FROM" itself). JOINs may be included. All involved tables must nevertheless be specified in the property linkinformation. As usual for processes, the SQL must be specified as argument of method result.string(), e.g.,
result.string("MYTABLE JOIN OTHERTABLE ON (...)")

The WHERE clause can optionally be specified in the property conditionProcess (without the word "WHERE" itself). As usual for processes, the SQL must be specified as argument of method result.string(), e.g.,

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

The ORDER BY clause can optionally be specified in the property orderClauseProcess (without the words "ORDER BY" theirselves). The SQL must be specified as argument of method result.object(), e.g.,

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

NOTE
In order to access a database, you should use prepared statements instead of plain SQL code - at least in all cases, where an external input is processed (i.e., text input by the user or a variable filled by an import process). Among other advantages, this increases the data security of ADITO, as attacks by the "SQL injection" technique are avoided. In the library SqlBuilder_lib you can find several classes providing SQL helper functions, including prepared statements, in particular, the classes SqlBuilder and SqlUtils. Further information and examples can be found in property documentation of SqlBuilder_lib.