Back to Contents

Data Types

The data types supported by the language.

Data Type Description
String Data Types
CHAR Fixed size character strings.
VARCHAR Variable size character strings.
STRING Dynamic size character strings.
Date and Datetime Data Types
DATE Simple calendar dates.
DATETIME High precision date and hour data.
INTERVAL High precision time intervals.
Numeric Data Types
INTEGER 4 byte integer.
SMALLINT 2 byte integer.
FLOAT 8 byte floating point decimal.

SMALLFLOAT

 4 byte floating point decimal.
DECIMAL High precision decimals.
MONEY High precision decimals with currency formatting.
Large Data Types
BYTE Large binary data (images).
TEXT Large text data (documents).

See also: Data conversions, Variables, Programs.

CHAR

Purpose:

The CHAR data type is a fixed length, character string data type.

Syntax:

CHAR[ACTER] [ (size) ]

Notes:

  1. When size is not specified, the default length is 1 character.
  2. There is no maximum size limitation; it depends on available memory.
  3. Variables are always filled with trailing blanks, but these are not significant in comparisons.
  4. Variables are initialized to NULL in functions, modules and globals.
  5. Sub-strings can be extracted with the substring operator (v[x,y]).
  6. You can concatenate strings with the double pipe operator (||).

Warnings:

  1. The CHAR type is not limited to 32767 characters.

Example:

01 MAIN
02   DEFINE c CHAR(100)
03   LET c = "abcdef"
04   DISPLAY c
05 END MAIN

VARCHAR

Purpose:

The VARCHAR data type is a variable length character string data type, with a maximum size.

Syntax:

VARCHAR [ ( maxsize, [ reserve ] ) ]

Notes:

  1. When maxsize is not specified, the default length is 1 character.
  2. There is no maximum size limitation, it depends on available memory.
  3. The reserve is not used, it is supported for compatibility with the SQL data type.
  4. Variables have significant trailing blanks: Value "abc   " is different from "abc".
  5. Variables are initialized to NULL in functions, modules and globals.
  6. Sub-strings can be extracted with the substring operator (v[x,y]).
  7. You can concatenate strings with the double pipe operator (||).

Warnings:

  1. The VARCHAR type is not limited to 255 characters.

Example:

01 MAIN
02   DEFINE c VARCHAR(10,5)
03   LET c = "abcdef  "
04   DISPLAY c || "."
05 END MAIN

STRING

Purpose:

The STRING data type is a variable length, dynamically allocated character string data type, without limitation.

Syntax:

STRING

Notes:

  1. The behavior is similar to the VARCHAR data type.
  2. There is no size limitation, it depends on available memory.
  3. Variables have significant trailing blanks: Value "abc   " is different from "abc".
  4. Variables are initialized to NULL in functions, modules and globals.
  5. You can concatenate strings with the double pipe operator (||).
  6. Variables declared with this type can use built-in class methods such as getLength() or toUpperCase().

Warnings:

  1. Because STRING variables have no size, they cannot be used as SQL parameters.
    For example, you cannot use a STRING variable in EXECUTE:
      EXECUTE stmt USING string_variable
    However, you can use it as argument for the PREPARE instruction:
      PREPARE stmt FROM string_variable
  2. The STRING data type cannot be used in FORMONLY field definitions.
  3. Sub-strings cannot be extracted with the subscript operator v[x,y].
  4. The getLength() method counts the number of characters, including trailing blanks.
    This is not the same behavior as the LENGTH() built-in function, which ignores trailing blanks.

Tips:

  1. Variables defined as STRING data types are managed by the runtime system efficiently, using many optimization techniques. Therefore, it is strongly recommended that you use STRING instead of CHAR/VARCHAR data types in code requiring fast execution.

Methods:

Object Methods
Name Description
append( str STRING )
  RETURNING STRING		 Returns a new string made by adding str to the end of the current string.
equals( src STRING )
  RETURNING INTEGER		 Returns TRUE if the string passed as parameters matches the current string. If one of the strings is NULL the method returns FALSE.
equalsIgnoreCase( src STRING )
  RETURNING INTEGER		 Returns TRUE if the string passed as parameters matches the current string, ignoring character case. If one of the strings is NULL the method returns FALSE.
getCharAt( pos INTEGER )
  RETURNING STRING		 Returns the character at the position pos (starts at 1). Returns NULL if the position does not match a character position in the current string.
getIndexOf( str STRING, spos INTEGER )
  RETURNING INTEGER		 Returns the position of the sub-string str in the current string, starting from position spos. Returns zero if the sub-string was not found.
getLength( )
  RETURNING INTEGER		 Returns the number of characters of the current string, including trailing spaces.
subString( spos INTEGER, epos INTEGER )
  RETURNING STRING		 Returns the sub-string starting at spos and ending at epos. Returns NULL if the positions do not delimit a sub-string in the current string.
toLowerCase( )
  RETURNING STRING		 Converts the current string to lowercase.
