Smart large object functions

The database server provides four SQL functions that you can call from within an SQL statement to import and export smart large objects. The following table shows the smart-large-object functions.
Table 1. SQL functions for smart large objects
Function name Purpose
FILETOBLOB() Copies a file into a BLOB column
FILETOCLOB() Copies a file into a CLOB column
LOCOPY() Copies BLOB or CLOB data into another BLOB or CLOB column
LOTOFILE() Copies a BLOB or CLOB into a file

For detailed information and the syntax of smart-large-object functions, see the Expression segment in the IBM® Informix® Guide to SQL: Syntax.

You can use any of the functions that the table shows in SELECT, UPDATE, and INSERT statements. For examples of how to use the preceding functions in INSERT and UPDATE statements, see Modify data.

Suppose you create the inmate and fbi_list tables, as the following figure shows.
Figure 1. Create the inmate and fbi_list tables.
CREATE TABLE inmate 
(
   id_num   INT, 
   picture  BLOB,
   felony   CLOB
);

CREATE TABLE fbi_list 
( 
   id       INTEGER,
   mugshot  BLOB
) PUT mugshot IN (sbspace1);
The following SELECT statement uses the LOTOFILE() function to copy data from the felony column into the felon_322.txt file that is located on the client computer:
SELECT id_num, LOTOFILE(felony, 'felon_322.txt', 'client')
   FROM inmate
   WHERE id = 322;

The first argument for LOTOFILE() specifies the name of the column from which data is to be exported. The second argument specifies the name of the file into which data is to be copied. The third argument specifies whether the target file is located on the client computer ('client') or server computer ('server').

The following rules apply for specifying the path of a file name in a function argument, depending on whether the file resides on the client or server computer:
  • If the source file resides on the server computer, you must specify the full path name to the file (not the path name relative to the current working directory).
  • If the source file resides on the client computer, you can specify either the full or relative path name to the file.