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 data type

Purpose:

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

Syntax:

CHAR[ACTER] [ (size) ]

Notes:

  1. CHAR and CHARACTER are synonyms.
  2. size defines the length of the variable; the number of bytes allocated for the variable. The upper limit is 65534.
  3. When size is not specified, the default length is 1 character.

Usage:

CHAR variables are initialized to NULL in functions, modules and globals.

The size defines the number of bytes the variable can store. It is important to distinguish bytes from characters, because in a multi-byte character set, one character may be encoded on several bytes. For example, in the ISO-8859-1 character set, "forêt" uses 5 bytes, while in the UTF-8 multi-byte character set, the same word occupies 6 bytes, because the "ê" letter is coded with two bytes.

CHAR variables are always filled with trailing blanks, but the trailing blanks are not significant in comparisons:

01 MAIN
02   DEFINE c CHAR(10)
03   LET c = "abcdef"
04   DISPLAY "[", c ,"]"      -- displays [abcdef    ]
05   IF c == "abcdef" THEN    -- this is TRUE
06      DISPLAY "equals"
07   END IF
08 END MAIN

VARCHAR data type

Purpose:

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

Syntax:

VARCHAR [ ( size, [ reserve ] ) ]

Notes:

  1. The size defines the maximum length of the variable; the maximum number of bytes the variable can store. The upper limit is 65534.
  2. reserve is not used, however its inclusion in the syntax for a VARCHAR variable is permitted for compatibility with the SQL data type.
  3. When size is not specified, the default length is 1 character.

Usage:

VARCHAR variables are initialized to NULL in functions, modules and globals.

The size defines the maximum number of bytes the variable can store. It is important to distinguish bytes from characters, because in a multi-byte character set, one character may be encoded on several bytes. For example, in the ISO-8859-1 character set, "forêt" uses 5 bytes, while in the UTF-8 multi-byte character set, the same word occupies 6 bytes, because the "ê" letter is coded with two bytes.

VARCHAR variables store trailing blanks (i.e. "abc " is different from "abc"). Trailing blanks are displayed or printed in reports, but they are not significant in comparisons:

01 MAIN
02   DEFINE vc VARCHAR(10)
03   LET vc = "abc  "         -- two trailing blanks
04   DISPLAY "[", vc ,"]"     -- displays [abc  ]
05   IF vc == "abc" THEN      -- this is TRUE
06      DISPLAY "equals"
07   END IF
08 END MAIN

When you insert character data from VARCHAR variables into VARCHAR columns in a database table, the trailing blanks are kept. Likewise, when you fetch VARCHAR column values into VARCHAR variables, trailing blanks are kept. 

01 MAIN
02   DEFINE vc VARCHAR(10)
03   DATABASE test1
04   CREATE TABLE table1 ( k INT, x VARCHAR(10) )
05   LET vc = "abc  "         -- two trailing blanks
06   INSERT INTO table1 VALUES ( 1, vc )
07   SELECT x INTO vc FROM table1 WHERE k = 1
08   DISPLAY "[", vc ,"]"     -- displays [abc  ]
09 END MAIN
Warning: In SQL statements, the behavior of the comparison operators when using VARCHAR values differs from one database to the other. Informix is ignoring trailing blanks, but most other databases take trailing blanks of VARCHAR values into account. See SQL Programming for more details.

STRING data type

Purpose:

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

Syntax:

STRING

Usage:

The behavior of a STRING variable is similar to the VARCHAR data type. For example, as VARCHAR variables, STRING variables have significant trailing blanks (i.e. "abc   " is different from "abc"). There is no size limitation, it depends on available memory.

STRING variables are initialized to NULL in functions, modules and globals.

The STRING data type is typically used to implement utility functions manipulating character string with unknown size. It cannot be used to store SQL character string data, because databases have rules that need a maximum size as for CHAR and VARCHAR types. 

Warning: The STRING data type cannot be used as SQL parameter of fetch buffer, not can it be used as form field.

Variables declared with the STRING data type can use built-in class methods such as getLength() or toUpperCase().

Methods:

