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;