ExecDQL

Function::Internal

ExecDQL
ExecDQL("DQL Script","Data-Entry Field1","Data-Entry Field2","Data-Entry Field3","Data-Entry Field3","Export File", "Body", "Extra parameters")
ExecDQL("delete records in MyTable.")
ExecDQL("for My Table with MyField=Data-Entry Field1 ; modify records My Field2=data-entry field2 .",
ExecDQL("@NameScript","Data-Entry Field1","Data-Entry Field2","Data-Entry Field3","Data-Entry Field3","", "@NameBody")
ExecDQL("#IDScript","Data-Entry Field1","Data-Entry Field2","Data-Entry Field3","Data-Entry Field3","", "#IDBody")
ExecDQL("*:filename.dql","Data-Entry Field1","Data-Entry Field2","Data-Entry Field3","Data-Entry Field3","", "*:filename.bdy")

To run a DQL script as a function from anywhere.

DQL is the most useful part of a DE application but it has had its limitations because it has been part of the DQL document (report). With ExecDQL we have liberated it, and you can now call a DQL from anywhere at anytime. It follow normal DQL syntax rules and it will generate an variable length export file if you include a file name as the last parameter (parameter 6).

Because it is a function you have to reference the Data-entry fields (transfered as parameter 2-5) with fixed names i.e Field1, Field2, Field3 and Field4. Other than that it is completely traditional DQL.

The ExecDQL has the GUI context of the document from where it is called, so you can read and manipulate the document from the DQL. This means that ExecDQL is not only a Data manipulating procedure it can also be a GUI manipulating procedure.

If you need more input variables than 4 you can simply pick them out of the Form you are calling the ExecDQL from with GetValue()

To make ExecDQL and other parts of DE8 more effective we have also introduced ESCAPE values in DE8 Strings.

Early in 8.0 we introduced CHR() to make it easier (possible) to use reserved letters in DE derivations and DQL etc. However it is a little awkward when you have to use ConCat() to joint things together for instance in a ExecDQL etc.

Ex.
ExecDQL(concat("message ", chr(34), "Hello world!",chr(34)," window.","","","","","")

So now you can simply do this with the escape charcter for ".
Ex.
ExecDQL("Message /'Hello World!/' window .")

Ex. LE9 you can use " or ' for string so the it will be
ExecDQL('Message "Hello World!" window .')

The observant reader also see that we don't add all the "" at the end of the function. This is news in 8.2 too. We have introduced variable number of parameters to make the functions more user friendly.

Up to now you had to be exact with your parameters and that could be a chore when the only thing you wanted was to execute a small line of commands with no export, and no input.

You will still have to follow the numbers so if you want to include an export file for ExecDQL you will need to include 4 empty "" as parameters before the export file name, but if you only need two data-entry parameters you can now simply add the two and skip the 3 last parameters.

Parameters

DQL: (String)

Here you can write a DQL direct into the execdql. The length of the dql follow the same rules as any string added to input. It will be auto escaped if you not use any of the spesial formatting It can do anything a DQL can do and has the same format as any other DQL You can also add a field name read the information from a text or memo field. If you add raw: in the start of the text it will not be auto escaped. If you add file:filename it will read the dql from disk. If you add lable:lablename it will read it from a label. If you add @dqlname it will read the dql from $$DQLStore$$ table using DQLName field to lookup the script. If you use #number it will use DQLId to look up a numbered dql. The DQL will be stored in DQLScript field in the table. Same rules applied to Body execpt that is read from DQLBody field. *:filename.dql will read a dql file from the dql folder in app.

From LE9>= 7022 you can use ~name for body and text, and these will be read from the current form blob using json path execdql.%s.code and execdql.%s.body where %s is the name without the ~.

From LE9>=7046 you can use .dql -> .enddql and .body -> .endbody for a single field and single file format. All .commands must start at line start without any white spaces in from and the file must start with . as first character.

From LE9>=7074 you can use .commands as extra parameters (see last paramter in execdql command)
.tdf - return tdf instead of running. Supported values fullonly and cleanedonly (1, yes, true also means cleanedonly)
.includenameinheader - include header in export. Take any true value like 1, yes and true.
.allasstring - all export values as strings. Take any true value like 1, yes and true.
.exportfielddelimiter - delimiter to use between fields. Can take any char or SPACE, TAB, CR and LF. Default value if not set is ~

Data-Entry Field1: (String)

This is the parameter that you can reference inside the DQL script as Data-Entry field1.

Data-Entry Field2: (String)

This is the parameter that you can reference inside the DQL script as Data-Entry field2.

Data-Entry Field3: (String)

This is the parameter that you can reference inside the DQL script as Data-Entry field3.

Data-Entry Field4: (String)

This is the parameter that you can reference inside the DQL script as Data-Entry field4.

Output command: (String)

Where to write the result based on prefix.

file:filepath
memo:memofieldinform
pdf:pdffilepath - the pdf is generated from the html output and send trough the htmltopdf.exe program comming with DE
web:printername,[how(0-3)] a temporary html file is generated using body and printed 0-screen 1-preview 2- default 3-ask default is screen

Body: (String)

Here you can add a body format to generate output from the dql. It can take all the same input types as the DQL like @Name, #ID, *:filename.bdy read body from dqlfoder in app or from LE9>=7022 ~name for form blob at execdql.%s.body where %s is the name without the ~.

If you use *:filename (any with second pos is :) or \filepath (starts with \) to load a file, the absoulute file name will be generated by trying to open files in the following order:

For all starting with *:

1. Take path given in [EXECDQL] DqlPath= and append file name after *: and see if that exists
2. Take repo path and add dqls, then add the file name after *: and test if that exists
3. Take the exe file path adn add dqls to that and the filename after *: and test if that exists
4. If running in server try repopaht + filename after *: and try if that exists

For any with : in pos 2 or starts with \

1. If filename is a absoulute file path try to open that.

Body export format

ExecDQL was designed to cover two major flaws in the DQL hierarchy of DFW. Manipulation and export so if you add a file name her and a list records in the DQL it will create a CSV file with column headers where the columns are in the order you put them in the list records.

Body live text format

If you have added a body to the execdql either by writing it into the field or adding it from $$DQLStore$$ by @name, #id or LE9 7022> ~formbodyname, you can use this text format to merge data genereted in the list records

.commands

A body must have at least a .item and .end starting from pos 0 in line to work.
.header - header elements in body if dql is multilevel
.group header - a group header elements in body if dql is multilevel and have in group
.items - start of main level list records items
.footer - footer elements if multilevel dql
.group footer - a group footer elements if multilevel dql and have a in group, this is mostly used for aggregate at group level
.end - end of last level list records items
.page insert a html page break
.if value - check if field from list records is blank or not
.else - field from list records was blank so do from here
.endif- end if if

[{livetext}]

A live text i the exact name of a field used in a list records and will be replaced with value representing the list records field.

A livetext can have formating, be aggregate and from LE9>7037 have filters.

livetext formatting

The formatting in livetext comes as :format in the live text. Ex [{price:F6.2}] will format a number form DE with 6 digits and 2 decimals using . as decimal seperator.

:F - decimal (float as D is used by date)
:I - integer
:D - date - how to format a date, can use yYmMdD with any seperators. Ex. [{orderdata:Ddd/mm/YYYY}]
:T - time - how to format the time using hHmMsS with any seperators. Ex. [{starttime:THH:MM}]
:R - right with padding
:C - center with padding on both sides
:L - left with padding

livetext aggregate

This is used to tell what aggregate to use from the list records. The name is the sam as used there and can be direct after the : of after the format seperated by a space. Ex. [{orderline:sum}] ot [{orderline:F7.2 sum}]

:item
:sum
:percent
:mean
:max
:min
:count
:variance
:std.dev.
:std.err.

livetext filters new in LE>=7037

Filters manipulated the data comming from list records and rewirte it to something else before putting any formatting on. You can have as many filters as you want chained by seperating them with a SPACE. To add a filter use |filtername. Ex. [{repopath|tojsonstring}] to escape the \ characters in the path as used in json.

|tojsonstring - convert the string to something that can be used in json output. All illegal characters will we escaped like \ -> \\ and CR to \n
|fromjsonstring - take a json string input and make it back to DE.
|toutf8 - take a DE string and transform it to UTF8
|fromutf8 - take a UTF8 string and transform it to DE
|tojsonstringutf8 - do an escape to json string and also transform to UTF8
|fromjsonstringutf8 - take a UTF8 json string and transform it to DE
|urlencode - url encode a string to be used in a browser
|urldecode - take a url encode string and make it into DE string.

Extra parameters: (string) LE9>=7074

This parameter is used for setting command and values that is changing how the dql run and what it will output. The format is cmd1=val1&cmd2=val2&cmd3=val3...

The supported commands at the moment are: tdf, includenameinheader, allasstring and exportfielddelimiter. 

All this values can be set in document properties and the .dql format as well. Extra paramters will always override the other two ways of setting. Order is document properties, .dql settings and extraparameters. 

.dql format uses .cmd value and due to this commands that can have SPACE, TAB, CR and LF can use this alias for the character.

tdf

set output to tdf mode and can have the following values

Full tdf with all levels use: fullonly

Tdf with only levels with fields or aggregates (this is what is uses by execdql): 1, true, yes and cleanedonly

Any other values means not use tdf of turn of if set in any other place.

includenameinheader

Set A true false value if adding header in first line with the field names in export. 

1, positive value, true and yes means to use header all others not to use header.

allasstring

Export all csv values as string with "" around the value.

1, positive value, true and yes means to use string values all others not to use string values.

exportfielddelimiter

The delimiter to use between fields in the export. Default is ~ as all exports in DataEase. Can use SPACE, TAB, CR and LF instead of these names as some was of setting it removes white space. If not it will be the character after = og space for . commands.

Returns/Result

First field in list records if no body is used and only one field in list records.This is used to use execdql as functions returning a value.

If no body is set and many fields in list records, a export format are returned.

If the body is set , a generated body is returned.

The setting for execdql @name and #id

When using @ or # to lookup a execdql by name or ID, there are default, but you can also change these by settings in RDRRxAAA.INI. If nothing is set, a table named $$DQLStore$$ is used with the following fields for code, body, id and name: DQLScript, DQLBody, DQLId and DQLName.

DqlCodeField	Gives the name of the memo field that is used for dql body in execdql using either @name or #id for looking it up. Default is "DQLScript".
DqlBodyField	Gives the name of the memo field that is used for dql code in execdql using either @name or #id for looking it up. Default is "DQLBody".
DqlIDField	Gives the name of the field that is used to lookup #id in execdql. The field have to be a numeric field. Default is "DQLId".
DqlNameField	Gives the field name used to lookup the @name used in execdql. The field have to be a text field. Default is "DQLName".
DqlPath	Where execdql should look first when you have a *:dqlfile.dql for name or *:bdyfile.bdy for body in execdql
DqlTableName	Gives the execdql table name for @name and #number calling execdql. Default is "$$DQLStore$$".

All of this has to be set in the [EXECDQL] section unless used in server as you can have one for each prefix set and set it direct into this section instead.