Oracle8 Image Cartridge User's Guide Release 8.0.4 A55713-02 |
Library | Product | Contents | Index

# 2
Using Image Object Types

This chapter provides examples for the common uses of the Image BLOB and Image BFILE types, including:

- adding types to a new or existing table
- inserting a row using BLOB or BFILE images
- populating a row using BLOB or BFILE images
- querying a row
- copying an image from BFILE to BLOB
- copying an image from BLOB to BLOB
- converting an image's format either temporarily or permanently
- extending the cartridge with new object types
- using the cartridge with object views

Prior to updating a BLOB value, you must lock the row containing the BLOB locator. This is usually done using a SELECT FOR UPDATE statement in SQL and PL/SQL programs, or using an OCI pin or lock function in OCI programs.

## 2.1 Adding Image Types to an Existing Table

Suppose you have an existing table named 'emp' with the following columns:

    ename      VARCHAR2(50)
    salary     NUMBER
    job        VARCHAR2(50)
    department INTEGER

To add a new column to the 'emp' table called 'photo_id' using the Image BLOB type, issue the following statement:

    ALTER TABLE emp
    ADD (photo_id ORDSYS.ORDIMGB);

To add a new column to the 'emp' table called 'large_photo' using the Image BFILE type, issue the following statement:

    ALTER TABLE emp
    ADD (large_photo ORDSYS.ORDIMGF);

## 2.2 Adding Image Types to a New Table

Suppose you are creating a new table called 'emp' with the following columns:

    ename         VARCHAR2(50)
    salary        NUMBER
    job           VARCHAR2(50)
    department    INTEGER
    photo_id      ORDIMGB
    large_photo   ORDIMGF

The column 'photo_id' would use the Image BLOB type, while the column 'large_photo' would use the Image BFILE type. The following statement would create the table:

    CREATE TABLE emp (
    ename VARCHAR2(50),
    salary NUMBER,
    job VARCHAR2(50),
    department INTEGER,
    photo_id ORDSYS.ORDIMGB,
    large_photo ORDSYS.ORDIMGF);

## 2.3 Inserting a Row Using BLOB Images

To insert a row into a table that has storage for image content using the Image Cartridge BLOB type (ORDImgB), you must populate the type with an initializer. Note that this is different from NULL.

The following examples describe how to insert rows into the table using the Image BLOB type. Assume you have a table 'emp' with the following columns:

    ename      VARCHAR2(50)
    salary     NUMBER
    job        VARCHAR2(50)
    department INTEGER
    photo_id   ORDIMGB

To insert a row into the table with no data in the 'photo_id' column, issue the following statement:

    INSERT INTO emp VALUES ('John Doe',24000,'Technical Writer',123,NULL);

Attempting to use the Image Cartridge types with a NULL value results in an error. If you are going to use the image type's content attribute, you must populate the content attribute with a value and initialize storage for the content attribute with an empty_blob( ) constructor. To insert a row into the table with empty data in the 'photo_id' column, issue the following statement:

    INSERT INTO emp VALUES ('John Doe',24000,'Technical Writer',123,
    ORDSYS.ORDIMGB(empty_blob(),NULL,NULL,NULL,NULL,NULL,NULL));

## 2.4 Populating a Row Using BLOB Images

The following is an example of populating the row with Image BLOB data:

    DECLARE
        -- application variables
        Image  ORDSYS.ORDIMGB;
    BEGIN
        insert into emp values('John Doe',24000,'Technical Writer',123,
        ORDSYS.ORDIMGB(empty_blob(), NULL,NULL,NULL,NULL,NULL,NULL));
        --select the newly inserted row for update
        SELECT photo_id into Image from emp
        where ename = 'John Doe' for UPDATE;

        BEGIN
        -- populate the data with dbms lob calls or write an OCI
        -- program to fill in the content attribute
        END;

        -- set property attributes for the image data
        Image.setProperties;

        UPDATE emp set photo_id = Image where ename = 'John Doe';

        -- continue processing
        END;

An UPDATE statement is required to update the property attributes. If you do not perform the setProperties( ) function and UPDATE statement now, you can still commit and the change to the image will be reflected in the content attribute, but not in the properties. See the Oracle8 Application Developer's Guide for more information on BLOBs.

## 2.5 Inserting a Row Using BFILE Images

To insert a row into a table that has storage for image content using the Image BFILE type (ORDImgF), you must populate the type with an initializer. Note that this is different from NULL.

