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 a data value 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 HCL OneDB™ 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 from the value in the photos.xxx file on the local database server, and insert that value into the election2008 table of the rdb database, which is another database of the local database server: 
INSERT INTO rdb@:election2008 (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 election2008 table in the rdb database of the local database server. This new BLOB value has the storage characteristics of the cand_photo column in the candidate table in the local database.

In the following example, the new BLOB value has the storage characteristics of the cand_pix column in the election96 table in the rdb2 database, where rdb1 and rdb2 are databases of the local HCL OneDB instance: 
INSERT INTO rdb1:election2008 (cand_pic) 
   VALUES (FILETOBLOB('C:\tmp\photos.xxx', 'server',
      'rdb2: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'));