TEXT

The TEXT data type stores large text data.

Syntax

TEXT

Usage

A BYTE or TEXT variable is a handle for a large object (LOB), that is stored in a file or in memory. Such data type is a complex type that cannot be used like INTEGER or CHAR basic types: It is designed to handle a large amount of data and has different semantics as simple types. The main difference with simple data types, is the fact that you must specify the storage with the LOCATE instruction, before using BYTE and TEXT variables.

The maximum size of data that can be handled by BYTE and TEXT variable is theoretically 2^31 bytes (~2.14 Gigabytes), but the practical limit depends from the disk or memory resources available to the process.

BYTE and TEXT variable must be initialized with the LOCATE instruction before usage. The LOCATE instruction basically defines where the large data object has to be stored (in a named file, in a temporary file, or in 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. When located in a temporary file (IN FILE), the temp directory can be defined by the DBTEMP environment variable.

DEFINE t TEXT
LET t = "aaaa" -- invalid, t is not located
LOCATE t IN MEMORY
LET t = "aaaa" -- valid, now t is located in memory

Important:

TEXT/BYTE variables are not comparable: Comparing TEXT/BYTE variable does not procude and error, but the result of the comparison operator will always be NULL, which is equivalent to FALSE in an IF statement:

DEFINE t1, t2 TEXT
IF t1 == t2 THEN -- Always NULL/FALSE
   ...
END IF

With BYTE and TEXT types, you can insert/update/fetch large objects of the database. The native database type to be used depends from the type of database server. After defining the storage with LOCATE, load / assign its value and use it directly in the SQL statements, or fetch data from LOB columns of the database, like simple data types:

DEFINE t1, t2 TEXT
...
CREATE TABLE mytable ( id INT, data TEXT )
...
LOCATE t1 IN MEMORY
CALL t1.readFile("lob.4gl")
INSERT INTO mytable VALUES ( 1, t1 )
LOCATE t2 IN FILE
SELECT data INTO t2 FROM mytable WHERE id=1
...

BYTE and TEXT types implement the readFile() and writeFile() methods to read/write the whole large object data from/to files. These methods can be used to easily interface with other software components:

DEFINE t TEXT
LOCATE t IN MEMORY
CALL t.readFile("orig.txt")
CALL t.writeFile("copy.txt")

For more details about LOB types methods, see BYTE data type as class and TEXT data type as class.

When initializing a BYTE or TEXT variable to NULL (INITIALIZE var TO NULL), if the variable is located in a file, the file is truncated (file size will be zero). If the variable is located in memory, the data in memory will be truncated. A subsequent usage of the variable (for example, FETCH INTO or LET assignment) is still possible:

DEFINE b BYTE
LOCATE b IN FILE "picture.png"
INITIALIZE b TO NULL
-- The file "picture.png" is now empty.

Resources allocated to a BYTE or TEXT variable can be deallocated with the FREE instruction.

Note: When the TEXT or BYTE variable is already located, a new LOCATE will free the allocated resource: If the prior LOCATE was using the IN FILE clause, the temporary file is dropped, if the prior LOCATE was using IN MEMORY, the memory is freed.

A FREE will remove the file if the LOB variable is located in a (named or temporary) file. When located in memory, the FREE instruction will deallocate the memory. After freeing the resources of a LOB variable, it must be re-located with a LOCATE instruction:

DEFINE b BYTE
LOCATE b IN FILE
CALL b.readFile("picture.png") -- ok
FREE b
CALL b.readFile("picture.png") -- Invalid, b is not located.
LOCATE b IN MEMORY
CALL b.readFile("picture.png") -- ok

Important:

TEXT and BYTE are reference types. This implies that assigning two variables (LET, passing a variable as parameter to a function, returning a result from a function) does not copy the value (Only the handle is copied. As a result, modifying the data with a TEXT/BYTE variable assigned from another TEXT/BYTE variable will in fact modify the same LOB data. Furthermore, the storage resource (file or memory) that was used by the assigned variable becomes unreferenced and is lost:

DEFINE b1, b2 BYTE -- Could be TEXT: same behavior
LOCATE b1 IN FILE "mydata" -- reference file directly
LOCATE b2 IN MEMORY -- use memory instead of file
CALL b2.readFile("mydata") -- read file content into memory
# FREE b2 -- this should be done to free memory before LET
LET b2 = b1 -- Now b2 points directly to the file (like b1)
INITIALIZE b1 TO NULL -- truncates reference file
DISPLAY IIF( b2 IS NULL, "b2 is null", "b2 is not null")
-- Displays "b2 is null"

In the next (invalid) code example, we try to save the value of the img BYTE variable in a temporary variable (tmp), with the typical programming pattern to save the value before modification. In fact the LET tmp=img assignment does not copy the data of the LOB like for simple data types (STRING, VARCHAR, DECIMAL), only the reference (i.e. handle) to the data is copied:

-- WARNING: THIS IS AN INVALID CODE EXAMPLE
DEFINE img, tmp BYTE
LOCATE img IN MEMORY
CALL img.readFile("picture1.png")
LOCATE tmp IN MEMORY
LET tmp = img -- Expecting to save the current data, but now
              -- both variables reference the same data...
CALL img.readFile("picture2.png")
LET img = tmp -- Does not restore the old value: Same data.

If you need to clone a large object, use the writeFile() / readFile() methods.

It is possible to assign TEXT variables to/from VARCHAR, CHAR and STRING variables.