The database server provides four SQL functions that you can call from within an SQL statement to import and export smart large objects. Table 4 shows the smart-large-object functions.
| 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 Table 4 shows in SELECT, UPDATE, and INSERT statements. For examples of how to use the preceding functions in INSERT and UPDATE statements, see Modifying Data.
Suppose you create the inmate and fbi_list tables, as Figure 216 shows.
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 filename in a function argument, depending on whether the file resides on the client or server computer: