IBM Books

Image, Audio, and Video Extenders Administration and Programming

Transmitting large objects

You can transmit large objects such as images, audio clips, and video clips between your application and a DB2 database in various ways. The method you use depends on whether the object is transmitted to or from a file or memory buffer. The method you use also depends on whether the file is in your client machine or in the database server machine.

If the object is transmitted between a table and a server file

When you transmit an object between a database table and a server file, specify the file path in the appropriate extender UDF request. Because the extender UDF and the file are both on the server, the extender will be able to find the file. For example, in the following SQL statement, an image whose content is in a server file is stored in a database table:

EXEC SQL BEGIN DECLARE SECTION;
  long hvStorageType;
EXEC SQL END DECLARE SECTION;
 
hvStorageType=MMDB_STORAGE_TYPE_INTERNAL;
 
EXEC SQL INSERT INTO EMPLOYEE VALUES(
        '128557',
        'Anita Jones',
        DB2Image(
          CURRENT SERVER,
          '/employee/images/ajones.bmp',
          'ASIS',
          :hvStorageType,
          'Anita''s picture')
       );

If the object is transmitted to or from a client buffer

The extenders cannot directly access a memory buffer. If you want to transmit an object to or from a buffer on your client machine, you need a way to do it other than by specifying a buffer location. One way to transmit an object to or from a buffer is through a host variable. This is the way you normally transmit objects between an application and a DB2 database.

You define and use host variables for large objects in the same way as for traditional character and numeric objects. You declare the host variables in a DECLARE section, assign them values for transmission, or access values that are transmitted to them.

When you declare a host variable for image, audio, or video data, specify a data type of BLOB. When you use a UDF to store, retrieve, or update an object, you specify the appropriate host variable as an argument in the UDF request. Use the same format as for other host variables that you specify in an SQL statement.

For example, the following SQL statements declare and use a host variable that is named hvaudio to transmit an audio clip to the database:

EXEC SQL BEGIN DECLARE SECTION;
  SQL TYPE IS BLOB (2M) hvaudio;
EXEC SQL END DECLARE SECTION;
 
EXEC SQL INSERT INTO EMPLOYEE VALUES(
        '128557',
        'Anita Jones',
        DB2Audio(
          CURRENT SERVER,
          :hvaudio,
          'WAVE',
          CAST(NULL as LONG VARCHAR),
          'Anita''s voice')
       );

Using LOB locators

Large objects such as audio and video clips can be very large, and using host variables might not be the most efficient way of manipulating them. A LOB locator might be a better way to manipulate LOBs in your applications.

A LOB locator is a small (4-byte) value stored in a host variable that your program can use to refer to a much larger LOB in the DB2 database. Using a LOB locator, your program can manipulate the LOB as if the LOB was stored in a regular host variable. The difference is that there is no need to transport the LOB between the database server and the application on the client machine. For example, when you select a LOB in a database table, the LOB remains on the server, and the LOB locator moves to the client.

You declare a LOB locator in a DECLARE section and use it in the same way as a host variable. When you declare a LOB locator for image, audio, or video data, specify a data type of BLOB_LOCATOR. For example, the following SQL statements declare and use a LOB locator that is named video_loc to retrieve a video clip from a database table:

EXEC SQL BEGIN DECLARE SECTION;
  SQL TYPE IS BLOB_LOCATOR video_loc;
EXEC SQL END DECLARE SECTION;
 
EXEC SQL SELECT CONTENT(VIDEO)
     INTO :video_loc
     FROM EMPLOYEE
     WHERE NAME='Anita Jones';

UDFs use LOB locators: DB2 extender UDFs that store, retrieve, and update image, audio, and video objects use LOB locators. These UDFs in DB2 extenders V1 did not use LOB locators, and because of this, could not process objects larger than 2 MB. This restriction forced users to transmit objects larger than 2 MB in segments. Because these UDFs now use LOB locators, the 2 MB restriction is removed.

If the object is transmitted to or from a client file

Use a file reference variable to transmit objects to and from a file on a client. Using a file reference variable saves you from having to allocate buffer space for a large object in your application program. When you use a file reference variable with a UDF, DB2 passes the BLOB content directly between the file and the UDF.

You declare a file reference variable in a DECLARE section and use it in the same way as a host variable. When you declare a file reference variable for image, audio, or video data, specify a data type of BLOB_FILE. However, unlike a host variable, which contains the content of an object, the file reference variable contains the name of the file. The size of the file can be no larger than the size of the BLOB defined for the UDF.

You have various options for how to use a file reference variable for input and output. You choose the option you want by setting the FILE_OPTIONS field in the file reference variable structure in your program. You can choose from the following options:

Option for input:

SQL_FILE_READ. This file can be opened, read, and closed. The length of the data in the file (in bytes) is determined when the file is opened. The data_length field of the file reference variable structure holds the length of the file (in bytes).

Options for output:

SQL_FILE_CREATE. This option creates a new file if it does not already exist. If the file already exists, an error message is returned. The data_length field of the file reference variable structure holds the length of the file (in bytes).
SQL_FILE_OVERWRITE. This option creates a new file if it does not already exist. If the file already exists, the new data overwrites the data in the file. The data_length field of the file reference variable structure holds the length of the file (in bytes).
SQL_FILE_APPEND. This option appends the output to the file if the file already exists. If the file does not exist, it creates a new file. The data_length field of the file reference variable structure holds the length of the data that is added to the file (in bytes), not the total length of the file.

For example, the following statements declare a file reference variable that is named Img_file and use it to store an image, whose content is in a client file, into a database table. Notice the SQL_FILE_READ assignment in the FILE_OPTIONS field:

EXEC SQL BEGIN DECLARE SECTION;
  SQL TYPE IS BLOB_FILE Img_file;
  EXEC SQL END DECLARE SECTION;
 
strcpy (Img_file.name,"/employee/images/ajones.bmp");
Img_file.name_length=strlen(Img_file.name);
Img_file.file_options=SQL_FILE_READ;
 
EXEC SQL INSERT INTO EMPLOYEE VALUES(
        '128557',
        'Anita Jones',
        DB2Image(
          CURRENT SERVER,
          :Img_file,
          'ASIS',
          CAST(NULL as LONG VARCHAR),
          'Anita''s picture')
       );

Specifying file names when you transmit objects

The DB2 extenders give you flexibility in how to specify file names when you store, retrieve, or update objects.

Although you can specify a fully qualified file name, (that is, a complete path followed by the file name) for store, retrieve, and update operations, it's preferable to specify a relative file name. In AIX, HP-UX, and Solaris, a relative file name is any file name that does not begin with a slash. In OS/2 and Windows, a relative file name is any file name that does not begin with a drive letter followed by a colon and backslash.

If you specify a relative file name, the extenders will use the directory specifications in various client and server environment variables as a search path to resolve the file name. A full path name consists of a leading part, which is typically related to mount points, and a trailing pathname, which uniquely identifies the needed file. The trailing pathname is specified in UDFs. Environment variables supply a list of leading pathnames to search when trying to resolve relative file names. See Appendix A, Setting environment variables for DB2 extenders for information about the environment variables that the DB2 extenders use to resolve file names.

The extenders also convert file name formats as appropriate. When a file name is passed to the server, it is converted to the appropriate format for the server's operating system. For example, an OS/2 file name such as c:\dir1\abc.bmp is converted to /dir1/abc.bmp when passed to an AIX server.


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