OpenWGA 5.4 - Query languages reference


sql

General

This type of query executes a generic SQL statement on a relational database in the syntax of the database platform.

For OpenWGA Content Stores on relational database platforms

You can execute SQL queries on your content stores for special cases where the capabilities of HQL don't allow you to do what you want, or don't provide it effectively. But be aware that to use it you must have a basic understanding of the internal relational structure of the WGA Content Store. This structure differs between the available versions of WGA Content Stores and may be subject of further change on even later versions. So you most likely will have to lock your query into the structure of one specific content store version and will have to change it if this version changes.

The result of SQL queries on WGA Content Stores must always be interpretable as content documents. Therefor the query must return the data columns of content documents, which in the query must be marked by curly brackets. Also the query alias for the content table must be surrounded by those brackets. This leads to the following base query:
SELECT {content.*} FROM CONTENT {content}



Based on this you can extend the query with everything that your native SQL dialect supports.
The result of these SQL queries are the content documents whose data is selected in the query that also are editable in that form.
The curly brackets are not actually passed on to the backend server. After OpenWGA has processed these marks they are removed from the query before it is executed by the SQL server.

Query parameters

SQL for content stores supports the specification of query parameters in the query which can be dynamically injected, like with <tml:param> in WebTML. Specify the parameter with a colon and a custom name directly inside the query:
SELECT {content.*} FROM CONTENT {content} where content.city = :city

Do not surround the parameter with type specific delimiters, like single quotes for strings. The parameter injection functionality will automatically take care of this. Her is a complete example in WebTML:
<tml:query type="sql">
  <tml:param name="city">Berlin</tml:param>
  SELECT {content.*} FROM CONTENT {content} where content.city = :city
</tml:query>

While it at first might seem easier and better to just inject the needed value at the right position in the query using query parameters has some crucial advantages why they should be preferred where possible:
  • Query parameters are automatically protected against SQL injection attacks. They take care that the injected value will only be used as a literal.
  • Query parameters allow database drivers to precompile the SQL statement and reuse it with different parameter values, preventing unneccessary recompilation.


For data sources of type "Custom MySQL/HSQLDB/... database"

The SQL queries for these data sources does not have to fit any WGA dependent prerequisites. The result of the query is interpreted as list of content documents where each result row represents a single virtual content document.The column values are retrievable as items on these documents with their column names (in lowercase) as item names.

For queries with large result lists the data source type "JDBC Database (Query only)" is more resource effective than its counterpart with "enhanced Access". because the latter always loads the whole result data into memory.

The resulting data documents are in both cases not editable.

Query parameters

Query paramters on SQL for custom JDBC databases is also possible, although they do not support named parameters. Instead the parameter position inside the query should be marked by a question sign "?". Individual parameters should be addressed in <tml:param> with their ordinal position. The first parameter in the query therefor is addressed by name "1".
<tml:query type="sql">
  <tml:param name="1">Berlin</tml:param>
  SELECT {content.*} FROM CONTENT {content} where content.city = ?
</tml:query>