OpenWGA 7.11 - OpenWGA Concepts and Features

Data sources » Custom MySQL/HQLDB/... Database

Using in WebTML

Both data source types support querying with query type "sql". This takes some custom SQL query whose resultset rows will be served as virtual content documents. The row columns are retrievable as items with names equaling the column name (or alias) in lowercase.

<tml:collection>

    <tml:query db="inventory" type="sql">

        select name, amount from shelf where not ownerid is null order by name desc

    </tml:query>


    <ul>

    <tml:foreach>

       <li><tml:item name="name"/> - <tml:item name="amount"/>

    </tml:foreach>

    </ul>

</tml:collection>

The "enhanced access" type also supports browsing the tables of the database as "areas". The rows inside the area are then presented as virtual content documents on first level. So the following iterates over all rows of table "shelf":

<tml:siblings context="db:inventory/area:shelf">

   <li><tml:item name="name"/> - <tml:item name="amount"/>

</tml:siblings>

On "Enhanced Access" the returned virtual content documents are editable. Using the WGAPI content object you can set values to the items that will be reflected in the columns that they represent. Imagine the following WebTML action that resets the amount field:

content().setItemValue("amount", 0);

content().save()

Rows can be created and deleted too using the WGAPI when using "Enhanced Access". For creation use the method createContent() without arguments on the WGAPI area object. For deletion use the method remove() on the WGAPI content object:

var newContent = db().getArea("shelf").createContent();

newContent.setItemValue("name", tmlform.name);

newContent.setItemValue("amount", 0);

newContent.save();


oldContent.remove();

An alternative to navigating areas is the query type "table". This also retrieves the rows of a table but allows specifying a WHERE filter expression. The returned virtual content documents are also editable via WGAPI. The query type takes the name of the table inside the type attribute - divided from "table" by a column. The query string then may contain a WHERE and additionally an optional ORDER clause:

<tml:query db="inventory" type="table:shelf">

    not ownerid is null order by name desc

</tml:query>