Category: Getting Started

When an EntitySet or data relationship is created, it is declared to be a table managed either by Zim or by an SQL server. This declaration is done by the field EntType in the data dictionary EntitySet EntitySets or, for data relationships only, in the field RelType in the data dictionary EntitySet Relationships. This flag is checked to determine whether standard Zim code or SQL code (including SQL statements) should be generated when the object is referenced in a Zim command. The following tables indicate how to specify whether Zim or a SQL Server is managing an EntitySet or data relationship.

For example, if the field EntType in the definition of the EntitySet Customers contained the SAM name JDBCSAM, then the EntitySet Customers is a table managed by the JDBC SAM which in turn will handle data from this entity set that is located on a particular SQL database server.

If the field EntType or RelType is either blank or the value “ZIM”, the object continues to be managed by Zim; no other object definitions are required.

When the object is created, a check ensures that the SAM name in the field EntType or RelType is a valid server name.

The date mapping when using two heterogeneous database management systems (for instance Zim and Oracle or Zim and DB2) is a feature that enables date values referenced in Zim to be mapped into date values referenced by the server and vice-versa. This date mapping enables non-standard Zim date values to be represented on the server, so that the Zim application system does not change in functionality (that is changing the Zim code). For example, Zim can accept date values like “0” and “1” or even negative values (instead of the standard 20010101  for example) whereas the Server accepts only valid or null dates.

This date mapping is provided by the mapdate.cfg file, which converts Zim date values to valid Server date values and back again. Any time a Zim date value is sent to the Server, its corresponding value is checked in the table; if matching, this Zim date value assumes the corresponding server date value. Any time a date is requested from the Server, the opposite process is accomplished.

The mapdate.cfg configuration file is an ASCII file and should be located in the directory where the SAM has been installed. The mapdate.cfg file can contain any number of lines, each containing a pair of date values, comments, or both.

The pair of date values consists of a Zim date value and a server value, separated by a semi-colon (;). The format of a Zim date value follows Zim rules for dates (for instance 20010101, 0, 2) while the server date values must follow the format YYYY-MM-DD (i.e. 2005-12-31). If one of the date values is omitted, its corresponding value is assumed to be null.

The comments begin with a “%” and follow the same rules of Zim comments.

Use only 8 digits to represent the mapping date for the client. In the example below, a “0” in Zim is mapped onto 1001-01-01 on the database server.

An example of a mapdate.cfg file follows:

%This is date mapping for the accounting system.

%This is date mapping for the accounting system.
0;1001-01-1 % converts Zim dates containing zeros to this date on the server
1;1001-01-02
-1;1001-02-03 % any Zim date value can be used
; 3005-05-05 % null date values in Zim are converted to this date on the server
20031231;    % all Zim dates containing 2003/12/31 are converted to nulls

Use caution when implementing date mapping as shown in the last example, since all null dates on the server are converted back to 20031231.

To migrate a Zim host based application into a client/server application using a SQL-based server, you must prepare the data properly to ensure that the migration is successful.

Basically programs can stay as they are, but some fine-tuning is necessary for performance adjustments.

The basic steps are outlined below.

Model revision and adaptation

The first steps in migrating the data involve ensuring that the data model has been adapted to work in a client-server environment. This involves a number of individual steps.

Entity sets data relationships have a unique key: Ensure that all tables have at least one and only one primary key with the attributes UNIQUE or PRIMARY and REQUIRED. All data relationships must also have a primary key. Normally, data relationships do not have unique keys. If necessary, create a virtual field concatenating the primary keys of related EntitySets.

A Zim virtual field can be used as the primary key for a table. In this case, its field expression (Default Value) should contain only the concatenation of other fields. No other function or expression is permitted for SQL restrictions.

An error occurs during the export process if an attempt is made to export an indexed virtual field whose expression does not abide by this rule.

Table Naming

Any tables whose name starts with the symbol (underscore) must have its name changed. SQL servers do not permit tables to have names starting with this character.

Numeric Fields

Numeric fields of data type Int, Longint and Vastint which have decimal places need to be altered as well. The length specified for these types of fields should be the maximum allowed; use 4, 9, and 15 for Int, Longint, and Vastint fields respectively.

Table Relationships

Joins between tables located on the server and on the client can be created in Zim, however, a performance degradation occurs as a result of these types of relationships. Avoid this type of construction, and if possible, change these types of relationships before migrating the data.

If per-user EntitySets have relationships with tables located on the server side, reallocate the per-user EntitySets to the server side as well. Ensure that you modify the primary key to indicate the user identification.

