IBM Books

Image, Audio, and Video Extenders Administration and Programming

Retrieving and using attributes

When you store an image, audio, or video object in a database, the extender also stores the object's attributes in the database. When you update an object, the extender updates the object's attributes that are stored in the database. These attributes are available for you to use in queries.

The extenders create UDFs for each of the attributes that they manage. As a result, you can specify UDFs in SQL statements to access and use object attributes. The following table lists the attributes that the extenders manage and their UDFs. It also indicates the object types for each attribute. Some of the attributes, such as an object's format and file name, are common to all the object types. These attributes are associated with image, audio, and video objects. Other attributes, such as sampling rate or compression type, are specific to certain object types, such as audio and video.

Table 6. Attributes managed by the DB2 extenders
Attribute UDF Image Audio Video
Name of server file in which the object is stored Filename x x x
User ID of person who stored the object Importer x x x
Date and time when the object was stored ImportTime x x x
Size of the object in bytes Size x x x
User ID of person who last updated the object Updater x x x
Date and time when the object was last updated UpdateTime x x x
Format of the object (for example, GIF or MPEG1) Format x x x
Comments about the object Comment x x x
Height of the object (in pixels) Height x   x
Width of the object (in pixels) Width x   x
Number of colors in the object NumColors x    
Thumbnail-size image of the object Thumbnail x   x
Number of bytes returned per sample in an audio, or in an audio track of a video AlignValue   x x
Number of bits used to represent each sample BitsPerSample   x x
Number of recorded channels NumChannels   x x
Duration (in seconds) Duration   x x
Sampling rate (in samples per second) SamplingRate   x x
Average bytes per second transfer time BytesPerSec   x  
Number of audio track for instrument FindInstrument   x  
Track number of named track FindTrackName   x  
Name of recorded instruments GetInstruments   x  
Track numbers and names of recorded instruments GetTrackNames   x  
Clock ticks per second of audio TicksPerSec   x  
Clock ticks per quarter note of audio TicksPerQNote   x  
Aspect ratio AspectRatio     x
Video compression format (such as MPEG1) CompressType     x
Frames per second of throughput FrameRate     x
Maximum throughput (in bytes per second) MaxBytesPerSec     x
Number of audio tracks NumAudioTracks   x x
Number of frames NumFrames     x
Number of video tracks NumVideoTracks     x

You can use an attribute UDF in an SQL statement SELECT clause expression or WHERE clause search condition. When you request the UDF, you specify the name of the column in the database table that contains the object's handle.

For example, the following statement uses the Updater UDF in the SELECT clause of an SQL SELECT statement to retrieve the user ID of the person who last updated an image in the employee table:

EXEC SQL BEGIN DECLARE SECTION;
char hvUpdatr[30];
EXEC SQL END DECLARE SECTION;
 
EXEC SQL SELECT UPDATER(PICTURE)
     INTO :hvUpdatr
     FROM EMPLOYEE
     WHERE NAME = 'Anita Jones';

The following statement uses the Filename UDF in the SELECT clause of a SELECT statement and the NumAudioTracks UDF in the WHERE clause to find videos stored in the employee table that have audio tracks:

EXEC SQL BEGIN DECLARE SECTION;
char hvVid_fname[251];
EXEC SQL END DECLARE SECTION;
 
EXEC SQL SELECT FILENAME(VIDEO)
     INTO :hvVid_fname
     FROM EMPLOYEE
     WHERE NUMAUDIOTRACKS(VIDEO)>0;


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]