Category: Building an Application

In general, SQL servers support limited indexing capabilities. Indexes can be maintained on individual columns (multi-valued or unique) and also on the concatenation of two or more columns (multi-valued or unique). In Zim terms, an indexed virtual field that corresponds to an SQL concatenated index can be represented only by an expression of the form:

$concat(field1,field2,…)

where “field1”, “field2” etc. are the names of fields only. No other expression operators are permitted.

This means that indexed virtual fields defined by such expressions can be mapped to indexes in the SQL database that are defined as the concatenation of two or more columns. The multi-valued/unique options for such indexes can also be mapped to SQL.

The Zim Definition Interface supports the export of indexed virtual fields whose associated field expressions conform to the simple concatenation structure indicated above. Performance improvements can be achieved from the SQL server for virtual field indexes so defined. Indexed virtual field expressions of any other form are not supported in SQL and accordingly, no performance improvements can be achieved. For applications whose design rests on the performance resulting from indexed virtual fields which do not conform to the above format (e.g. substrings of fields), test the application as it exists. If performance is not acceptable, then the virtual fields must be implemented as additional real fields, necessitating the creation of additional indexed columns to the corresponding SQL table(s).

Zim supports the specification of decimal places for fields whose internal representation is in the form of integers (data types “int”, “longint” and “vastint”). This is done by maintaining the logical position of the decimal point external to the physical storage of the data. SQL servers do not do this – integer data types contain integer data only. If the data for a field has either explicit or implicit decimal places, then it must be defined with the appropriate SQL data type (DECIMAL, NUMERIC, NUMBER, FLOAT, DOUBLE PRECISION, etc.). To formalize the translation of field definitions between Zim and SQL, Zim insists that fields of data type “int”, “longint” and “vastint”, which have non-zero decimal places, also have an appropriate value (greater than zero) in the “Length” field of the Data Dictionary entity set “Fields”. The value ranges for the “Length” field are

The values specify the maximum number of significant digits that the field is required to support. This number is the sum of the number of digits to the left of and to the right of the (implied) decimal point. For example, if it is known that data values for a particular field range from 0.01 to 9.99, then in standard Zim, the definition of the field could be

Type = ‘int’ Length = 0 Decimals = 2

For SQL tables, the definition must be expressed as

Type = ‘int’ Length = 3 Decimals = 2

In SQL servers, these two numbers are generally referred to as the “Precision” (Length) and “Scale” (Decimals) for columns of data type NUMERIC, DECIMAL, and so on.

The specification of a non-zero value for the “Length” in Zim is not a concern.  For these data types, Zim ignores the “Length” value. On the other hand, for SQL tables, Zim checks to ensure that the “Length” value lies within the ranges stated above. The Zim Definition Interface uses the “Length” values to translate Zim number field definitions to the appropriate SQL definitions and vice versa. Even though an “int” value has limited support for 5 digits (i.e. up to 32767), it cannot support up to 99999 and accordingly, its real maximum precision is 4. Consider an SQL data type of DECIMAL(5,2). Such a definition can support values from -999.99 to 999.99. A Zim data type of “int” cannot support this complete range. The Definition Interface translates such a definition into a Zim “longint” data type with a “Length” of 5 and “Decimals” of 2.

To a Zim application developer, the impact of this requirement is relatively small. For all fields of type “int”, “longint” and “vastint” which have a “Decimals” value greater than zero, a “Length” value from the ranges listed above must also be specified. The “Length” value must also be sufficiently large to support the maximum expected number of significant digits for data values for the field.

Indexed Fields in WHERE Clauses

In Zim, developers use expressions in the form

WHERE clauses retrieve data in either (a) ascending or (b) descending sequence of the data values in . This technique is used instead of an explicit sort because it takes advantage of a physical characteristic exhibited by Zim. However, there is no guarantee that any given SQL server behaves in the same manner. The use of indexed fields in expressions of the above form whose sole purpose is to deliver data in a particular sequence should be avoided. Instead, an explicit sort expression should be included in the set specification.

“Unrelated” and “Complete”

The performance of Zim set specifications qualified by “unrelated” or “complete” against SQL servers can be seriously affected, if that SQL server does not provide explicit support for the outer join operation. In such cases, use the following guidelines:

• Inspect the application code to determine if any set specifications including the “unrelated” or “complete” qualifiers exist. Determine if these are functionally altered if the qualifiers were removed.
• Performance can be improved by splitting a “complete” set specification into the union of the same set specification with the “complete” removed (the normal “related” case) and the same set specification using “unrelated”.

“DATE” Data

For date type fields, Zim accepts NULL values, true date values (like 20021031, etc.) and non-date values (like -1, 0, etc.). However, SQL servers do not accept non-date values. “DATE” data types should be restricted to valid dates and NULL values to ensure compatibility. If your application uses such non-date values and it will be difficult to change it, you can use the ”mapdate” file to map date values from ZIM to SQL and vice-versa.

SET manipulation