Relationships between EntitySets and documents can cause performance problems when the document is located on the client side and the entity set on the server side. Copy the document to the server side before executing any command that relates the document and the table.

Ensure that there are no relationships between EntitySets and forms. If any relationships of this type exist, change them, as they can result in application malfunctions.

Table creation on the server

1. Use the DC to generate the Data Definition Language (DDL) for the table creation on the server and create them.
2. Using server tools, create the reference integrity rules for each index, where applicable.
3. For each Zim virtual field whose definition is not a simple field concatenation, create a real field and a trigger on the server, in order to evaluate it.

Table creation on the client side

1. Change the table type of all EntitySet tables and data relationships tables to the name of the server (e.g. Oracle).
2. Fine-tune the application, looking for performance bottlenecks. Run the main programs (those that are most critical,  most used, or both) of the application. Ensure that the  trace feature is enabled, in order to be able to visually inspect points where the trace encounters problems. If the trace stops, a performance problem is likely the issue. Ensure that you verify these points to enhance performance.
3. Where possible, combine multiple find commands (such as find .. -> set1, find set1 …-> set2 and so on) into single commands. This type of change decreases traffic between the client and the server.

Exporting Zim definition to SQL

1. The DC Export to SQL tools export and create the SQL tables on the server side. The DC Export also generates a file containing the SQL syntax (such as CREATE TABLE) to create the tables and, using the SQLEXEC command, sends and executes the script on the server side.
2. The CREATE TABLE syntax for a specific SQL server can include additional syntax that relates to the physical storage characteristics of the created table. This syntax varies widely from server to server. If you want to include the specification of some or all optional physical table characteristic parameters, enter the appropriate syntax in the field “Physical Table Characteristics Syntax:”. The syntax specified is appended to the CREATE TABLE statement for each name entered in the form.

For example, entering

   “TABLESPACE ts_account”

ensures that the table is created in the “ts_account” table space.

3. The SQLEXEC traces the SQL statements that it is executing, as well as identifying any error conditions. In general, the only error condition that is acceptable is an error from the DROP TABLE statement when the table does not exist. All other error conditions should be investigated. The SQLEXEC does not stop executing on encountering an error condition.

The Purpose of Masking Patterns

Masking patterns are used to format data values in a manner appropriate to the type of information they represent.

For example, in Zim, dates are stored as a special type of numerical value, in which the first four digits represent the year, the next two represent the month and the last two stand for the day.

As such, the date January 01, 2020 is stored numerically as 20200101. This allows for mathematical operations to be performed on dates, but this format is not intuitive for presentation.

By using a masking pattern, such as “DD/MM/YYYY”, this date value will now be presented as the string “01/01/2020”, which is instantly recognized as a date.

The same masking pattern is also used to format the input of data into a date entry field. Instead of typing 20200101, the end-user can type 01012020, because the separator character / is only presented between the year, month and day values, but is not actually part of the date value itself.

The same principle can be applied to separator characters in currency values, postal codes, phone numbers, etc.

Situations in Which Masking Patterns Are Used

Zim offers the possibility of applying data masking patterns in different situations, each with a specific set of fixed and masking characters, as summarized in the table below (follow the links in the first columns for more information):

Masking Characters According to Data Types

The set of available masking characters is different according to the Data Type property (DT) of the entity set field or the entry field.

The following sections describe the available masking characters for each data type:

Alphanumeric Masking

Alphanumeric masking is used to format generic text data. It is particularly useful when data is entered in shortened form but displayed in full form.

In the output value, each question mark is replaced by one character from the current value of the report item, in order from left to right.

When no more characters remain in the value, any remaining question marks become spaces.

Alphanumeric Masking Characters

Examples

Field Mask      MASK      $mask

Leading or trailing spaces are indicated by / in the Output column.

Data Mask

Note:

If the CM (compress status) attribute of a form field is N, the value of the form field is the same as the displayed data. Otherwise, the value of the form field is the same as the input data vlue.

lf the mask pattern is shorter than the field length, then the mask is assumed to contain ? characters in the missing positions (See the last example above).

Non-Digit Masking

Non-digit mask patterns are nearly identical to alphanumeric ones, with the exception that digits (0, 1, …, 9) are not accepted as valid input.

Date Masking

Date masks are composed of three types of character groups, representing the year, month and day, which can be combined in any order.

Between each group there may be an optional fixed separator character, such as /, –, ., or blank space, which appear in the display or output as is.