toUpperCase( )
  RETURNING STRING		 Converts the current string to uppercase.
trim( )
  RETURNING STRING		 Removes white space characters from the beginning and end of the current string.
trimLeft( )
  RETURNING STRING		 Removes white space characters from the beginning of the current string.
trimRight( )
  RETURNING STRING		 Removes white space characters from the end of the current string.

Example:

01 MAIN
02   DEFINE s STRING
03   LET s = "abcdef  "
04   DISPLAY s || ". (" || s.getLength() || ")"
05   IF s.trimRight() = "abcdef" THEN
06     DISPLAY s.toUpperCase()
07   END IF
08 END MAIN

INTEGER

Purpose:

The INTEGER data type is used for storing large whole numbers.

Syntax:

INT[EGER]

Notes:

  1. Storage is based on 4 bytes of signed data ( = 32 bits ).
  2. INTEGER value range is from -2,147,483,647 to +2,147,483,647.
  3. Value -2,147,483,648 is reserved for the representation of NULL.
  4. Variables are initialized to zero in functions, modules and globals.
  5. INTEGER values can be converted to strings.

Example:

01 MAIN
02   DEFINE i INTEGER
03   LET i = 1234567
04   DISPLAY i
05 END MAIN

SMALLINT

Purpose:

The SMALLINT data type is used for storing small whole numbers.

Syntax:

SMALLINT

Notes:

  1. Storage is based on 2 bytes of signed data ( = 16 bits ).
  2. SMALLINT value range is from -32,767 to +32,767.
  3. The value -32,768 is reserved for the representation of NULL.
  4. Variables are initialized to zero in functions, modules and globals.
  5. SMALLINT values can be converted to strings.

Example:

01 MAIN
02   DEFINE i SMALLINT
03   LET i = 1234
04   DISPLAY i
05 END MAIN

FLOAT

Purpose:

The FLOAT data type stores values as double-precision floating-point binary numbers with up to 16 significant digits.

Syntax:

{ FLOAT | DOUBLE PRECISION } [(precision)]

Notes:

  1. Storage is based on 8 bytes of signed data ( = 64 bits ).
  2. FLOAT values have the same range as the double data type in C.
  3. The precision can be specified but it has no effect in programs.
  4. Variables are initialized to zero in functions, modules and globals.
  5. FLOAT values can be converted to strings according to the DBMONEY environment variable (defines the decimal separator).

Tips:

  1. This data type it is not recommended for exact decimal storage; use the DECIMAL data type instead.

SMALLFLOAT

Purpose:

The SMALLFLOAT data type stores values as single-precision floating-point binary numbers with up to 8 significant digits.

Syntax:

{ SMALLFLOAT | REAL }

Notes:

  1. Storage is based on 4 bytes of signed data ( = 32 bits ).
  2. SMALLFLOAT values have the same range as the float data type in C.
  3. Variables are initialized to zero in functions, modules and globals.
  4. SMALLFLOAT values can be converted to strings according to the DBMONEY environment variable (which defines the decimal separator).

Tips:

  1. The data type it is not recommended for exact decimal storage; use the DECIMAL data type instead.

DECIMAL

Purpose:

The DECIMAL data type is provided to handle large numeric values with exact decimal storage.

Syntax:

{ DEC[IMAL] | NUMERIC } [ ( precision[,scale] ) ]

Notes:

  1. The number of significant digits is specified by the precision.
  2. The maximum number of significant digits (precision) is 32.
  3. The default for the precision is 16.
  4. The number of decimal digits is specified by the scale.
  5. When no scale is specified, the Decimal is a floating point decimal.
  6. The largest absolute value that a DECIMAL(p,s) can store without errors is 10p-s - 10s.
  7. Variables are initialized to NULL in functions, modules and globals.
  8. Decimal values can be converted to strings according to the DBMONEY environment variable (which defines the decimal separator).

Warnings:

  1. When you try to assign a value larger than the Decimal definition (for example, 12345.45 into DECIMAL(4,2) ), no out of range error occurs, and the variable is assigned with NULL. If WHENEVER ANY ERROR is used, it raises error -1226. If you do not use WHENEVER ANY ERROR, the STATUS variable is not set to -1226..

Example:

01 MAIN
02   DEFINE d1 DECIMAL(10,4)
03   DEFINE d2 DECIMAL(10,3)
04   LET d1 = 1234.4567
05   LET d2 = d1 / 3 -- Rounds decimals to 3 digits
06   DISPLAY d1, d2
07 END MAIN

MONEY

Purpose:

The MONEY data type is provided to store currency amounts with exact decimal storage.

Syntax:

MONEY [ (precision[,scale]) ]

