OpenWGA 7.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 for MySQL/HSQLDB/...
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 JDBC Driver in use needs to support scrollable resultsets for this query type to work.
There are two ways of querying content stores, depending on what you want the query to return: Content documents or just raw data.
Returning content documents
To accomplish this the query must return the data columns of content documents, which in the query must be marked by curly braces. 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.
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.
Returning column data
If you omit curly braces in your query it will just return the data of the selected columns in a virtual resultset. For each result row there will be a virtual document containing the row column data as items "column1", "column2" and so on. Unfortunately it is not possible to retrieve columns via a column alias.
This is a very simple query capable of determining the number of released content documents in a content store of version 5. The count will be available as item "column1" on the one virtual result document:
select count(*) from content where status='p'
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. Alternatively you can determine an alias with the "AS" keyword in the SQL query to make a column retrievable by that alias:
select count(*) as cnt from requests
In this example the column will be retrievable as item "cnt".
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 parameters 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", the second by name "2" and so on.<tml:query type="sql">
<tml:param name="1">customer</tml:param>
<tml:param name="2" expression="5"/>
select * from companies where type=? and zipregion=?
</tml:query>