For date entry fields, the default masking pattern is MM/DD/YY.

Date Masking Characters

Examples

Field Mask      MASK      $mask

Leading or trailing spaces are indicated by / in the Output column.

Data Mask

Numeric Masking

When the data item contains decimals, the decimal point in the mask pattern is aligned with the decimal point in the value, and digit substitutions are made from right to left for the mask characters to the left of the decimal point, and from left to right for the mask characters to the right of the decimal point.

A series of 9s can therefore be used to force the printing of non-significant zeros to the right of the decimal point (in dollar values, for example). A 9 would also be used to force the printing of leading zeros in serial numbers: for example, 009876.

Any report item whose current value is too large to fit in the specified mask pattern (including the sign output, if applicable) causes the overflow characters (asterisks) to be displayed.

• the currency placeholder ($) and sign placeholders: plus (+), minus (-), left and right parentheses (( and )), and the special character strings CR and DB

The currency placeholder inserts the applicable currency symbol into the output value.

Sign placeholders reflect the sign of the number (positive or negative) in the output value. The left parenthesis can be used only on the left-hand side of the mask pattern. The CR, DB and left and right parenthesis can be used only on the right-hand side of the mask pattern. The plus and minus signs can be used on either side of the mask pattern.

The plus and minus signs, left parenthesis, and the dollar sign can be used as float characters. A single plus, minus, dollar sign, or left parenthesis can also “float” ahead of a series of other floating characters.

• fixed text characters

Typically, in the output value, fixed text characters (e.g., commas) appear as is, provided that there are digits remaining to be substituted into the mask or that the mask character 9 forces the output of zeros.

Numeric Masking Characters

Examples

Field Mask      MASK      $mask

Leading or trailing spaces are indicated by / in the Output column.

Data Mask

The Purpose of Masking Patterns

Masking patterns are used to format data values in a manner appropriate to the type of information they represent.

For example, in Zim, dates are stored as a special type of numerical value, in which the first four digits represent the year, the next two represent the month and the last two stand for the day.

As such, the date June 28, 2014 is stored numerically as 20140628. This allows for mathematical operations to be performed on dates, but this format is not intuitive for presentation.

By using a masking pattern, such as “DD/MM/YYYY”, this date value will now be presented as the string “28/06/2014”, which is instantly recognized as a date.

The same masking pattern is also used to format the input of data into a date entry field. Instead of typing 20140628, the end-user can type 28062014, because the separator character / is only presented between the year, month and day values, but is not actually part of the date value itself.

The same principle can be applied to separator characters in currency values, postal codes, phone numbers, etc.

Situations in Which Masking Patterns Are Used

Zim offers the possibility of applying data masking patterns in different situations, each with a specific set of fixed and masking characters, as summarized in the table below (follow the links in the first columns for more information):

Masking Characters According to Data Types

The set of available masking characters is different according to the Data Type property (DT) of the entity set field or the entry field.

The following sections describe the available masking characters for each data type:

Alphanumeric Masking

Alphanumeric masking is used to format generic text data. It is particularly useful when data is entered in shortened form but displayed in full form.

In the output value, each question mark is replaced by one character from the current value of the report item, in order from left to right.

When no more characters remain in the value, any remaining question marks become spaces.

Alphanumeric Masking Characters

Examples

Field Mask      MASK      $mask

Leading or trailing spaces are indicated by / in the Output column.

Data Mask

Note:

If the CM (compress status) attribute of a form field is N, the value of the form field is the same as the displayed data. Otherwise, the value of the form field is the same as the input data vlue.

lf the mask pattern is shorter than the field length, then the mask is assumed to contain ? characters in the missing positions (See the last example above).

Non-Digit Masking

Non-digit mask patterns are nearly identical to alphanumeric ones, with the exception that digits (0, 1, …, 9) are not accepted as valid input.

Date Masking

Date masks are composed of three types of character groups, representing the year, month and day, which can be combined in any order.

Between each group there may be an optional fixed separator character, such as /, –, ., or blank space, which appear in the display or output as is.

For date entry fields, the default masking pattern is MM/DD/YY.

Date Masking Characters

Examples

Field Mask      MASK      $mask

Leading or trailing spaces are indicated by / in the Output column.

Data Mask

Numeric Masking

When the data item contains decimals, the decimal point in the mask pattern is aligned with the decimal point in the value, and digit substitutions are made from right to left for the mask characters to the left of the decimal point, and from left to right for the mask characters to the right of the decimal point.

