If you have an existing SQL database, you can use Dexterity to create a dictionary to access the data in that database. However, you must be sure to correctly name and define your data types, fields, tables and paths. If you don’t define your dictionary’s resources properly, you won’t be able to access the existing data. The following sections provide information you will need to define your dictionary’s resources properly.
A Dexterity table must be defined for each existing table in the database. Use the existing table’s name as the physical name in the Dexterity Table Definition window. Be sure to include a table field for every column in the existing table.
A field must be defined in Dexterity for each unique column in the existing database. Be sure to use the column name as the field’s physical name in the Field Definition window. Dexterity doesn’t allow you to enter an underscore in the physical name field. If an existing column name contains an underscore, use a space in its place when defining the column’s related field. For example, “Last Name” should be entered as the physical name for the field defined to correspond to the “Last_Name” column. Dexterity automatically converts the space into an underscore.
The Dexterity data types defined for the fields must have control types that correspond to the SQL data types defined for the columns in the existing tables. For example, a column in an existing table that is defined as a SQL integer must be defined as a long integer field in Dexterity.
Be sure that the keyable length of the data type defined for use with a given field is the exact length of the field’s corresponding SQL column. If a field’s keyable length is less than the actual length of the related SQL column, existing data will be truncated when it’s displayed and saved using Dexterity.
Special care must be taken when defining the keyable length of string and text data types. The keyable length of a SQL char field is always the same as its storage size. In contrast, Dexterity string and text fields have length bytes added to their keyable length. One byte is added for string fields; two bytes are added for text fields. Since all SQL databases allow odd-length fields, you may want to select the Allow Odd Length Strings option in the Data Type Definition window when defining string or text data types in Dexterity.
For example, a SQL char field of keyable length 50 has an actual storage size of 50 bytes, while a Dexterity string field with a keyable length of 50 will have a storage size of 51, if odd-length strings are allowed. If you don’t select the Allow Odd Length Strings option, a Dexterity string field of keyable length 50 will have a storage size of 52 bytes. The extra two bytes consist of a length byte and a pad to ensure an even-numbered storage size.
Dexterity offers several different control types for you to base your data types upon, such as string, integer and currency. When you define a data type, you choose a control type that specifies how information will be stored and displayed in fields using that data type.
When you write SQL stored procedures, you use SQL data types to define the types of fields being specified. It’s important that the SQL data type defined for a field in the stored procedure corresponds to the Dexterity control type of that field in your application.
The following table lists the Dexterity control types and their corresponding SQL data types. Button drop lists, push buttons and radio buttons aren’t included in this list. They aren’t stored in your application’s tables, and therefore don’t have a corresponding SQL data type.
![[spacer]](1p.gif){kind=small}
|
Dexterity control type |
SQL data type |
|---|---|
|
Boolean (2 bytes) |
tinyint (1 byte) |
|
Check box (2 bytes) |
tinyint (1 byte) |
|
Combo box (Keyable length + 1 + pad if not even) |
char (Length + 2 bytes) (Length < 256 bytes) |
|
Composite (Total of components) |
Storage type of each component. Each component is stored as its own column in the table. The column name is the field physical name followed by an underscore (_) and then the number of the component. |
|
Currency (10 bytes) |
numeric(19,5) (9 bytes) The currency value can be up to 19 digits long, including five decimal digits. |
|
Currency (variable) (8, 10 or 12 bytes) |
numeric() The total number of digits and the number of decimal digits is specified by the data type. The total number of digits can be up to 23, with up to 15 decimal digits. |
|
Date (4 bytes) |
datetime (8 bytes) The time portion of the datetime is set to 12:00 AM. |
|
Drop-down list (2 bytes) |
smallint (2 bytes) |
|
Horizontal list box (2 bytes) |
smallint (2 bytes) |
|
Integer (2 bytes) |
smallint (2 bytes) |
|
List box (2 bytes) |
smallint (2 bytes) |
|
Long integer (4 bytes) |
int (4 bytes) |
|
Multi-select list box (4 bytes) |
binary(4) (4 bytes) |
|
Non-native list box (2 bytes) |
smallint (2 bytes) |
|
Picture (Variable) |
image (Variable up to 2,147,483,647 bytes) |
|
Radio group (2 bytes) |
smallint (2 bytes) |
|
String (Length + 1 + pad if not even) |
char (Dexterity storage size - 1) (Length < 256) |
|
Text (Length + 2 + pad if not even) |
char (Variable up to 2,147,483,647 bytes) |
|
Time (4 bytes) |
datetime (8 bytes) The date portion of the datetime is set to 1/1/1900. |
|
Visual switch (2 bytes) |
smallint (2 bytes) |