Smart-Large-Object Functions (IDS)

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Data Types and Expressions > Expression >

Smart-Large-Object Functions (IDS)

The smart-large-object functions support BLOB and CLOB data types:

Smart-Large-Object Functions:
 
|--+-+-FILETOBLOB-+--(--pathname--,--file_destination--+---------------------+--)-+--|
   | '-FILETOCLOB-'                                    '-,--table--,--column-'    |
   +-LOTOFILE--(--+-BLOB_column-+--,--pathname--,--file_destination--)------------+
   |              '-CLOB_column-'                                                 |
   '-LOCOPY--(--+-BLOB_column-+--+---------------------+--)-----------------------'
                '-CLOB_column-'  '-,--table--,--column-'

Element	Description	Restrictions	Syntax
BLOB_column, CLOB_column	A column of type BLOB; a column of type CLOB	In table.column, the column must have BLOB or CLOB data type	Identifier, p. Identifier
column	Column within table for the copy of the BLOB or CLOB value	Must have CLOB or BLOB as its data type	Quoted String, p. Quoted String
file_destination	Name of the system on which to put or get the smart large object	The only valid values are the strings server or client	Quoted String, p. Quoted String
pathname	Directory path and filename to locate the smart large object	Must exist on file_destination system. See also Pathnames with Commas.	Quoted String, p. Quoted String
table	Name or synonym of a table that contains column whose storage characteristics are used for the copy of BLOB or CLOB value	Must exist in the database and must contain a CLOB or BLOB column	Quoted String, p. Quoted String

FILETOBLOB and FILETOCLOB Functions

The FILETOBLOB function creates a BLOB value for data that is stored in a specified operating-system file. Similarly, the FILETOCLOB function creates a CLOB value for data that is stored in an operating-system file.

These functions determine the operating-system file to use from the following parameters:

The pathname parameter identifies the directory path and name of the source file.
The file destination parameter identifies the computer, client or server, on which this file resides:
- Set file destination to client to identify the client computer as the location of the source file. The pathname can be either a full pathname or relative to the current directory.
- Set file destination to server to identify the server computer as the location of the source file. The pathname must be a full pathname.

The table and column parameters are optional:

If you omit table and column, the FILETOBLOB function creates a BLOB value with the system-specified storage defaults, and the FILETOCLOB function creates a CLOB value with the system-specified storage defaults.
These functions obtain the system-specific storage characteristics from either the ONCONFIG file or the sbspace. For more information on system-specified storage defaults, see the IBM Informix Administrator's Guide.
If you specify table and column, the FILETOBLOB and FILETOCLOB functions use the storage characteristics from the specified column for the BLOB or CLOB value that they create.

The FILETOBLOB function returns a handle value (a pointer) to the new BLOB value. Similarly, FILETOCLOB returns a handle value to the new CLOB value. Neither function actually copies the smart-large-object value into a database column. You must assign the BLOB or CLOB value to the appropriate column.

The FILETOCLOB function performs any code-set conversion that might be required when it copies the file from the client or server computer to the database.

The following INSERT statement uses the FILETOCLOB function to create a CLOB value from the value in the smith.rsm file:

INSERT INTO candidate (cand_num, cand_lname, resume) 
   VALUES (2, 'Smith', FILETOCLOB('smith.rsm', 'client'))

In the preceding example, the FILETOCLOB function reads the smith.rsm file in the current directory on the client computer and returns a handle value to a CLOB value that contains the data in this file. Because the FILETOCLOB function does not specify a table and column name, this new CLOB value has the system-specified storage characteristics. The INSERT statement then assigns this CLOB value to the resume column in the candidate table.

The following INSERT statement uses the FILETOBLOB function to create a BLOB value in a remote table, election2006, from the value in the photos.xxx file on the local database server:

INSERT INTO rdb@rserv:election2006 (cand_pic) 
   VALUES (FILETOBLOB('C:\tmp\photos.xxx', 'server', 
      'candidate', 'cand_photo'))

In the preceding example, the FILETOBLOB function reads the photos.xxx file in the specified directory on the local database server and returns a handle value to a BLOB value that contains the data in this file. The INSERT statement then assigns this BLOB value to the cand_pic column in the remote election2006 table. This new BLOB value has the storage characteristics of the cand_photo column in the candidate table on the local database server.