Sets built using Zim tables are different from sets built using Zim tables connected to a SQL server. In the following example:

set executemode zimmode

find States -> StateSet

set executemode sqlmode

list StateSet

The LIST statement will raise an error because it will try to list the records existing locally but referenced remotely, which is inconsistent. The opposite is also an error.

DB2 SAM Comparisons with Constants

The following construction:

FIND MyEnt WHERE MyField = 111 OR 1 = 2

Although valid in ZIM and in other SQL servers, this construction should be avoided because it brings an error from DB2. The reason is that DB2 requires object types to be cast all the time. and DB2 (thus raising the error), .

The topics in this section provide information on reporting through the Zim programming environment.

Refer to the following sections for more information:

Available Format Options

How to Use the Report Generator

Format Options

Output Masks

Any meaningful combination of the following options can be applied to any value displayed in a report:

Output masks are employed to format the output of fields, form fields, and data values in output operations.

The MASK format option in Zim Reports specifies an output mask pattern for a report item.

The $mask function specifies an output masking pattern to be applied to fields, form fields, and data values and returns a character string with the result of the masking operation.

The Field Mask attribute defines the expected output format of a field’s data value when it is printed or listed in Zim Reports, or by the list or output commands.

Mask patterns used with any of the three options described above are constructed in the same manner using the same set of available characters.

However, the set of characters available varies according to the data type of the item being masked. For more information on mask patterns and their relation to data types, see Masking.

Selected Examples of Mask Patterns for Numbers

In the following examples, leading or trailing spaces are indicated by / in the Output column.

Output of Sign Placeholders

Behaviour of Fixed Text Characters

Behaviour of Float Characters

Mask Patterns and the Language Customizer

Certain characters inserted into the output value by a mask are defined internally in the software.

The Language Customizer (ZIMLANG) administrative utility is used to redefine the internal characters and strings used by the software in a variety of circumstances, such as output masks.

For example, the currency placeholder $,  is by default the dollar sign $. However, it can be redefined to output other symbols, such as the Euro sign €, the pound sign  Â£, etc.

To achieve this, the Language Customizer can redefine the currency symbol output by the currency placeholder.

When using the Language Customizer to edit the software’s internal characters and strings, note the following:

• In application programs, output masks are always specified using the standard mask pattern characters. This rule applies to mask patterns specified in reports, in field and form field definitions, and in the $mask function.
• In user interface displays and output, the characters output by a mask pattern are determined by the software’s internal values.

For example, in Zim application programs, the dollar sign $ is universally used as the currency placeholder.

However, the currency symbol output by the placeholder depends on the current language customization (if any).

The Report Generator takes a report specification and creates a report for viewing or printing.

The elements that must or can be provided to the Report Generator in the report specification are

• set of records

• page structure

• report headings and footings

• page headings and footings

• break headings and footings

• item position and format

How To Construct Report Specifications

A report specification is a series of commands that describe the set of data to be used in the report, the values to be extracted from the data set, and the formatting to be applied to each value and to the report in general.

The general form of a report specification is

REPORT FROM…

report commands

ENDREPORT

Reports can be line-oriented or column-oriented in their formatting.

How To Use Report Commands

Between REPORT FROM, that initiates the report and specifies the set of database records on which the report is to be based, and ENDREPORT, that marks the end of the report specification, any meaningful combination of the following commands can be used.

REPORT HEADING, REPORT FOOTING

Identifies and formats the data that is to appear at the very start and end of the report.

Always line-oriented by default, even in column-oriented reports. Can be column-oriented if desired.

PAGE HEADING, PAGE FOOTING

Identifies and formats the data that is to appear at the top and bottom of each page of the report.

Always line-oriented.

PAGE LEFT, PAGE RIGHT

In column-oriented reports, specifies “headings” that are to appear at the left-hand and right-hand sides of each page of the report.

DETAIL LINE

Extracts and formats values from the set of records specified in the REPORT FROM command, and manages the headings for each set of values.

Obeys the orientation specified in the REPORT FROM command.

BREAK

Specifies the conditions for dividing detail lines into groups and subgroups, and specifies the headings and footings that are to be output for each group.

The break headings and footings obey the orientation specified in the REPORT FROM command. In column-oriented reports, break headings and footings can be line-oriented if desired. Break headings and footings can differ in orientation.

COLUMN BREAK

In column-oriented reports, specifies the headings and footings that are to be output each time DETAIL LINE values carry over to a new tier of the report.

How To Specify the Report Set

The set specification is specified in the REPORT FROM command.

Only those records that fit the set specification are reported. One DETAIL LINE is produced for each record. The records are reported from top to bottom in sequential order. Typically, therefore, the set specification includes a SORT clause to place the records into a logical order.

Sorting is essential if breaks are to be included in the report. (As each record is processed, the break indicator is checked to determine whether a break has occurred.)

How To Specify Report Headings and Footings

Report headings are specified in the REPORT HEADING command; report footings, in the REPORT FOOTING command.

Report headings are processed before any records identified by the set specification are read.

