Using SQL Functions to Update Smart Large Objects (IDS)

You can use an SQL function that you can call from within an UPDATE statement to import and export smart large objects. For a description of these functions, see page Smart-Large-Object Functions (IDS).

The following UPDATE statement uses the LOCOPY() function to copy BLOB data from the mugshot column of the fbi_list table into the picture column of the inmate table. (Figure 216 defines the inmate and fbi_list tables.)

UPDATE inmate (picture)
    SET picture = (SELECT LOCOPY(mugshot, 'inmate', 'picture')
                   FROM fbi_list WHERE fbi_list.id = 669)
    WHERE inmate.id_num = 437

The first argument for LOCOPY() specifies the column (mugshot) from which the object is exported. The second and third arguments specify the name of the table (inmate) and column (picture) whose storage characteristics the newly created object will use. After execution of the UPDATE statement, the picture column contains data from the mugshot column.

When you specify the path of a filename in the function argument, apply the following rules:

• If the source file resides on the server computer, you must specify the full pathname to the file (not the pathname relative to the current working directory).
• If the source file resides on the client computer, you can specify either the full or relative pathname to the file.