DQL pages

ExecDQL in DataEase server can be served from a single file or from a table in the Application. If you are using a table, you can split header and body in two memos or use single file format from one memo.

DQL extensions for server

To accommodate LiveText an DQL query from URL, we have made a couple of extensions to the DQL format. These are not part of DQL prism, but parsed by the server and converted back to native DQL by the body tree parser. The extensions are as "name" in list records and data-entry custom named fields.  This are how they works:

Named list record entries

When using LiveText, the name used for LiveText inside a .items are the same at the element in the list records. When you use a long formula, this become inconvenient. The help out we have added the as keyword to the end of the list records line. This will then let you use the name given in as "name" instead of the formula. The following limitations apply: we only support single line elements. You must always add a name not used previous and without any separators in the name like ; and .

Custom named data-entry fields

The body tree parser now picks up all data-entries in the code and add named fields as text 255 to the dql runner. The values for the data-entry are set using query fields in the url.

Ex. Url /report/order.html?&field1=Norway will open the DQL found in appfolder\static\report\order.html and run with data-entry field1 set to Norway. The result is then merged with the code in body part of the file using .commands and livetext.

Example using the single file/body format

.dql
for Order with CustomerID = data-entry CustomerID;
list records
    OrderNo;
    Reference ;
    concat(Name, ": ", PostAddress, " ", PostCode) as "NameAndAddr" ;
    OrderDate ;
    DueDate ;
    OrderSum .
end
.end
<div class="row">
<table class="table">
.items
    <tr>
        <td>[{OrderNo}]</td>
        <td>[{Reference}]</td>
        <td>[{NameAndAddr}]</td>
        <td>[{OrderDate}]</td>
        <td>[{DueDate}]</td>
        <td>[{OrderSum}]</td>
        <td><a class="btn btn-primary btn-xs" href="/demo/orderedit/?OrderNo=[{OrderNo}]">Edit</a> </td>
    </tr>
.end
</table>
</div>

Ex. for new format list all orders where given customer id given in url like /order/orderlist/?CustomerID=012345 where the query gets picked up by the data-entry in the DQL and the combined name and address are output in the third table cell.

Body format:

All text before first .command is the global header.

.header is a header for one level above the previous command, so if your data have several levels, more than one header is needed. You can end a header with .end but a new command automatically ends it as well.

.items lowest level items of the DQL. Usually the innermost data list. You can end items with an end, but a footer will also end items.

.footer footer data for levels outside the innermost data, it you have more than 2 levels, more than one footer is needed to list all data. You can end a footer with an end but a new footer will also end it.

.end is ending the previous command, any new command also end the previous command, so .end is usually only needed for the last footer before the global footer

All text after last .end is the global footer

Live text format

In any of the different header, items and footers, you can add a live text. The format is [{name:filter}]. The name represents the field name given by the DQLs list record.The filter can be used for make proper DOS reports with Left, Right, Center and Format. Ex [{Name:L40}] will take the data returned in List records Name and make sure it is length 40 by adding spaces. :C centers the field, R right pads the field and F formats a number like LineTotalt:F999 999 999,99 makes sure that you get number formated like that. You can also add derived field to the format bu adding (SPACE) derivation. Ex. [{LineTotal:F999 999 999,99 sum}] will use the derived line total of sum from the list record.

Ex DQL:

define temp "telle" Number.
telle := 0 .
for Order ;
 telle := telle + 1 .
  list records
    telle ;
    OrderDate ;
    OrderNr ;
    CustomerNr .
    for Orderlines ;
    list records
      SalesGroup in groups ;
      Stock Code ;
      Desc ;
      Count : item sum ;
      Unit ;
      Price ;
      LineTotal : item sum ;
      OrderNr : count .
end

Ex Body:

<pre>
*************************************************************************************
DQL with 2 for loop and 2 list records groups and totals
*************************************************************************************
.header -- order heading
[{telle:R2}] Date: [{OrderDate:C10}] Customer: [{CustomerNr:L7}] Order: [{OrderNr}]
.end
.header -- group heading
Group on: [{SalesGroup}]
.end
.header -- items heading
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
.end
.items -- list order lines
[{Stock Code:R8}] [{Desc:L51}] [{Count:R4}] [{Unit:L6}] [{Price:R12}] [{LineTotal:R14}] 
.end
.footer -- group total
Group total: Ordernumber count: [{OrderNr:count}] Count sum: [{Count:sum}] Line total sum: [{LineTotal:F999 999 999,99 sum}]
.end
.footer -- order total
Order total: Ordernumber count: [{OrderNr:count}] Count sum: [{Count:sum}] Line total sum: [{LineTotal:F999 999 999,99 sum}]
=========+=========+=========+=========+=========+=========+=========+=========+=========+=========+
.end
.footer -- grand total
===============================================
Grand total: Ordernumber count: [{OrderNr:count}] Count sum: [{Count:sum}] Line total sum: [{LineTotal:F999 999 999,99 sum}]
.end
===============================================
</pre>

In a Web server you probably would add either html code to each item or a <pre></pre> like we did in this example.

The default format for a DQL

This is the format used when reading a web application with pages and DQLs from a WebServer table.

This is all that is needed to get it to work. The format of the ExecDQLs and the Body is the same as for standard ExecDQL inside DataEase. 

When served from a table, you can used default or specify all parameters in the url like where to read dql from and what fields are used for each element described above. To use defaults give you much shorter url than if you want to use your own table or fields.