Table Fields

Each database table contains fields (columns) to store the object-specific attributes. You create these fields during model definition in the Table Fields sub-lists of the corresponding model object form. In doing so you assign field attributes that define essential properties of the field in the database table.

The syntax for all field names is TABLENAME.COLUMNNAME. For COLUMNNAME C_NAME is recommended in order to prevent conflicts with protected field names of the connected database (-> cf. database documentation).

Note! When multilingual fields are created, DataView creates field names with language ID, such as TABLENAME.COLUMNNAME _ENG. Do not use such a syntax for normal monolingual table fields.


Sequence of Fields in Tables

The sequence of fields (columns) in a database table is determined in the Sequence field of the table-field assignment. Actually, the sequence of fields in the database is without any consequence. However, because it is used for creating masks it can be helpful to enter the desired sequence here.


Field Types

Data type and length of table fields are determined in the Type field of the table-field assignment. DataView automatically imports the type as master attribute into the field master. Lengths can only be entered for the field types Float (F), Integer (I), Money (M) and String (S), where the length information for Float and Money is extended by the number of decimal digits. After selecting a type identifier DataView automatically enters a type-dependent default length for these field types. The default lengths can be user-specifically set in the default variables FLOAT, INTEGER, MONEY and STRING. The date format can be set using the default variable DATAFORMAT. The following field types are available:

Data Type Explanation
B -> Binary Field of the BLOB type (Binary Large Object). In this field binary files can be directly stored in the database. No length specification is possible.
D -> Date Field of the type Date for date and time information, e.g. 17.05.1994 12:20:31. The mask date format can be language-specifically set via the default variable DATEFORMAT, the database date format depends on the connected database . Length cannot be specified, the internal default is 19.
F -> Float Field of the Float type with decimal point, e.g. 350.12 or -350.12. Default value is F 10.3 (with 10 = total number of digits before the decimal point, 3 = number of digits after the decimal point).
Alternatively the exponential representation is possible, e.g. 3.5012e-002.
If auto correction is set: comma -> point (for German users).
The number of digits for real numbers is now limited to F38.37.
I -> Integer Field of the Integer type, e.g. 350 or -350. Default value is I10.
L -> Logical Field of the Boolean type. It can only take the values ON, OFF, or EMPTY. No length specification is possible.
M -> MONEY Field of type Money for currencies, e.g. 350.00 Euro. Default value is M10.2 (with 10 = total number of digits before the decimal point, 2 = number of digits after the decimal point).
If auto correction is set: point -> comma.
S -> String Field of type String for text input of any characters in the 8-Bit ASCII format. Default value is S20. String type fields with more than 255 characters are Longtext fields.


BLOB Fields

With the help of BLOB fields you can directly store binary data in the database. For BLOB fields the following rules and limitations apply:


Longtext Fields

Fields of the string type with the length longer than 255 characters are Longtext fields. When using Longtext fields you should observe the following special features:


Date Fields

Date fields in the database are generally stored in the database date format. As opposed to that there is the mask date format. It is used to enter and display date values in masks. Any required conversion for write and read operations are automatically done.

Database date format
Format depends on the database, but usually YYYY-MM-DD HH:MM:SS (each placeholder stands for a digit).

Mask date format
Is language-specifically set using the default variable DATEFORMAT_LNG. The default for English is M/D/Y H:M:S (each placeholders stands for a two-digit numeral value, only the year (Y) can have either two or four digits). All six numeral values for day, month, year, hour, minute, and second must exist. Any single character (but no digit) can be used as delimiter. First the day, month and year must be specified in any sequence followed by hour, minute, and second in any sequence.

After a date has been entered in a mask date field, DataView automatically checks for conformity with the mask data format defined and corrects the entry according to the following rules:

You can determine the part of the date to be displayed via the field width of a mask date field. A width = 8 shows 17.05.94, and the width = 10 results in 17.05.1994, and the width = 16 results in 17.05.1994 12:20.

