Tables - Configuration Dialog Box
Database tables - configuration dialog box
Editing of all objects in the process D2000 CNF is being 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.
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 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 create a new structure definition according
to the table definition in the database. The name of structure definition is
derived from the name of DB Table object in 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 database table and creates the new structure definition. After saving the definition, the system ensures the connection of definition to the DB Table.
Definition is in the system - after confirming, the system ensures connection of definition to the DB Table.
Conversion table
D2000 types | ODBC | OCI |
|---|---|---|
BOOL | SQL_BIT |
|
INT | SQL_INTEGER, SQL_NUMERIC, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT | SQL_INTEGER, SQL_NUMERIC, SQL_BIGINT, SQL_SMALLINT, SQL_TINYINT TYPE_NUMBER, TYPE_INTEGER, TYPE_UNSIGNED8, TYPE_UNSIGNED16, TYPE_UNSIGNED32, TYPE_SIGNED8, TYPE_SIGNED16, TYPE_SIGNED32, TYPE_SMALLINT |
REAL | SQL_DECIMAL, SQL_FLOAT, SQL_REAL, SQL_DOUBLE, SQL_NUMERIC | TYPE_FLOAT, TYPE_DECIMAL, TYPE_REAL, TYPE_DOUBLE, TYPE_NUMBER |
TEXT | SQL_CHAR, SQL_VARCHAR, SQL_WCHAR, SQL_WVARCHAR | TYPE_VARCHAR, TYPE_VARCHAR2, TYPE_CHAR |
TIME | SQL_DATE, SQL_TIME, SQL_TIMESTAMP | TYPE_DATE, TYPE_TIME, TYPE_TIME_TZ, TYPE_TIMESTAMP, TYPE_TIMESTAMP_TZ, TYPE_INTERVAL_YM, TYPE_INTERVAL_DS, TYPE_TIMESTAMP_LTZ |
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. Compound name of table consists of dot separated user name and simple name of 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. There are following rules for working with table:
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 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 table.
Note:
The name of table (including the name of user separated by comma) can be up to 64
characters. The length of table name is given by a particular database.
Index
Column (columns), which is (are) regarded as a key item. Key item is such an item, which uniquely defines a row in the database. The list of possible key items is equal to the names of columns according to Structure definition. The parameter is optional.
Optional
Column (columns) that is (are) considered to be optional. The optional column need not exist in a database. The list of possible optional columns is equal to the columns in Structure definition. To verify the existence of required columns in the database table (i.e. all that were not marked as optional), use Test button for the object of Table type or Test table for the object of Database type.
Not Null
Column (columns) that is (are) considered to be NOT NULL.
The value of
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 Structure
definition. If there are undefined values in these columns before inserting
or modifying of tables, the action is terminated with 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 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!