Prerequisites

The examples in this section depend on the existence of the following, alternate catalog table for the stores7 database. The examples also depend on the presence of an sbspace, s9_sbspc, that stores the contents of the BLOB and CLOB columns, picture and advert_descr, in the alternate catalog table.

-- create table that uses smart large objects (CLOB & BLOB) to
-- store the catalog advertisement data.

CREATE TABLE  catalog
        (
        catalog_num     SERIAL8 (10001) primary key,
        stock_num       SMALLINT,
        manu_code       CHAR(3),
        unit            CHAR(4),
        advert          ROW (picture BLOB, caption VARCHAR(255, 65)),
        advert_descr    CLOB,
    FOREIGN KEY (stock_num, manu_code) REFERENCES stock constraint aa
        )
        PUT advert IN (s9_sbspc)
           (EXTENT SIZE 100),
        advert_descr IN (s9_sbspc)
            (EXTENT SIZE 20, KEEP ACCESS TIME)

The following example illustrates typical commands to create an sbspace. The values of specific options can vary. You must replace PATH with the complete file name of the file that you allocate for the sbspace.

touch s9_sbspc
onspaces -c -S s9_sbspc -g 4 -p PATH -o 0 -s 2000

For more information about how to create an sbspace, and particularly on the onspaces utility, see your HCL OneDB™ Administrator's Guide.

The following code illustrates the format of entries in a load file that you might use to load data into the alternate catalog table. A load file contains data that the LOAD statement loads into a table. Each line in the following figure loads one row in the table. The following figure shows only a sample of code that you can use to load the catalog table. For more information about the LOAD statement, see the HCL OneDB Guide to SQL: Syntax:

0|1|HRO|case|ROW(/tmp/cn_1001.gif,"Your First Season's Baseball Glove")|0,62,
   /tmp/catalog.des|
0|1|HSK|case|ROW(NULL,"All Leather, Hand Stitched, Deep Pockets, Sturdy 
   Webbing That Won't Let Go")||
0|1|SMT|case|ROW(NULL,"A Sturdy Catcher's Mitt With the Perfect Pocket")||
0|2|HRO|each|ROW(NULL,"Highest Quality Ball Available, from the 
   Hand-Stitching to the Robinson Signature")||
0|3|HSK|case|ROW(NULL,"High-Technology Design Expands the Sweet Spot")||
0|3|SHM|case|ROW(NULL,"Durable Aluminum for High School and Collegiate
   Athletes")||
0|4|HSK|case|ROW(NULL,"Quality Pigskin with Norm Van Brocklin Signature")||

The following code fragment illustrates the format of information in the catalog.des file, to which the preceding code refers. The entry for advert_descr (0,62,/tmp/catalog.des) in the preceding code specifies the offset, length, and file name from which the description is loaded. The offset and length are hexadecimal values.

Brown leather. Specify first baseman's or infield/outfield style.
   Specify right- or left-handed.

Double or triple crankset with choice of chainrings. For double crankset,
chainrings from 38-54 teeth. For triple crankset, chainrings from 24-48 teeth.

No buckle so no plastic touches your chin. Meets both ANSI and Snell standards
   for impact protection.7.5 oz. Lycra cover.

Fluorescent yellow.

Super shock-absorbing gel pads disperse vertical energy into a horizontal plane
   for extraordinary cushioned comfort. Great motion control.
   Mens only. Specify size

This section contains the following example programs.


Program	Description	See
create_clob.ec	Inserts a row that contains a CLOB column into the alternate catalog table.	The create_clob.ec program
get_lo_info.ec	Appends the price from the stock table of the stores7 database to the advert_descr column of the alternate catalog table.	The get_lo_info.ec program
upd_lo_descr.ec	Obtains the price of catalog items for which the advert_descr column is not null and appends the price to the description.	The upd_lo_descr.ec program