The following examples describe how to insert rows into the table using the Image BFILE type. Assume you have a table 'emp' with the following columns:

    ename    VARCHAR2(50)
    salary   NUMBER
    job      VARCHAR2(50)
    department  INTEGER
    large_photo ORDIMGF

To insert a row into the table with no data in the 'large_photo' column, issue the following statement:

    INSERT INTO emp VALUES ('John Doe',24000,'Technical Writer',123,NULL);

Attempting to use the Image BFILE type with a NULL value results in an error. If you are going to use the Image BFILE type column, you must first populate the column with a value. To populate the value of the Image BFILE type column, you must populate the row with a file constructor.

The following example inserts a row into the table with an image called 'jdoe.gif' from the ORDIMGDIR directory:

    insert into emp values ('John Doe',24000,'Technical Writer',123,
    ORDSYS.ORDIMGF(bfilename('ORDIMGDIR','jdoe.gif'),
    NULL,NULL,NULL,NULL,NULL,NULL));

Note: In release 8.0.3 of the Server, the content of the Image BFILE type is read-only.

The 'bfilename' argument 'ORDIMGDIR' is a directory referring to a file system directory. The following sequence creates a directory named ORDIMGDIR:

    connect internal
            -- make a directory referring to a file system directory
    create directory ordimgdir as '<myimagedirectory>';
    grant read on directory ordimgdir to <user-or-role>;

where <myimagedirectory> is the the file system directory, and <user-or-role> is the specific user to grant read access to.

## 2.6 Populating a Row Using BFILE Images

The following is an example of populating the row with Image BFILE data:

    DECLARE
        Image  ORDSYS.ORDIMGF;
    BEGIN
        insert into emp values('John Doe',24000,'Technical Writer',123,
        ORDSYS.ORDIMGF(bfilename('ORDIMGDIR','jdoe.gif'),
        NULL,NULL,NULL,NULL,NULL,NULL));

        --select the newly inserted row for update
        SELECT large_photo into Image from emp
        where ename = 'John Doe' for UPDATE;

        -- set property attributes for the image data
        Image.setProperties;

        UPDATE emp set large_photo = Image where ename = 'John Doe';

        -- continue processing

     END;

## 2.7 Querying a Row

For the following examples, assume you have this table:

    create table emp (
    ename VARCHAR2(50),
    salary NUMBER,
    job VARCHAR2(50),
    department INTEGER,
    photo_id ORDSYS.ORDIMGB,
    large_photo ORDSYS.ORDIMGF);

The following is an example of querying the row that has Image BFILE data. You must create a table alias (E in this example) when you refer to a type in a SELECT statement.

    SELECT ename, E.photo_id.width
     FROM emp E
    WHERE ename = 'John Doe' and
          E.photo_id.width > 32 and
          E.photo_id.fileFormat='GIFF';

The following is an example of querying the row that has Image LOB data:

    SELECT ename, E.large_photo.compressionFormat
     FROM emp E
    WHERE ename = 'John Doe' and
         E.large_photo.width > 32 and
         E.large_photo.fileFormat='GIFF' and
         E.large_photo.compressionFormat='GIFLZW';

## 2.8 Copying an Image from a BFILE to a BLOB Type

To copy the data from an Image BFILE type to an Image BLOB type, you would use the ORDImgF.copyContent method. For example, the following program copies image data from an Image BFILE type to an Image BLOB type:

     DECLARE
        BLOBImage ORDSYS.ORDIMGB;
        BFILEImage ORDSYS.ORDIMGF;
     BEGIN
        SELECT photo_id,large_photo
        INTO BLOBImage,FILEImage
        FROM emp where ename = 'John Doe' for UPDATE;

        -- Copy the BFILE image to the BLOB image
        BFILEImage.copyContent(BLOBImage.content);

        -- Set the BLOB image properties
        BLOBImage.setProperties;

        -- update the row
        UPDATE emp
          SET photo_id = BLOBImage
          where ename = 'John Doe';

     END

## 2.9 Copying an Image from a BLOB to a BLOB Type

To copy the data between two Image BLOB types, use the ORDImgB.copyContent method. For example, the following program copies image data from an Image BLOB type to another Image BLOB type:

    DECLARE
        Image_1 ORDSYS.ORDIMGB;
        Image_2 ORDSYS.ORDIMGB;
    BEGIN
        SELECT photo_id
        INTO Image_1
        FROM emp where ename = 'John Doe';

        SELECT photo_id
        INTO Image_2
        FROM emp where ename = 'Also John Doe' for UPDATE;

        -- copy the data from Image_1 to Image_2
        Image_1.copyContent(Image_2.content);

        -- set the image properties for Image_2
        Image_2.setProperties;

        -- continue processing

        UPDATE emp
          SET photo_id = Image_2
          WHERE ename = 'Also John Doe';

    END