The items specified in the report heading are output only once, at the start of the report.

Report footings are processed after all records identified by the set specification are read.

The items specified in the report footing are output only once, at the end of the report.

How To Specify Page Headings and Footings

Page headings and footings are specified in the PAGE HEADING, PAGE FOOTING, PAGE LEFT, and PAGE RIGHT commands.

Page headings and footings are processed when a page boundary is reached.

The items specified in the page headings/footings are output on every page of the report. The values can vary, however, depending on the source of the items (e.g., the current member from the set specification) and their format (e.g., NOPRINT, SUPPRESS).

How To Specify Breaks and Break Headings and Footings

Breaks, with their associated headings and footings are specified in the BREAK (Reports) command.

Breaks are processed when the break indicator expression changes in value. Many break levels can be specified in one report. Break headings and footings are processed when a break occurs at the corresponding level.

The items specified in the break heading are output when the first record of the set specification is processed, and when the first record of each new break group is processed. The values can vary, however, depending on the source of the items (e.g., the current member from the set specification) and their format (e.g., NOPRINT, SUPPRESS).

The items specified in the break footing are output when the last record of each break group is processed, and when the last record of the set specification is processed. The values can vary, however, depending on the source of the items (e.g., the current member from the set specification) and their format (e.g., NOPRINT, SUPPRESS).

How To Specify Item Position and Format

The items output by the various report commands can be explicitly specified, drawn from the records identified in the set specification, or produced by the evaluation of an expression that involves groups of such values, plus operators and functions, as desired.

Each item is individually positioned and formatted using report format options.

Other formatting of items can be accomplished using character functions such as $trim and $concat.

How To Specify the Report Page Structure

The general structure of a report page is specified in the REPORT FROM command.

Structural details that can be controlled include the overall width and depth of the page, the four margins (top, bottom, left, and right), the spacing between subsequent items on the same line, and the orientation of the entire report (line-oriented or column-oriented).

Report format options describe where and how each report item is to be displayed and printed. Format options define the appearance of the report, its contents, and the position and appearance of its data, headings, and footings. Each data item in the report can be followed by any meaningful group of format options (See Conventions). Each group of format options begins and ends with a colon. Within the colons, each format options is separated from the next by at least one space.

Some format options are designed for text reports while other for graphical reports, sometimes both.

The following is the Report Format Options description for graphical reports, where “n” can be any positive integer:

The following is the Report Format Options description for text reports, where “n” can be any positive integer:

Format options can be used in any report command except REPORT FROM and ENDREPORT.

Using Report Format Options

General points to remember when specifying format options for a report item are the following:

• The first report item in a command always begins on a new line (i.e.: NEWLINE 1 is automatic) in a line-oriented report. Also, this report item begins in column 1, unless otherwise specified.
• All line and column positioning is processed before the report item itself is formatted.

Establishes the accelerators for the current window.

Syntax

WINDOW SET [ ADD|NOT] ACCELERATOR [ «keyname»]

Parameters

Comments

Each window has its own current list of accelerator keys. Before a WINDOW SET ACCELERATOR command is issued, no accelerator keys are in effect.

Any combinations of key names can be specified as accelerator keys. Mouse button names are valid key names.

If neither ADD nor NOT is specified, then keyname replaces the current list of accelerator keys. If no keyname is specified, then all accelerator keys are disabled.

An accelerator key causes an event that is intercepted by the program. Event.EventType is assigned “Accelerator”, and Event.EventName is assigned the name of the key.

Accelerator keys are ignored if the focus is in a menu.

See Also

FORM INPUT

MENU INPUT

ThisWindow

WINDOW OPEN

Saves the current form or display in its current state for later use in the current Zim window.

Note: This command is invalid in Zim version 5 and above.

Syntax

FORM SAVE

Comments

This command should be used to preserve only dynamic changes to form field attributes (made using FORM SET(Attributes) commands) across FORM OPEN commands in the same window, when the current form or display in its current state is required at some later time.

Many forms and/or displays can be saved. When the FORM SAVE command is executed, the current form or display is pushed onto the stack of previously saved forms. Afterwards, no form or display is considered to be open in the window.

Saved forms or displays can be “popped” off the “stack” one at a time, using the FORM RESTORE command, to become the current form or display.

Note: The FORM SAVE command must only be used with forms that are selected in text windows – never forms in graphical windows. Use of this command in Zim for Windows can cause system termination.

Example

form open fEmployees

form set (protected) fEmployees.Salary  %Salary protected from edit

form display input

… processing commands – error detected…

form save

form open fErrorMsg

form display noclear

… processing commands …

form open fEmployees NoClear           % Salary is still protected

form display

When an input error occurs, the form is saved in its current state, then an error message is displayed over the form on the screen. The form is later restored and displayed as it existed at the time of the FORM SAVE.

See Also

FORM CLEAR

FORM DISPLAY

FORM INPUT

FORM OPEN

FORM REPORT

FORM RESTORE

FORM SCROLL

FORM SET