The data of a TEMP-TABLE BLOB and CLOB fields is stored in the same way as that of a user database table's BLOB and CLOB fields. That is, the data is not stored in the record itself but in an external location with a pointer in the record to that external location. Hence, the length or size of the BLOB and CLOB fields do not count towards the 32K record size limit.
Binary large objects (BLOBs) and character large objects (CLOBs) fields can be up to 1 gigabyte long.
While conceptually they are like other fields in a database record, they are stored separately and the record contains a "lob locator" that points to the data. This has the following benefits:
1. The 32 KB record size limit is not exceeded, but lob values can be much larger than 32K up to 1 GB, is the maximum LOB size supported
2. When you read a record from the database, the lob values are not retrieved with the record. Instead they are retrieved on demand when you use the lob data. This is good for performance because you don't pay for fetching the lob data unless you actually want it.
3. If the record is updated but the lob data is not, then the transaction log (bi and ai logs) overhead is not increased since the lob data does not have to be recorded. This is good both for performance and for transaction log space usage.
Please note that when the lob data is created, updated, or deleted, there is overhead associated with it. Also there is overhead if you make a copy of the record and include the lob data in the copy.
See
How the size of a LOB is stored and retrieved for additional information.
Example:
DEFINE VARIABLE cFilePath AS CHARACTER NO-UNDO INITIAL "c:\photo.jpg".
DEFINE VARIABLE lFileSize AS INTEGER NO-UNDO.
DEFINE VARIABLE mBinaryData AS MEMPTR NO-UNDO.
DEFINE VARIABLE lBytesRead AS INTEGER NO-UNDO.
/* Get the size of the file */
FILE-INFO:FILE-NAME = cFilePath.
lFileSize = FILE-INFO:FILE-SIZE.
IF lFileSize > 0 THEN DO:
/* Allocate memory for the binary data */
SET-SIZE(mBinaryData) = lFileSize.
/* Open the file in binary mode */
INPUT FROM VALUE(cFilePath) BINARY NO-ECHO.
/* Read the binary data into the MEMPTR */
lBytesRead = lFileSize.
IMPORT mBinaryData.
/* Close the input stream */
INPUT CLOSE.
END.
DEFINE TEMP-TABLE ttIdDocument NO-UNDO
FIELD photo AS BLOB
FIELD address2 AS CHARACTER FORMAT "X(40)"
FIELD address1 AS CHARACTER FORMAT "X(40)"
FIELD phone AS CHARACTER FORMAT "X(15)"
FIELD lastName AS CHARACTER FORMAT "X(15)"
FIELD firstName_Name AS CHARACTER FORMAT "X(15)"
INDEX IdDocument_index AS PRIMARY UNIQUE lastName firstName.
/* Create temp-table record */
CREATE ttIdDocument.
COPY-LOB FROM mBinaryData TO ttIdDocument.photo.
/* Release the MEMPTR memory */
SET-SIZE(mBinaryData) = 0.
/* Copy temp-table record to database */
CREATE IdDocument.
BUFFER-COPY ttIdDocument TO IdDocument.