Notes:

  1. The behavior is similar to the DECIMAL data type, with some differences.
  2. The number of significant digits is specified by the precision.
  3. The maximum number of significant digits (precision) is 32.
  4. The default for the precision is 16.
  5. The number of decimal digits is specified by the scale.
  6. When no scale is specified, it defaults to 2 or 3 according to locale settings.
  7. Variables are initialized to NULL in functions, modules and globals.
  8. Money values can be converted to strings according to the DBMONEY environment variable (which defines the currency sign and the decimal separator).

Warnings:

  1. See the DECIMAL data type.

DATE

Purpose:

The DATE data type stores calendar dates with a Year/Month/Day representation.

Syntax:

DATE

Notes:

  1. Storage is based on a 4 byte integer representing the number of days since 1899/12/31.
  2. Date arithmetic is based on a number of days: D1-D2 = NDays; D2+NDays = D1.
  3. Date / String conversions depend on the DBDATE environment variable.
  4. Several built-in functions and constants are available such as MDY() and TODAY.

Warnings:

  1. Variables are initialized to zero (=1899/12/31) in functions, modules and globals.

Tips:

  1. Integers can represent dates as a number of days starting from 1899/12/31 and can be assigned to dates, but it is not recommended that you directly assign integers to dates, for source code readability .
  2. As Date/String conversion is based on an environment setting, it is not recommended that you hardcode strings representing Dates.

Example:

01 MAIN
02   DEFINE d DATE
03   LET d = TODAY
04   DISPLAY d, "  ", d+100
05 END MAIN

DATETIME

Purpose:

The DATETIME data type stores date and time data with time units from the year to fractions of a second.

Syntax:

DATETIME qual1 TO qual2[(scale)]

Notes:

  1. The qualifiers qual1 and qual2 define the size of the Datetime.
  2. The qualifiers can be: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, FRACTION[(scale)].
  3. The scale defines the number of significant digits of the fractions of a second.
  4. The built-in CURRENT constant provides current system date/time.
  5. Datetime arithmetic is based on the INTERVAL data type: ( Datetime1 - Datetime2 = Interval; Datetime2 + Interval = Datetime1 ).
  6. DATE data can be used in Datetime arithmetic ( Datetime1 - Date1 = Interval ) 
  7. Variables are initialized to NULL in functions, modules and globals.
  8. The language supports Datetime Literals with a specific notation.
  9. Datetime values can be converted to Strings by following the standard format of Datetimes (YYYY-MM-DD hh:mm:ss.fffff).

Example:

01 MAIN
02   DEFINE d1, d2 DATETIME YEAR TO MINUTE
03   LET d1 = CURRENT YEAR TO MINUTE 
04   LET d1 = "1998-01-23 12:34"
05   DISPLAY d1, d2
06 END MAIN

INTERVAL

Purpose:

The INTERVAL data type stores spans of time as Year/Month or Day/Hour/Minute/Second/Fraction units.

Syntax:

INTERVAL qual1[(precision)] TO qual2[(scale)]

Notes:

  1. The qualifiers qual1 and qual2 define the size of the Interval.
  2. The qualifiers can be: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, FRACTION[(scale)].
  3. The precision defines the number of significant digits of the first qualifier.
  4. The scale defines the number of significant digits of the fractions of a second.
  5. Variables are initialized to NULL in functions, modules and globals.
  6. The language supports Interval Literals with a specific notation.
  7. Interval values can be converted to Strings by following the standard format of Datetimes (YYYY-MM-DD hh:mm:ss.fffff).

Warnings:

  1. There are two kind of Interval data types: Year/Month and Day/Hour/Minute/Second/Fraction intervals.

Example:

01 MAIN
02   DEFINE i1 INTERVAL YEAR TO MONTH
03   DEFINE i2 INTERVAL DAY(5) TO MINUTE
04   LET i1 = "2342 4"
05   LET i2 = "23423 12:34"
06   DISPLAY i1, i2
07 END MAIN

BYTE

Purpose:

The BYTE data type stores any kind of binary data such as images or sounds.

Syntax:

BYTE

Notes:

  1. The Byte data type is a complex type that cannot be used like simple types such as Integers or Chars.
  2. Byte variables are actually 'locators' for a large object stored in a file or in memory.

Warnings:

  1. A Byte variable must be initialized with the LOCATE instruction before usage.

Example:

01 MAIN
02   DEFINE b BYTE
03   DATABASE stock
04   LOCATE b IN MEMORY
05   SELECT bytecol INTO b FROM mytable
06 END MAIN

TEXT

Purpose:

The TEXT data type stores large ASCII text data.

Syntax:

TEXT

Notes:

  1. The Text data type is a complex type, unlike simple types such as Integers or Chars.
  2. Text variables are actually 'locators' for a large object stored in a file or in memory.

Warnings:

  1. A Text variable must be initialized with the LOCATE instruction before usage.

Example:

01 MAIN
02   DEFINE t TEXT
03   DATABASE stock
04   LOCATE t IN FILE "/tmp/mytext.txt" 
05   SELECT textcol INTO t FROM mytable
06 END MAIN