SQL functions to update smart large objects

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.

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. (Create the inmate and fbi_list tables. 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 file name in the function argument, apply the following rules:

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.