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³¹ -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

The next statement specifies that data for the new LONG column, PartModule, will be stored in PartPictureSet:

   ALTER TABLE PurchDB.PartsIllus
           ADD PartModule LONG VARBINARY(50000) IN PartPictureSet

Since LONG data for PartMap will be stored in the same DBEFileSet as its related table, PartsIllus, it goes to PartsIllusSet.

   ALTER TABLE PurchDB.PartsIllus
           ADD PartMap LONG VARBINARY(70000)

Defining Input and Output with the LONG Column I/O String

The INSERT and UPDATE statements use the LONG column I/O string to define the various input and output parameters for any LONG column. You need to understand this string in order to input, change, or retrieve LONG data.

The LONG column I/O string has an input portion (indicated with <) and an output portion (indicated with >). The input portion of the LONG column I/O string, also referred to as the input device, specifies the location of data that you want written to the database. You can indicate a file name or a shared memory address.

A variable length record file cannot be input to a LONG column.

The output portion of the LONG column I/O string (the output device) specifies where you want LONG data to be placed when you execute the SELECT or FETCH statement. You have the option of specifying a file name, part of a file name, or having ALLBASE/SQL specify a file name. You also can specify a shared memory address or have ALLBASE/SQL specify a shared memory address. Additional output parameters allow you to append to or overwrite an existing file. The output device specification is stored in the database table and is available to you when you use the OUTPUT_DEVICE function or OUTPUT_NAME function together with a SELECT or FETCH statement. For more information on the OUTPUT_DEVICE and OUTPUT_NAME functions, see Chapter 8 “Expressions” in this document.

The examples in the following sections illustrate the use of the input and output portions of the LONG column I/O string. The complete syntax for the LONG column I/O string is presented under the INSERT, UPDATE, and UPDATE WHERE CURRENT statements.

It is important to note that files used for LONG column input and output are opened and closed by ALLBASE/SQL. You do not need to open or close the files for use in the DBEnvironment. ALLBASE/SQL does not control the input or output device files on the operating system. That is, if there is a rollback work, ALLBASE/SQL will not remove the physical operating system file generated by the SELECT statement.

Using INSERT with LONG Column Data

As with any column, you use the SQL INSERT statement or an ISQL INPUT command to initially put data in a LONG column. The LONG column I/O string requires an input device, but the output device is optional.

The following examples illustrate some of the options available to you.

Using INSERT with No Specified File Options

In this example, data from the file ../tools/hammer becomes the contents of the LONG column PartPicture. The output device is the file hammer. If this file already exists when the SELECT or FETCH statement is issued, it is not overwritten or appended to, and an error is generated.

   INSERT INTO PurchDB.PartsIllus
    VALUES ('hammer'
             100,
            '<../tools/hammer >hammer')

Using INSERT with the Overwrite Option

When you want to reuse an existing output device file when the inserted data is later selected or fetched, specify the overwrite option. Here if file wrench already exists at INSERT time, it is overwritten:

   INSERT INTO PurchDB.PartsIllus
        VALUES ('hammer',
               100,
               '<../tools/hammer >!wrench')

Using INSERT with the Append Option

You can append LONG data to an existing file. In this example, when the LONG column PartPicture is selected or fetched, output is appended at the end of the file wrench:

   INSERT INTO PurchDB.PartsIllus
        VALUES ('hammer',
               100,
               '<../tools/hammer >>wrench')

Using INSERT with the Wildcard Option

Depending on your application, you may need to assign a specific, known name to the output device. On the other hand, a partially generic name or a completely unknown name may be desirable. In this example, the output device name begins with prt and is followed by a five-character, random wild card, for instance, 'prt123aB':

   INSERT INTO PurchDB.PartsIllus
        VALUES ('hammer'
               100,
               '< ../tools/hammer >prt$')

Using INSERT with Shared Memory Input and Output

You have the option of using a shared memory address to indicate the location of input data and/or output data. In the next example, data flows from shared memory address 1230 to PartsIllus table, and when this data is selected or fetched, it goes to shared memory address 1000:

   INSERT INTO PurchDB.PartsIllus
        VALUES ('saw'
               300,
               '<%1230 >%1000')

Using SELECT with LONG Column Data

The concept of how data is retrieved differs from that of non-LONG columns. The output portion of the LONG column I/O string (rather than the data itself) is obtained with the SELECT or FETCH statement. The LONG data goes to a file or shared memory.

In this example, the SELECT statement places the LONG data from the PartPicture column in a file or in shared memory, as specified in the LONG column I/O string when the PartPicture column was inserted or updated. The SELECT statement puts the file name or shared memory address in the PartPicture LONG column descriptor. In an application, the contents of the descriptor are placed in a host variable and may be parsed to extract the file name or heap space address. When a long field column is selected using ISQL, the file name or heap space address is displayed in the column whose heading is the long field name. Refer to the "Programming with LONG Columns" chapter of the appropriate application programming guide for information on the format of the LONG column descriptor.

   SELECT PartPicture
     FROM PurchDB.PartsIllus
    WHERE PartName = 'saw'

Using UPDATE with LONG Column Data

When you issue an UPDATE on a LONG column, you have the following options:

Change the stored data as well as the output device name and/or options.
Change the stored data only.
Change the output device name and/or options only.

You must specify either the input device, the output device, or both.

Examples

The following examples present a sampling of possible combinations.

Using UPDATE to Change Stored Data and Output Device Name

In this example, data from the file ../tools/newhammer is inserted into the LONG column PartPicture replacing the previously stored data. The output device name is changed to be the file newhammer. Should file newhammer already exist when the SELECT or FETCH statement is issued, it is not overwritten, and an error is generated.

   UPDATE PurchDB.PartsIllus
      SET PartPicture = '<../tools/newhammer >newhammer'
    WHERE PartName = 'hammer'

Using UPDATE to Change Stored Data Only

Here the stored data in LONG column PartPicture is replaced with data from the file ../tools/newhammer. Assuming the original output device was named hammer, when you select or fetch the PartPicture column, the updated output still goes to a file named hammer.

   UPDATE PurchDB.PartsIllus 
      SET PartPicture = '<../tools/newhammer'
    WHERE PartName = 'hammer'

Using UPDATE to Change the Output Device Name and Options

You may want to change the output file name but not the LONG data associated with a particular column. Here newhammer becomes the output device name. When LONG column PartPicture is SELECTed or FETCHed, output is appended to the file newhammer.

   UPDATE PurchDB.PartsIllus
      SET PartPicture = '>>newhammer'
    WHERE PartName = 'hammer'

Using UPDATE with Shared Memory Input and Output

You may decide to use shared memory as your input and/or output device. In this example, LONG data flows from file newsaw to the PartsIllus table, and when this data is selected or fetched it goes to shared memory address 1000:

   UPDATE PurchDB.PartsIllus
     SET PartPicture = '< newsaw >%1000'
     WHERE PartName = 'saw'

In the next example, data flows to the database from shared memory address 1000 and when the LONG column is selected or fetched data goes to file newsaw:

   UPDATE PurchDB.PartsIllus
      SET PartPicture = '<%1000 >newsaw'
    WHERE PartName = 'saw'

Using DELETE with LONG Column Data

DELETE and DELETE WHERE CURRENT syntax is unchanged when used with LONG columns. It is limited in that a LONG column cannot be used in the WHERE clause.

In the following example, any rows in PurchDB.PartsIllus with the PartName of hammer are deleted:

   DELETE FROM PurchDB.PartsIllus
         WHERE PartName = 'hammer'

When LONG data is deleted, the space it occupied in the DBEnvironment is released when your transaction ends. But the data files still exist on the operating system.