Warning: The STRING methods are all based on byte-semantics. In a multi-byte environment, the getLength() method returns the number of bytes, which can be different from the number of characters.

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 byte position pos (starts at 1). Returns NULL if the position does not match a valid character-byte position in the current string or if the current string is null.
getIndexOf( str STRING, spos INTEGER )
  RETURNING INTEGER		 Returns the position of the sub-string str in the current string, starting from byte position spos. Returns zero if the sub-string was not found. Returns -1 if string is NULL.
getLength( )
  RETURNING INTEGER		 This method counts the number of bytes, including trailing blanks. The LENGTH() built-in function ignores trailing blanks.
subString( spos INTEGER, epos INTEGER )
  RETURNING STRING		 Returns the sub-string starting at byte position spos and ending at epos. Returns NULL if the positions do not delimit a valid sub-string in the current string, or if the current string is null.
toLowerCase( )
  RETURNING STRING		 Converts the current string to lowercase. Returns NULL if the string is null.
toUpperCase( )
  RETURNING STRING		 Converts the current string to uppercase. Returns NULL if the string is null.
trim( )
  RETURNING STRING		 Removes white space characters from the beginning and end of the current string. Returns NULL if the string is null.
trimLeft( )
  RETURNING STRING		 Removes white space characters from the beginning of the current string. Returns NULL if the string is null.
trimRight( )
  RETURNING STRING		 Removes white space characters from the end of the current string. Returns NULL if the string is null.

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 data  type

Purpose:

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

Syntax:

INT[EGER]

Notes:

  1. INT and INTEGER are synonyms.

Usage:

The storage of INTEGER variables is based on 4 bytes of signed data ( = 32 bits ). The value range is from -2,147,483,647 to +2,147,483,647.

INTEGER variables are initialized to zero in functions, modules and globals.

Warning: The value -2,147,483,648 is reserved for the representation of NULL.

Example:

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

SMALLINT data type

Purpose:

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

Syntax:

SMALLINT

Notes:

  1. Variables are initialized to zero in functions, modules and globals.
  2. SMALLINT values can be converted to strings.

Usage:

The storage of SMALLINT variables is based on 2 bytes of signed data ( = 16 bits ). The value range is from -32,767 to +32,767.

SMALLINT variables are initialized to zero in functions, modules and globals.

Warning: The value -32,768 is reserved for the representation of NULL.

Example:

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

FLOAT data type

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. FLOAT and DOUBLE PRECISION are synonyms.
  2. The precision can be specified but it has no effect in programs.

Usage:

The storage of FLOAT variables is based on 8 bytes of signed data ( = 64 bits ), this type is equivalent to the double data type in C.

FLOAT variables are initialized to zero in functions, modules and globals.

FLOAT values can be converted to strings according to the DBMONEY environment variable (defines the decimal separator).

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

SMALLFLOAT data type

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. SMALLFLOAT and REAL are synonyms.
  2. SMALLFLOAT values can be converted to strings according to the DBMONEY environment variable (which defines the decimal separator).

Usage:

The storage of SMALLFLOAT variables is based on 4 bytes of signed data ( = 32 bits ), this type is equivalent to the float data type in C.

SMALLFLOAT variables are initialized to zero in functions, modules and globals.

SMALLFLOAT values can be converted to strings according to the DBMONEY environment variable (defines the decimal separator).

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

DECIMAL data type

Purpose:

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

Syntax:

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

Notes:

  1. DEC, DECIMAL and NUMERIC are synonyms.
  2. precision defines the number of significant digits (limit is 32, default is 16).
  3. scale defines the number of digits to the right of the decimal point.
  4. When no scale is specified, the data type defines a floating point number.

Usage:

The DECIMAL data type must be used for storing number with fractional parts that must be calculated exactly.

DECIMAL variables are initialized to NULL in functions, modules and globals.

The largest absolute value that a DECIMAL(p,s) can store without errors is 10p-s - 10s. The stored value can have up to 30 significant decimal digits in its fractional part, or up to 32 digits to the left of the decimal point.