## 2.10 Converting an Image's Format

To convert the image data into a different format, use the Process method. For example, the following program converts the image data to the TIFF file format:

     DECLARE
        Image ORDSYS.ORDIMGB;
     BEGIN
        SELECT photo_id
        INTO Image
        FROM emp
        WHERE ename = 'John Doe' for UPDATE;

        -- convert the image to TIFF in place
        Image.process('fileFormat=TIFF');

     END

## 2.11 Copying and Converting in One Step

To make a copy of the image and convert it into one step, use the processCopy method. For example, the following program converts the image data to the TIFF image file format, but leaves the original image intact:

    DECLARE
        Image_1 ORDSYS.ORDIMGB;
        Image_2 ORDSYS.ORDIMGB;
    BEGIN
        SELECT photo_id
        INTO Image_1
        FROM emp
        WHERE ename = 'John Doe' for UPDATE;

        -- convert the image to tiff and store the result in Image_2
        Image_2 := Image_1;
        Image_1.processCopy('fileFormat=TIFF',Image_2.content);

        -- continue processing

    END

Changes made by these methods can be rolled back. This technique may be useful for a temporary format conversion.

## 2.12 Extend the Cartridge With a New Type

You can use the ORDImgF and ORDImgB types as the basis for a new type of your own creation. This task requires the Objects Option of Oracle8 Enterprise Edition.

    create type AnnotatedImage as object

       (  image ordsys.ordimgF,

           description varchar2(2000),

           MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage),
           MEMBER PROCEDURE CopyContent(dest IN OUT BLOB),
           MEMBER PROCEDURE ProcessCopy(command in VARCHAR2, dest IN OUT BLOB)
        );
    /

    create type body AnnotatedImage as
      MEMBER PROCEDURE SetProperties(SELF IN OUT AnnotatedImage) IS
      BEGIN
        SELF.image.setProperties;
        SELF.description :=
     'This is an example of using Image Cartridge as a subtype';
      END SetProperties;

      MEMBER PROCEDURE CopyContent(dest IN OUT BLOB) IS
      BEGIN
        SELF.image.copyContent(dest);
      END CopyContent;

      MEMBER PROCEDURE ProcessCopy(command in VARCHAR2, dest IN OUT BLOB) IS
      BEGIN
        SELF.image.processCopy(command,dest);
      END ProcessCopy;
    END;
    /

After creating the new type, you can use it as you would any other type. For example:

    create or replace directory TEST_DIR as 'C:\TESTS';

    create table my_example (id number,an_image AnnotatedImage);

    insert into my_example values ( 1,
        AnnotatedImage(
          ordsys.ordimgf(
            bfilename('TEST_DIR','test1.jpg'),
            NULL,NULL,NULL,NULL,NULL,NULL,NULL),
          NULL)
        );
    commit;

    declare
      myimage AnnotatedImage;
    begin
      select an_image into myimage from my_example;

      myimage.setProperties;

      dbms_output.put_line('This image has a description of ');
      dbms_output.put_line( myimage.description);

      update my_example set an_image=myimage;
    end;

## 2.13 Use Image Types With Object Views

Just as a view is a virtual table, an object view is a virtual object table.

Oracle provides object views as an extension of the basic relational view mechanism. By using object views, you can create virtual object tables from data-- of either built-in or user-defined types -- stored in the columns of relational or object tables in the database.

Object views provide the ability to offer specialized or restricted access to the data and objects in a database. For example, you might use an object view to provide a version of an employee object table that doesn't have attributes containing sensitivedata and doesn't have a deletion method. Object views also allow you to try object-oriented programming without permanently converting your tables. Using object views, you can convert data gradually and transparently from relational tables to object-relational tables.

Consider the following non-object image table:

    create table flat (
       id            number,
       content       bfile,
       height        number,
       width         number,
       contentLength number,
       fileFormat    varchar2(64),
       contentFormat varchar2(64),
       compressionFormat varchar2(64)
                       );

You can create an object view on the flat table as follows:

    create or replace view object_images_v as
      select
          id,
          ordsys.ORDImgF(
             T.content,
             T.height,
             T.width,
             T.contentLength,
             T.compressionFormat
                         ) IMAGE
       from flat T;

Object views provide the flexibility of looking at the same relational or object data in more than one way. Thus you can use different in-memory object representations for different applications without changing the way you store the data in the database. See the Oracle8 Concepts manual for more information on defining, using, and updating object views.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index