In the following example, the new BLOB value has the storage characteristics of the cand_pix column in the election96 table on a remote database server:

INSERT INTO rdb@rserv:election2006 (cand_pic) 
   VALUES (FILETOBLOB('C:\tmp\photos.xxx', 'server',
      'rdb2@rserv2:election96', 'cand_pix'))

When you qualify the FILETOBLOB or FILETOCLOB function with the name of a remote database and a remote database server, the pathname and the file destination become relative to the remote database server.

When you specify server as the file destination, as the following example shows, the FILETOBLOB function looks for the source file (in this case, photos.xxx) on the remote database server:

INSERT INTO rdb@rserv:election (cand_pic) 
   VALUES (rdb@rserv:FILETOBLOB('C:\tmp\photos.xxx', 'server'))

When you specify client as the file destination, however, as in the following example, the FILETOBLOB function looks for the source file (in this case, photos.xxx) on the local client computer:

INSERT INTO rdb@rserv:election (cand_pic) 
   VALUES (rdb@rserv:FILETOBLOB('photos.xxx', 'client'))

Pathnames with Commas

If a comma ( , ) symbol is within the pathname of the function, the database server expects the pathname to have the following format:

"offset, length, pathname"

For pathnames that contain a comma, you must also specify an offset and length, as in the following example:

FILETOBLOB("0,-1,/tmp/blob,x","server")

The first term in the quoted pathname string is an offset of 0, which instructs the database server to begin reading at the start of the file.

The second term is a length of -1, which instructs the database server to continue reading until the end of the entire file.

The third term is the /tmp/blob,x pathname, specifying which file to read. (Notice the comma symbol that precedes the x.)

Because the pathname includes a comma, the comma-separated offset and length specifications are necessary in this example to avoid an error when FILETOBLOB is called. You do not need to specify offset and length for pathnames that include no comma, but including 0,-1, as the initial characters of the pathname string avoids this error for any valid pathname.

LOTOFILE Function

The LOTOFILE function copies a smart large object to an operating-system file. The first parameter specifies the BLOB or CLOB column to copy. The function determines what file to create from the following parameters:

The pathname identifies the directory path and the source file name.
The file destination identifies the computer, client or server, on which this file resides:
- Set file destination to client to identify the client computer as the location of the source file. The pathname can be either a full pathname or a path relative to the current directory.
- Set file destination to server to identify the server computer as the location of the source file. The full pathname is required.

By default, the LOTOFILE function generates a filename of the form:

file.hex_id

In this format, file is the filename you specify in pathname and hex_id is the unique hexadecimal smart-large-object identifier. The maximum number of digits for a smart-large-object identifier is 17; however most smart large objects would have an identifier with significantly fewer digits.

For example, suppose that you specify a UNIX pathname value as follows:

'/tmp/resume'

If the CLOB column has the identifier 203b2, then LOTOFILE creates the file:

/tmp/resume.203b2

For another example, suppose that you specify a Windows pathname value as follows:

'C:\tmp\resume'

If the CLOB column has an identifier of 203b2, the LOTOFILE function would create the file:

C:\tmp\resume.203b2

To change the default filename, you can specify the following wildcards in the filename of the pathname:

One or more contiguous question mark ( ? ) characters in the filename can generate a unique filename.
The LOTOFILE function replaces each question mark with a hexadecimal digit from the identifier of the BLOB or CLOB column.

For example, suppose that you specify a UNIX pathname value as follows:
```
'/tmp/resume??.txt'
```
The LOTOFILE function puts 2 digits of the hexadecimal identifier into the name. If the CLOB column has an identifier of 203b2, the LOTOFILE function would create the file:
```
/tmp/resume20.txt  
```
If you specify more than 17 question marks, LOTOFILE ignores them.
An exclamation ( ! ) point at the end of the filename indicates that the filename does not need to be unique.
For example, suppose that you specify a Windows pathname value as follows:
```
'C:\tmp\resume.txt!'
```
The LOTOFILE function does not use the smart-large-object identifier in the filename, so it generates the following file:
```
C:\tmp\resume.txt 
```

