LOCOPY Function

The LOCOPY function creates a copy of a smart large object.

The first parameter specifies the BLOB or CLOB column to copy. The table and column parameters are optional.
  • If you omit table and column arguments, the LOCOPY function creates a smart large object with system-specified storage defaults, and copies the data in the BLOB or CLOB column into it.

    The LOCOPY function obtains the system-specific storage defaults from either the ONCONFIG file or the sbspace. For more information on system-specified storage defaults, see the HCL OneDB™ Administrator's Guide.

  • When you specify table and column, the LOCOPY function uses the storage characteristics from the specified column for the BLOB or CLOB value that it creates.

The LOCOPY function returns a handle value (a pointer) to the new BLOB or CLOB value. This function does not actually store the new smart-large-object value into a column in the database. You must assign the BLOB or CLOB value to the appropriate column.

The following code fragment copies the CLOB value in the resume column of the candidate table to the resume column of the interview table:
/* Insert a new row in the interviews table and get the
 * resulting SERIAL value (from sqlca.sqlerrd[1])
 */
EXEC SQL insert into interviews (intrv_num, intrv_time)
   values (0, '09:30');
intrv_num = sqlca.sqlerrd[1];

/* Update this interviews row with the candidate number
 * and resume from the candidate table. Use LOCOPY to
 * create a copy of the CLOB value in the resume column
 * of the candidate table.
 */
EXEC SQL update interviews 
   SET (cand_num, resume) = 
      (SELECT cand_num, 
         LOCOPY(resume, 'candidate', 'resume')
      FROM candidate
      WHERE cand_lname = 'Haven')
   WHERE intrv_num = :intrv_num;

In the preceding example, the LOCOPY function returns a handle value for the copy of the CLOB resume column in the candidate table. Because the LOCOPY function specifies a table and column name, this new CLOB value has the storage characteristics of this resume column. If you omit the table (candidate) and column (resume) names, the LOCOPY function uses the system-defined storage defaults for the new CLOB value. The UPDATE statement then assigns this new CLOB value to the resume column in the interviews table.

In the following example, the LOCOPY function executes on the local database and returns a handle value on the local server for the copy of the BLOB cand_pic column in the election2008 table in rdb, which is another database of the local database server. The INSERT statement then assigns this new BLOB value to the cand_photo column in the local candidate table.
INSERT INTO candidate (cand_photo)
   SELECT LOCOPY(cand_pic) FROM rdb:election2008;

When the LOCOPY function executes on the same database server as the original BLOB or CLOB column in a distributed query, it produces two copies of the BLOB or CLOB value, one in the remote database and the other in the local database, as the following two examples show.

In the first example, the LOCOPY function executes on the remote rdb database and returns a handle value in the remote database for the copy of the BLOB cand_pic column in the remote election2008 table. The INSERT statement then assigns this new BLOB value to the cand_photo column in the local candidate table:
INSERT INTO candidate (cand_photo)
   SELECT rdb:LOCOPY(cand_pic) 
      FROM rdb:election2008;
In the second example, the LOCOPY function executes on the local database and returns a handle value on the local database for the copy of the BLOB cand_photo column in the local candidate table. The INSERT statement then assigns this new BLOB value to the cand_pic column in the election2008 table in the remote rdb database:
INSERT INTO rdb:election2008 (cand_pic)
   SELECT LOCOPY(cand_photo) FROM candidate;

The BLOB and CLOB arguments of the built-in LOCOPY function are built-in opaque data types. These can be values returned by cross-database DML operations or by cross-database function calls, but built-in opaque types do not support distributed operations across database server instances. If the local database and the rdb database are databases of different HCL OneDB instances, the INSERT statements in the previous two examples fail with error -999.