When you specify both the precision and scale, you define a decimal with a fixed point arithmetic. If the data type declaration specifies a precision but no scale, it defines a floating-point number with precision significant digits. If the data type declaration specifies no precision and scale, the default is DECIMAL(16), a floating-point number with a precision of 16 digits.

DECIMAL values can be converted to strings according to the DBMONEY environment variable (defines the decimal separator).

Warnings:

  1. In ANSI-compliant databases, DECIMAL data types do not provide floating point numbers. When you define a database column as DECIMAL(16), it is equivalent to a DECIMAL(16,0) declaration. You should always specify the scale to avoid mistakes.
  2. When the default exception handler is used, if 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 data type

Purpose:

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

Syntax:

MONEY [ (precision[,scale]) ]

Notes:

  1. precision defines the number of significant digits (limit is 32, default is 16).
  2. scale defines the number of digits to the right of the decimal point.
  3. When no scale is specified, it defaults to 2.

Usage:

The MONEY data type is provided to store currency amounts. Its behavior is similar to the DECIMAL data type, with some important differences:

A MONEY variable is displayed with the currency symbol defined in the DBMONEY environment variable.

You cannot define floating-point numbers with MONEY: If you do not specific the scale in the data type declaration, it defaults to 2. If no precision / scale parameters are specified, MONEY is interpreted as a DECIMAL(16,2).

Warning: See the DECIMAL data type.

DATE data type

Purpose:

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

Syntax:

DATE

Usage:

Storage of DATE variables is based on a 4 byte integer representing the number of days since 1899/12/31.

Because DATE values are stored as integers, you can use them in arithmetic expressions: the difference of two dates returns the number of days. This is possible (and portable) with language arithmetic operators, but should be avoided in SQL statements, because not all databases support integer-based date arithmetic.

Data conversions, input and display of DATE values are ruled by environment settings, such as the DBDATE and DBCENTURY environment variables.

Several built-in functions and constants are available such as MDY() and TODAY.

DATE 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. It is not recommended that you directly assign integers to dates, however, for source code readability.
  2. As Date-to-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 data type

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

where qual1 can be one of:

  YEAR
  MONTH
  DAY
  HOUR
  MINUTE
  SECOND
  FRACTION

and qual2 can be one of:

  YEAR
  MONTH
  DAY
  HOUR
  MINUTE
  SECOND
  FRACTION
  FRACTION(1)
  FRACTION(2)
  FRACTION(3)
  FRACTION(4)
  FRACTION(5)

Notes:

  1. scale defines the number of significant digits of the fractions of a second.
  2. qual1 and qual2 qualifiers define the precision of the DATETIME variable.

Usage:

The DATETIME data type stores an instance in time, expressed as a calendar date and time-of-day.

The qual1 and qual2 qualifiers define the precision of the DATETIME variable. The precision can range from a year through a fraction of second.

DATETIME arithmetic is based on the INTERVAL data type, and can be combined with DATE values:

The CURRENT operator provides current system date/time.

You can assign DATETIME variables with datetime literals with a specific notation.

DATETIME variables are initialized to NULL in functions, modules and globals.

