Field Type Use Recommendations
If you want to define a new field into the LANSA dictionary use this decision tree:
- Is the field a Date or a Time?
- To store a unique day use type Date.
- To store a unique moment in time use type DateTime (in preference to two separate Date and Time fields).
- To store a non-unique time, which is unusual, use Time.
- Is it a True/False value? If so, use Boolean.
- Is it a number? If there are no decimal places use type Integer otherwise use type Packed.
- Then it must be a string of characters or some other stream of bytes.
- Is it normally kept in a specialized type of file or document (eg: an image, a sound, an MS-Word document or an XML document)? Use type BLOB.
- Do you expect it to be subjected to normal code page conversions? If not, use VarBinary unless your data is always the same length, in which case you may choose to use Binary.
- Will the field be 256 bytes or less and be best stored as a fixed length field? If yes, and it only needs to support one language at execution time, use type Alpha.
- Will it ever be longer than 65535 bytes? Use type CLOB.
- Do you store the field as variable length with any trailing blanks significant (for example, included in field concatenation operations)? If it only needs to support one language at execution time, use type String. Otherwise, use type NVarChar.
- Otherwise it is fixed length. If it only needs to support one language at execution time, use type Char. Otherwise, use type NChar.
All other types are provided for compatibility with non-LANSA database tables. Generally you should avoid using them when defining new fields or columns in new DBMS tables.
Commonly Used Field Types
|
Type
|
Description
|
Typical DBMS Storage Type
|
Maximum Dictionary Definable Length
|
Subject to Code Page Conversions
|
Notes
|
Useable in RDML or RDMLX Programs
|
|
Integer
|
Integer
|
INTEGER
|
4 bytes.
|
|
Hardware and compiler behaviors may vary.
|
RDMLX
|
|
Packed
|
Standard decimal number
|
DECIMAL
|
RDMLX programs: 63 digits of which up to 63 may be decimals
RDML programs: 30 digits of which up to 9 may be decimals
|
N/A
|
|
Both
|
|
String
|
Variable length alphanumeric string with a dictionary defined maximum length.
|
VARCHAR (variable length)
|
65535
|
YES
|
Not padded with blanks to the dictionary defined maximum length, except that a zero length string has 1 space added.
|
RDMLX
|
|
VarBinary
|
Exactly the same as String but never subjected to code page conversions.
|
VARBINARY
|
32767
|
NO
|
Not padded to the dictionary defined maximum length.
|
RDMLX
|
|
NVarChar
|
Similar to String but data is handled as Unicode. This allows multiple languages to be used at execution time.
|
NVARCHAR
|
65535 characters
|
YES (but only when converted to native String)
|
|
RDMLX
|
|
Alpha
|
Constant length alphanumeric string with a dictionary defined maximum length.
|
CHAR (fixed length)
|
256
|
YES
|
Always padded with blanks to the dictionary defined maximum length.
|
Both
|
|
Date
|
Date in ISO format: YYYY-MM-DD
|
DATE
|
N/A
|
N/A
|
|
RDMLX
|
|
Time
|
Time is ISO format HH:MM:SS
|
TIME
|
N/A
|
N/A
|
|
RDMLX
|
|
DateTime
|
Date and Time in ISO format YYYY-MM-DD HH:MM:SS[.fffffffff] where the existence and length of the [.fffffffff] portion are definable.
|
DATETIME
|
29
|
N/A
|
|
RDMLX
|
|
Boolean
|
True/False
|
Decimal
|
N/A
|
N/A
|
|
RDMLX
|
|
Specialized Character and Binary Types (useable only in RDMLX programs)
|
Type
|
Description
|
Typically Stored in a DBMS as
|
Maximum Dictionary Definable Length
|
Typical DBMS Storage length used
|
Subject to Code Page Conversions
|
Important
|
|
Char
|
Exactly the same as String, except fixed length and DBMS storage implementation is usually CHAR.
|
CHAR
|
65535
|
Fixed length
|
YES
|
|
|
Binary
|
Exactly the same as VarBinary but DBMS implementation varies.
|
BINARY
|
32767
|
May be either Fixed or Variable.
|
NO
|
Databases that use fixed length column types will pad to the maximum length.
|
|
NChar
|
Exactly the same as NVarChar, except fixed length and DBMS storage implementation is usually NCHAR
|
NCHAR
|
65535
|
Fixed length
|
YES (but only when converted to native String)
|
|
|
CLOB
|
Character Large
OBject
|
CLOB
|
Undefined
|
Varies according to content
|
YES
|
DBMS performance considerations may apply.
|
|
BLOB
|
Binary
Large
OBject
|
BLOB
|
Undefined
|
Varies according to content
|
NO
|
DBMS performance considerations may apply.
|
|
Rarely Used Numeric Types
|
Type
|
Description
|
Typical DBMS storage Type
|
Maximum Dictionary Definable Length
|
When to Use
|
Important
|
Useable in RDML or RDMLX Programs
|
|
Signed
|
Signed or Zoned decimal
|
DECIMAL
|
RDMLX programs: 63 digits of which up to 63 may be decimals
RDML programs: 30 digits of which up to 9 may be decimals
|
For programmatic numeric/character overlaying operations.
|
Signed is less efficient than Packed or Integer on mathematical operations.
|
Both
|
|
Float
|
Floating Point
|
FLOAT
|
Undefined.
Floating point numbers are approximations, not exact numbers.
|
When using non-LANSA defined DBMS tables only.
|
Hardware and compiler behaviors may vary.
|
RDMLX
|
|
Ý 1.1.1 Field Type Considerations