Field Type Use Recommendations

LANSA Technical

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