ALLBASE/SQL Reference Manual (36216-90216)

Chapter 7 221
Data Types
Long Operations
Long Operations
LONG columns in ALLBASE/SQL enable you to store a very large amount of binary data
in your database and to reference that data using a column name. You might use LONG
columns to store text files, software application code, voice data, graphics data, facsimile
data, or test vectors. Storing data in the database gives you the the advantages of
ALLBASE/SQL's recoverability, concurrency control, locking strategies, and indexes on
related columns.
The concept of how LONG column data is stored and retrieved differs from that of
non-LONG columns. LONG data is not processed by ALLBASE/SQL. Any formatting,
viewing, or other processing must be accomplished by a preprocessed application program.
Refer to the ALLBASE/SQL application programming guides for information on accessing
LONG columns from a preprocessed application.
Like other column data types, the LONG column is defined with the CREATE TABLE or
ALTER TABLE statement. A LONG column descriptor, called the LONG column I/O string,
describes where the LONG column input data is located and where the data is placed
when a SELECT or FETCH statement is executed. The LONG column I/O string is specified
as an element in the VALUES clause of an INSERT or the SET clause of an UPDATE
operation. When you use the SELECT or FETCH statement, the LONG column descriptor is
returned to the ISQL display or the host variable and the long column data is placed either
in the operating system file or stored memory.
Defining LONG Column Data with CREATE TABLE or ALTER
TABLE
Following is the syntax for specifying a column definition for a LONG column in either the
CREATE TABLE or ALTER TABLE statement. A maximum of 40 such LONG columns can be
defined for a single table.
( ColumnName LONG ColumnDataType [IN DBEFileSetName ]
[LANG = ColumnLanguageName ] [NOT NULL]) [,...]
The LONG data is stored in DBEFiles. These files can occupy up to 2
31
−1 bytes. For better
performance and storage considerations, specify a separate DBEFileSet when defining the
LONG column.
If IN
DBEFileSetName is not specified for a LONG column, this column's data is stored in
the same DBEFileSet as its related table. Do not specify the SYSTEM DBEFileSet as this
could severely impact database performance.
In the following example, LONG data for PartPicture is stored in the DBEFileSet
PartPictureSet, while data for columns PartName and PartNumber is stored in
PartsIllusSet:
CREATE TABLE PurchDB.PartsIllus
(PartName CHAR(16),
PartNumber INTEGER,
PartPicture LONG VARBINARY(1000000) IN PartPictureSet)
IN PartsIllusSet