A series of 9s can therefore be used to force the printing of non-significant zeros to the right of the decimal point (in dollar values, for example). A 9 would also be used to force the printing of leading zeros in serial numbers: for example, 009876.

Any report item whose current value is too large to fit in the specified mask pattern (including the sign output, if applicable) causes the overflow characters (asterisks) to be displayed.

• the currency placeholder ($) and sign placeholders: plus (+), minus (–), left and right parentheses (( and )), and the special character strings CR and DB

The currency placeholder inserts the applicable currency symbol into the output value.

Sign placeholders reflect the sign of the number (positive or negative) in the output value. The left parenthesis can be used only on the left-hand side of the mask pattern. The CR, DB and left and right parenthesis can be used only on the right-hand side of the mask pattern. The plus and minus signs can be used on either side of the mask pattern.

The plus and minus signs, left parenthesis, and the dollar sign can be used as float characters. A single plus, minus, dollar sign, or left parenthesis can also “float” ahead of a series of other floating characters.

• fixed text characters

Typically, in the output value, fixed text characters (e.g., commas) appear as is, provided that there are digits remaining to be substituted into the mask or that the mask character 9 forces the output of zeros.

Numeric Masking Characters

Examples

Field Mask      MASK      $mask

Leading or trailing spaces are indicated by / in the Output column.

Data Mask

If there is to be more than one Zim application compiled against a given SQL server database, it is best to uniquely identify each application. Conflicts can occur between programs with the same name. An application can be flagged with an ID by including the following entry in the database configuration file (zimconfig.zim):

SQL DATABASE NAME xxx

where xxx is a three-character ID differentiating Zim programs from different Zim databases compiled against the same SQL server database.

If the Zim application already exists, then some modifications to the data dictionary definitions for EntitySets and data relationships must be performed. Specifically, the field “EntType” in the data dictionary EntitySet “EntitySets” and/or the field “RelType” in the data dictionary entity set “Relationships” must be flagged as residing on the SQL server. These fields should be set to the name associated with the server access module and the objects must be re-created.

If the application is to be developed for execution against a pre-existing SQL server database then it is likely that the table definitions have been loaded into the Zim data dictionary from the SQL server. The “EntType” fields should be set to the name associated with the server access module being used, and the objects recreated. Take advantage of Zim’s ability to access local data objects.

EntitySets and data relationships in Zim can also be defined to map to SQL “views”. Again, the Zim field list need not include all columns of the view, nor need the field sequence correspond to that of the columns of the view. However, if the corresponding EntitySet or data relationship must be updated, then the SQL view must be classified as being able to be updated according to the rules of the SQL server being used. This means that the definition of the view must not include the DISTINCT operator or the ORDER BY or GROUP BY clauses, that the view is defined over a single table only (no multi-table joins), and that columns of the base table which are not in the view must permit NULL values. These are typical limitations of SQL database servers. These restrictions on views are such that it is more practical to use base tables rather than views.

The mapping between an SQL table and a Zim EntitySet or data relationship need not be complete with respect to the set of columns owned by the table. Zim retrieves only the columns required from an SQL table; it generates the minimal “select-column-list” to satisfy the requirements of the corresponding Zim command. The Zim Definition Interface imports all the columns of an SQL table translated into Zim field definitions, but only those fields relevant to the application being developed need be used. Also, the defined sequence of the fields in a Zim EntitySet or data relationship need not correspond to that of the columns of the SQL table to which it maps.

It is important to note that if Zim “ADD” commands are to be executed against an SQL table whose Zim field list is a subset of the complete table column list, then those table columns which are not included in the Zim definition must enable the value NULL. An “INSERT” statement generated by Zim cannot specify values for columns about which it has no knowledge.

Database systems, including Zim, have rules as to how objects such as database tables and columns (EntitySets and fields and Zim) are named. In Zim, object names must be 1 to 18 characters in length and start with an alphabetic character, an underscore (“_”), or dollar sign (“$”). If the name of a SQL table to which you wish to map an EntitySet is invalid in Zim or the name of an EntitySet which you wish to export to a SQL server is invalid on the server, you can specify an alternate name in the “Remote Name” field of EntitySets, Relationships, or Fields. All generated SQL syntax uses the specified Remote Name. There is no need to specify a Remote Name if the Zim name and the SQL name are identical.

For example, some SQL servers enable table and column names with embedded blanks if the name is enclosed in double quotation marks, which is illegal syntax in Zim. In such cases, specify a Remote Name with the server table name in double quotation marks so that the correct SQL server syntax is generated.