DATETIME values can be converted to strings by the ISO format (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 data type

Purpose:

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

Syntax 1: year-month class interval

 INTERVAL YEAR[(precision)]  TO MONTH
|INTERVAL YEAR[(precision)]  TO YEAR
|INTERVAL MONTH[(precision)] TO MONTH

Syntax 2: day-time class interval

 INTERVAL DAY[(precision)]      TO FRACTION[(scale)]
|INTERVAL DAY[(precision)]      TO SECOND
|INTERVAL DAY[(precision)]      TO MINUTE
|INTERVAL DAY[(precision)]      TO HOUR
|INTERVAL DAY[(precision)]      TO DAY

|INTERVAL HOUR[(precision)]     TO FRACTION[(scale)]
|INTERVAL HOUR[(precision)]     TO SECOND
|INTERVAL HOUR[(precision)]     TO MINUTE
|INTERVAL HOUR[(precision)]     TO HOUR

|INTERVAL MINUTE[(precision)]   TO FRACTION[(scale)]
|INTERVAL MINUTE[(precision)]   TO SECOND
|INTERVAL MINUTE[(precision)]   TO MINUTE

|INTERVAL SECOND[(precision)]   TO FRACTION[(scale)]
|INTERVAL SECOND[(precision)]   TO SECOND

|INTERVAL FRACTION[(precision)] TO FRACTION[(scale)]

Notes:

  1. precision defines the number of significant digits of the first qualifier, it must be an integer from 1 to 9.
    For YEAR, the default is 4. For all other time units, the default is 2.
    For example, YEAR(5) indicates that the INTERVAL can store a number of years with up to 5 digits.

Usage:

The INTERVAL data type stores a span of time, the difference between two points in time. It can also be used to store quantities that are measured in units of time, such as ages or times required for some activity.

The INTERVAL data type falls in two classes, which are mutually exclusive:

INTERVAL values can be negative.

INTERVAL arithmetic is possible, and can involve DATETIME values:

You can assign INTERVAL variable with interval literals with a specific notation.

INTERVAL values can be converted to strings by using the ISO format (YYYY-MM-DD hh:mm:ss.fffff).

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 data type

Purpose:

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

Syntax:

BYTE

Usage:

A BYTE variable is actually a 'locator' for a large object stored in a file or in memory. The BYTE data type is a complex type that cannot be used like simple types such as INTEGER or CHAR: It is designed to handle a large amount of unstructured binary data. This type has a theoretical limit of 2^31 bytes, but the practical limit depends from the resources available to the process. You can use the BYTE data type to store, fetch or update the contents of a BYTE database column when using Informix, or the content of a BLOB column when using another type of database.

Warning: A BYTE variable must be initialized with the LOCATE instruction before usage. You might want to free resources allocated to the BYTE variable with the FREE instruction. Note that a FREE will remove the file if the LOB variable is located in a file.

The LOCATE instruction basically defines where the large data object has to be stored (in file or memory). This instruction will actually allow you to fetch a LOB into memory or into a file, or insert a LOB from memory or from a file into the database.

Warning: When you assign a BYTE variable to another BYTE variable, the LOB data is not duplicated, only the handler is copied.

Note that if you need to clone the large object, you can use the I/O built-in methods to read/write data from/to a specific file. The large object can be located in memory or in a file.

Methods:

Object Methods
Name Description
readFile( fileName STRING ) Reads data from a file and copies into memory or to the file used by the variables according to the LOCATE statement issued on the object.
writeFile( fileName STRING ) Writes data from the variable (memory or source file) to the destination file passed as parameter. The file is created if it does not exist.

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 data type

Purpose:

The TEXT data type stores large text data.

Syntax:

TEXT

Usage:

A TEXT variable is actually a 'locator' for a large object stored in a file or in memory. The TEXT data type is a complex type that cannot be used like basic character string types such as VARCHAR or CHAR: It is designed to handle a large amount of text data. You can use this data type to store, fetch or update the contents of a TEXT database column when using Informix, or the content of a CLOB column when using another type of database.

Warning: A TEXT variable must be initialized with the LOCATE instruction before usage. You might want to free resources allocated to the TEXT variable with the FREE instruction. Note that a FREE will remove the file if the LOB variable is located in a file.

The LOCATE instruction basically defines where the large data object has to be stored (in file or memory). This instruction will actually allow you to fetch a LOB into memory or into a file, or insert a LOB from memory or from a file into the database.

Warning: When you assign a TEXT variable to another TEXT variable, the LOB data is not duplicated, only the handler is copied.

You can assign TEXT variables to/from VARCHAR, CHAR and STRING variables.

Note that if you need to clone the large object, you can use the I/O built-in methods to read/write data from/to a specific file. The large object can be located in memory or in a file.

Methods:

Object Methods
Name Description
readFile( fileName STRING ) Reads data from a file and copies into memory or to the file used by the variables according to the LOCATE statement issued on the object.
writeFile( fileName STRING ) Writes data from the variable (memory or source file) to the destination file passed as parameter. The file is created if it does not exist.

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