Attention! Since the year-conversion only mask fields with at least ten digits are supported for date values. Eight-digit date fields (previously used for the date with a two-digit year number) are not allowed any longer.

In order to pre-assign date fields with the current date the macro @TODAY can be used. It returns the date string in the defined mask data format.


Integer Fields

EPSQL limits the range of integer values to [-2147483648, 2147483647] when they are written into the database. If for that reason a validation of field contents is required in your application, as a customizer to you can:

When you read from the database, DataView can also display larger values.


Multi-Lingual Table Fields

Information on multilingual table fields can be found in chapter -> Language Management.


Indexed Fields

In the Index 1-3 fields of the table-field assignment you can index fields. By indexing you accelerate queries in a field, because the database provides the field entries in the database in a sorted order. This advantage is only fully effective if users use search terms without any wildcards (% or ?).

The syntax for index names is TABLENAME_INDEXNAME. For INDEXNAME the form S_NAME is recommended, in order to prevent conflicts with protected index names of the connected database (-> cf. database documentation). Under no circumstances you should use SQL keywords or names of existing database objects (table/view names) as index names.

By assigning identical index names fro several fields you form a combined index and accelerate simultaneous queries in several fields. Additional indexes allow you for instance to assign an additional query index to key fields which are part of a combined key.

In addition to fields that are relevant for queries, you have to index all unique fields. For instance these are significant attribute fields in DataView tables or key fields in foreign format tables. Multi-lingual fields cannot be indexed.


Unique Fields

A field defined as unique (column) within a table has the effect that the fields of this column can only contain different data. If you use a unique fields in masks, entering an existing entry will result in an error message. Thus every record in the table can be unambiguously referred to.

You should specify significant attribute fields in DataView tables (only if uniqueness is desired, e.g. for number fields) or key fields in foreign tables (absolutely required) as unique. Several unique fields in one table result in a combined key. Please note that all unique fields must be indexed. In case of combined keys they must use identical index names.

Unique fields are identified by the uniqueness flag unique 1-3 of the table-field assignment. In addition to the global uniqueness over the total table you can select several combined keys consisting of a field to be defined as unique and DataView-internal C_ID fields there in order to assure partial uniqueness in the table for special tasks. This may be required e.g. to allow multiple relations between parent and child elements in relation tables or to create 1:n type relations. The combinations offered for the uniqueness flag depend on the kind of table:

Table Uniqueness Explanation
Master Table G -> Globally unique Global uniqueness for all records in the master table
- -> Not unique Default setting
Type Table G -> Globally unique Global uniqueness for all records in the type table irrespective of the parent element they point to
2 -> Id-2 unique Partial uniqueness for all type records with identical parent element (unique field and C_ID2 form a combined key)
- -> Not unique Default setting
Relation Table G -> Globally unique Global uniqueness for all records in the relation table irrespective of the parent or child element they point to
1 -> Id-1 unique Partial uniqueness for all relation records with identical parent element (unique field and C_ID1 form a combined key)
2 -> Id-2 unique Partial uniqueness for all relation records with identical child element (unique field and C_ID2 form a combined key)
R -> Id-1/2 unique Partial uniqueness for all relation records with identical parent- and child element (unique field, C_ID1 and C_ID2 form a combined key)
- -> Not unique Default setting


Non-Zero Fields

A non-zero field within a table forces an entry to be made which may not be ZERO. If you use such a field in masks, the entry will be forced by setting field access for this field to m = mandatory (-> mask-field relation, Field Access).

The non-zero flag should be set for all fields that are responsible for a unique identification of records. These are especially significant attribute fields in DataView tables or key fields in foreign format tables.


Field-Specific Query Mode for Table Fields (Case Sensitivity)

The case sensitivity of Canaries can be set in the Mode field. Possible entries are:

Entry Comment
(empty) No field-specific case sensitivity (default)
I INSENSITIVE, uppercase/lowercase is ignored
S SENSITIVE, uppercase/lowercase is observed

If field-specific case sensitivities are defined, any sensitivity defined via the default variable QUERYMODE will be ignored for the corresponding fields.