Database tables - configuration dialog box

Editing of all objects in the D2000 CNF is performed in the configuration dialog box, a specific part of which is common for all editable objects, and another part depends on the type of edited object.

The configuration dialog box of objects of Database table type consists of several parts (tabs), which contain similar parameters.

• General properties

• Groups

• Table

General properties

---

Description

A text string describing the database table. Maximum: 128 characters.
Possibility to use the Dictionary (to open, press CTRL+L).

Table

---

Structure type

An object of the Structure definition type, which defines the structure type. If the database table is being used, then it is not possible to change the structure type.

The New button enables to creation of a new Structure definition according to the table definition in the database. The name of the Structure definition is derived from the name of the DB Table object in the D2000 System.
(The relevant system process D2000 DBManager must be running when creating the new definition.)

States when creating the new structure definition

• Definition is not in the system - the system requires the definition of the database table and creates the new Structure definition. After saving the definition, the system ensures the connection of the Structure definition to the DB Table.

• Definition is in the system - after confirming, the system ensures the connection of the Structure definition to the DB Table.

Conversion table

Mapping columns between the table in the database and the columns in the structure definition is carried out by name. When generating SQL D2000 commands, D2000 DBManager captures names into quotation marks by default. In some cases, this is unwanted activity, so the D2000 DBManager process have the /NQ parameter.

Access

Selection of access rights to the database.

• None - no access to the database

• Read only - the database cannot be modified (only read)

• Modify - the database can be read and modified

Table

Name of the table in the database. This name can be either simple (e.g., table1) or compound. A compound table name consists of dot dot-separated user name and a simple name of the table (e.g., user1.table1). Compound names are supported by e.g., MsSql, Oracle, and Sybase, simple names are required by e.g., Microsoft Access databases and MySql. The following rules for working with the table apply:

• If the name is simple, it is automatically extended with the user name (the parameter User) defined for the parent object of Database type. If the user name is not configured, the simple name of the table is used.

• If the name is compound, it is directly used.

• If the name is in the form of .table1 or "".table1, the simple name table1 is used, and it is not extended with the user name (the parameter User) defined for the parent object of Database type.
  Note: This rule is not valid for dbmanager_ora.exe, which always uses the compound name of the table.

Note: The name of the table (including the name of the user separated by a comma) can be up to 64 characters. The length of the table name depends on the particular database.

Index

Column (columns), which is (are) regarded as a key item. A key item is an item that uniquely defines a row in the database. The list of possible key items is equal to the names of columns according to the Structure definition. The parameter is optional.

Optional

The column (columns) that is (are) considered to be optional. The optional column doesn't have not exist in a database. The list of possible optional columns is equal to the columns in the Structure definition. To verify the existence of required columns in the database table (i.e., all that were not marked as optional), use the Test button for the object of Table type or the Test table for the object of Database type.

Note: Just as some columns may be present in the Structure definition and not exist in the database table, there may also be columns that exist in the database table and are not defined in the Structure definition. Even one database table can be represented by multiple Table objects, each of which uses a different Structure definition. Each such Table object can be used in a different case (e.g., sometimes I need to retrieve only the ID and name, sometimes the basic list of columns, and sometimes all of them). Using a Table object with a reduced list of columns speeds up data work (fewer items of the structured variable are retrieved and transferred).

Not Null

Column (columns) that is (are) considered to be NOT NULL.
The value of a NOT NULL column must be defined before inserting or modification in the database table (the operation to insert or modify the record tables). The list of possible NOT NULL columns is equal to the columns defined in the Structure definition. If there are undefined values in these columns before inserting or modifying tables, the action is terminated with an error. All the values that do not comply with the NOT NULL condition are listed; however, at most 10 for 1 column (it is in contrast with the DB engine - it returns only the first conflicting column in the first conflicting row).

Example of error message, which is displayed in DBManager:

%D2DBM-E-*** Error in con 1:
%D2DBM-E-con 1: DBS_INSERT : Column "column1" [row # 7], "column3" [row # 3 7 8], "column5" [row # 1 2 3 4 5 6 7 8 9 10 ...] in table "dba"."test_js_column_multi" cannot be NULL!

Data category