If the filename you specify already exists, LOTOFILE returns an error.

The LOTOFILE function performs any code-set conversion that might be required when it copies a CLOB value from the database to a file on the client or server computer.

When you qualify LOTOFILE with the name of a remote database and a remote database server, the BLOB or CLOB column, the pathname, and the file destination become relative to the remote database server.

When you specify server as the file destination, as in the next example, the LOTOFILE function copies the smart large object from the remote database server to a source file in the specified directory on the remote database server:

rdb@rserv:LOTOFILE(blob_col, 'C:\tmp\photo.gif!', 'server')

If you specify client as the file destination, as in the following example, the LOTOFILE function copies the smart large object from the remote database server to a source file in the specified directory on the local client computer:

rdb@rserv:LOTOFILE(clob_col, 'C:\tmp\essay.txt!', 'client')

LOCOPY Function

The LOCOPY function creates a copy of a smart large object. The first parameter specifies the BLOB or CLOB column to copy. The table and column parameters are optional.

If you omit table and column, the LOCOPY function creates a smart large object with system-specified storage defaults and copies the data in the BLOB or CLOB column into it.
The LOCOPY function obtains the system-specific storage defaults from either the ONCONFIG file or the sbspace. For more information on system-specified storage defaults, see the IBM Informix Administrator's Guide.
When you specify table and column, the LOCOPY function uses the storage characteristics from the specified column for the BLOB or CLOB value that it creates.

The LOCOPY function returns a handle value (a pointer) to the new BLOB or CLOB value. This function does not actually store the new smart-large-object value into a column in the database. You must assign the BLOB or CLOB value to the appropriate column.

The following ESQL/C code fragment copies the CLOB value in the resume column of the candidate table to the resume column of the interview table:

/* Insert a new row in the interviews table and get the
 * resulting SERIAL value (from sqlca.sqlerrd[1])
 */
EXEC SQL insert into interviews (intrv_num, intrv_time)
   values (0, '09:30');
intrv_num = sqlca.sqlerrd[1];

/* Update this interviews row with the candidate number
 * and resume from the candidate table. Use LOCOPY to
 * create a copy of the CLOB value in the resume column
 * of the candidate table.
 */
EXEC SQL update interviews 
   SET (cand_num, resume) = 
      (SELECT cand_num, 
         LOCOPY(resume, 'candidate', 'resume')
      FROM candidate
      WHERE cand_lname = 'Haven')
   WHERE intrv_num = :intrv_num;

In the preceding example, the LOCOPY function returns a handle value for the copy of the CLOB resume column in the candidate table. Because the LOCOPY function specifies a table and column name, this new CLOB value has the storage characteristics of this resume column. If you omit the table (candidate) and column (resume) names, the LOCOPY function uses the system-defined storage defaults for the new CLOB value. The UPDATE statement then assigns this new CLOB value to the resume column in the interviews table.

In the following example, the LOCOPY function executes on the local database server and returns a handle value on the local server for the copy of the BLOB cand_pic column in the remote election2006 table. The INSERT statement then assigns this new BLOB value to the cand_photo column in the local candidate table.

INSERT INTO candidate (cand_photo)
   SELECT LOCOPY(cand_pic) FROM rdb@rserv:election2006;

When the LOCOPY function executes on the same database server as the original BLOB or CLOB column in a distributed query, it produces two copies of the BLOB or CLOB value, one on the remote database server and the other on the local database server, as the following two examples show.

In the first example, the LOCOPY function executes on the remote database server and returns a handle value on the remote server for the copy of the BLOB cand_pic column in the remote election2006 table. The INSERT statement then assigns this new BLOB value to the cand_photo column in the local candidate table:

INSERT INTO candidate (cand_photo)
   SELECT rdb@rserv:LOCOPY(cand_pic) 
      FROM rdb@rserv:election2006

In the second example, the LOCOPY function executes on the local database server and returns a handle value on the local server for the copy of the BLOB cand_photo column in the local candidate table. The INSERT statement then assigns this new BLOB value to the cand_pic column in the remote election2006 table:

INSERT INTO rdb@rserv:election2006 (cand_pic)
   SELECT LOCOPY(cand_photo) FROM candidate