Using the lld_locator Type

The lld_locator type defines a large object. It identifies the type of large object and points to its location. It contains three fields:

lo_protocol

identifies the kind of large object.

lo_pointer

is a pointer to a smart large object or is NULL if the large object is any kind of large object other than a smart large object.

lo_location

is a pointer to the large object, if it is not a smart large object. Set to NULL if it is a smart large object.

The examples in this section show how to:

• insert an lld_locator row for an existing server file into a table.
• create a smart large object.
• copy a client file to a large object.
• copy a large object to another large object.
• copy a large object to a client file.
• create and delete a server file.

Inserting an lld_locator Row into a Table

The example in Figure 4 creates a table with an lld_locator row and shows how to insert a large object into the row.

--Create lobs table
create table lobs (key int primary key, lo lld_locator);

-- Create an lld_locator for an existing server file
insert into lobs
    values (1, "row('ifx_file',null,'/tmp/quote1.txt')");

The INSERT statement inserts an instance of an lld_locator row into the lobs table. The protocol in the first field, IFX_FILE, identifies the large object as a server file. The second field, lo_pointer, is used to point to a smart large object. Because the object is a server file, this field is NULL. The third field identifies the server file as quote1.txt.

Creating a Smart Large Object

The example in Figure 5 creates a smart large object containing CLOB type data. The lld_create function in Figure 5 creates a smart large object. The first parameter to lld_create uses the IFX_CLOB protocol to specify CLOB as the type of object to create. The other two arguments are NULL.

The lld_create function creates the CLOB type large object and returns an lld_locator row that identifies it.

The insert statement inserts in the lobs table the lld_locator row returned by lld_create.

--Create a new clob using lld_create
insert into lobs
    values (2, lld_create ("row('ifx_clob',null,null)"::lld_locator));

Copying a Client File to a Large Object

The example in Figure 6 uses the lobs table created in Figure 5.

In Figure 6, the lld_fromclient function in the first SELECT statement, copies the client file, quote2.txt, to an lld_locator row in the lobs table.

-- Copy a client file to an lld_locator 
select lld_fromclient ('quote2.txt', lo) from lobs where key = 2;


(expression)  ROW('IFX_CLOB      ','clob:ffffffffa6b7c8d9000000020000000300
          0000090000001a0000000000010000000000000ad3c3dc000000000b06eec8000
          00000005c4e6000607fdc000000000000000000000000',NULL) 


select lo.lo_pointer::clob from lobs where key = 2;


(expression)  
To be or not to be,
that is the question.

The lld_fromclient function returns a pointer to the lld_locator row that identifies the data copied from the large object. The first SELECT statement returns this lld_locator row.

The next SELECT statement selects the lo_pointer field of the lld_locator row, lo.lo_pointer, and casts it to CLOB type data. The result is the data itself.

Copying a Large Object to a Large Object

The example in Figure 7 uses the lobs table created in Figure 4.

The lld_copy function in Figure 7 copies large object data from one lld_locator type row to another.

-- Copy an lld_locator to an lld_locator 
select lld_copy (S.lo, D.lo) from lobs S, lobs D where S.key = 1 and D.key = 2;


(expression)  ROW('IFX_CLOB      ','clob:ffffffffa6b7c8d9000000020000000300
          0000090000001a0000000000010000000000000ad3c3dc000000000b06eec8000
          00000005c4e6000607fdc000000000000000000000000',NULL) 


select lo.lo_pointer::clob from lobs where key = 2;


(expression)  
Ask not what your country can do for you,
but what you can do for your country.

The second SELECT statement casts lo.lo_pointer to a CLOB type to display the data in the column.

Copying Large Object Data to a Client File

The example in Figure 8 uses the lobs table created in Figure 4. The lld_toclient function in Copying Large Object Data to a Client File copies large object data to the output.txt client file. This function returns t when the function succeeds. The SELECT statement returns t, or true, indicating that the function returned successfully.

-- Copy an lld_locator to a client file
select lld_toclient (lo, 'output.txt') from lobs where key = 2;

(expression) 

           t

Creating and Deleting a Server File

The example in Figure 9 shows how to create a server file and then delete it.

The lld_copy function copies a large object to another large object. The lld_locator rows for the source and destination objects use the IFX_FILE protocol to specify a server file as the type of large object. The lld_copy function returns an lld_locator row that identifies the copy of the large object.

The INSERT statement inserts this row into the lobs table using 3 as the key.

-- Create and delete a new server file 
insert into lobs
    values (3, lld_copy (
        "row('ifx_file',null,'/tmp/quote2.txt')"::lld_locator,
        "row('ifx_file',null,'/tmp/tmp3')"::lld_locator));

select lo from lobs where key = 3;


lo  ROW('IFX_FILE          ',NULL,'/tmp/tmp3') 


select lld_delete (lo) from lobs where key = 3;

(expression) 

           t


delete from lobs where key = 3;

The first SELECT statement returns the lld_locator row identifying the large object.

The lld_delete function deletes the large object itself. The DELETE statement deletes the lld_locator row that referenced the large object.